You are viewing kostja_osipov

Fish Magic - Counting prepared statements

> Recent Entries
> Archive
> Friends
> Profile
> My photos at flickr

March 24th, 2006


Previous Entry Share Next Entry
04:14 pm - Counting prepared statements
Finally found time to fix Bug#16365, which is a request to add a limit for the total number of prepared statements in the server. It turns out to be a pretty useful feature, at least I was able to find 4 statement leaks in the test suite with it. The patch adds server variables to limit and monitor the count of prepared statements:
mysql> show variables like '%stmt%';
 ------------------------- ------- 
| Variable_name           | Value |
 ------------------------- ------- 
| max_prepared_stmt_count | 16382 |
| prepared_stmt_count     | 0     |
 ------------------------- ------- 
2 rows in set (0.00 sec)
Notice that this is slightly different from the status information, as in the status we account for all prepares and executes, including unsuccessful ones:
mysql> prepare stmt from "select 1"; prepare stmt1 from "select 2";
mysql> prepare stmt2 from "select bla";
ERROR 1054 (42S22): Unknown column 'bla' in 'field list'
mysql> show variables like '%stmt%';
 ------------------------- ------- 
| Variable_name           | Value |
 ------------------------- ------- 
| max_prepared_stmt_count | 16382 |
| prepared_stmt_count     | 2     |
 ------------------------- ------- 
mysql> show status like '%stmt%';
 ------------------------- ------- 
| Variable_name           | Value |
 ------------------------- ------- 
| Com_stmt_close          | 0     |
| Com_stmt_prepare        | 3     |
 ------------------------- ------- 
So now 4.1 has a protection against statement leaks in the application, a way to see the total number of prepared statements, and the current prepare/execute ratio.
And I got peter_zaitsev off my back :)

(4 comments | Leave a comment)

Comments:


From:jamesd
Date:March 25th, 2006 12:52 pm (UTC)
(Link)
He wasn't the only person watching this one. Thanks. :)
From:jeremycole
Date:March 28th, 2006 10:18 pm (UTC)
(Link)
Hi Konst,

Have the four statement leak bugs you mentioned been fixed? Can you point me to the patches/descriptions/bugs of them?

Regards,

Jeremy
[User Picture]
From:kostja_osipov
Date:March 28th, 2006 11:18 pm (UTC)
(Link)
Hey Jeremy,
the statement leaks are in the test suite -- allocating a statement handle with PREPARE and not freeing it. I fixed them in the same patch. So I think you're pursuing a wrong cause. Besides, this is 4.1, not 5.0. And your 5.0 problem is being taken care of :)
From:jeremycole
Date:March 29th, 2006 12:00 am (UTC)
(Link)
I just updated Bug #18300... The site where this problem was occurring has rolled back to 4.1, and is still experiencing the same leak.

> Go to Top
LiveJournal.com