Allen Arakaki wrote:
> I use mysql and the status command to give me a
> general idea how my application is performing:
> threads, questions, slow queries, opens, etc are
> very helpful. I am befuddled at how the status
> command counts the questions.
>
> Currently, I'm using
> mysql Ver 14.7 Distrib 4.1.12a, for Win32 (ia32),
> but I've tried it on linux rhel v3.0 with similar
> results. I have a java application and using the
> Connector/J v3.1.8.
>
> If I run the following from the mysql text command
> line:
>
> mysql> status
> ... Threads: 1 Questions: 6103 Slow queries: 0
> ...
> mysql> status
> ... Threads: 1 Questions: 6105 Slow queries: 0
> ...
>
> Why does running status increment the Questions by
> 2? Shouldn't it be 1?
Interesting, well "show status like '%questions%'; does not have this problem.
I am guessing that "status;" does a show status and a show variables on the back end.
>
> If I execute the same sql statement from my java
> application I get a Count-Questions: 9 ... and
> after subtracting 2, it means that my simple sql
> statement executed from java resulted in 7
> questions. Why is that?
>
When java connections happen, there is quite of bit of background work does to set transaction context, etc. So its really connection set up overhead. There also might be some prepared statement magic happening on the backend in your case, not sure.
> How are "Questions" counted in the mysql status
> command?
>
Any action at the MySQL level would be a question, like setting a session variable, acquiring transaction context, etc.
> In my application I do the following:
> Open Db
> Open Connection
> Create Statement
> <execute the statement>
> Close Statement
> Close Connection
> Close Db
> This explains the count of 7. Is this how mysql
> counts questions?
>
Right, or something like that.
> How does executing batch affect the number of
> questions counted?
>
You should get fewer questions per operation since your open/close operations have been amortized across the batch.
> How do #questions processed relate to mysql
> performance. Should I be worried about the number
> of questions that are processed, or the number of
> statements that are processsed?
>
Not really. MySQL connect & operation overhead is very little. Better worry about long running SQL & disk i/o issues for typical database issues. For example, many MySQL databases handle thousands of connects/sec no problem, which is a fairly typical high volume LAMP stack use case.
> If I use connection pooling, the best I can do is
> reduce the questions' count to 3 (eliminate the
> need to open/close db and connection). Does
> stored procedures reduce the questions' count to 1
> (by eliminating the need to create/close a
> statement)?
>
I don't know. There is some overhead in reusing a JDBC database connection from a pool as transaction context is get/set.
> Please enlighten me on how mysql counts
> questions/statements.
>
Hope that answered your questions well enough.
Regards,
Josh
Josh Chamas
Director, Professional Services
MySQL Inc., www.mysql.com
Get More with MySQL!
http://www.mysql.com/consulting