Hi,
It appears libzdb mysql implementation of Connection_executeQuery actually uses a prepared statement to query the database. This may consume a lot more resources than doing a standard query.
At least each query generates 3 actions (statement preparation, execution and then closing). On one of our mysql servers handling up to 4K selects per second, the reported number of operations went up to more than 10K, and the CPU usage went from ~2% user and ~1% system to ~6% user and ~12% system. Some older servers did not have enough CPU available to handle the new load.
Is there a reason for using a (lone) prepared statement for each query, or could there be a way in future versions of the library to at least allow to do standard queries (mysql_query or mysql_real_query, and mysql_fetch_*) ?
Regards
Hi
The reason is that the other alternative, mysql_store_result() read the result set into the client and can potentially use a lot of memory. By doing it with statement, we can get away with using a minimum amount of memory, basically the memory required to hold one row only.
There is also another reason why statement is used instead of mysql_real_query() and that is so Prepared Statements can use the same result set module without any changes.
I just took a look at the MySQL API again and came over mysql_use_result() [1] which seems to support the strategy mentioned above. That is, to read result set partially. It might be that this call will use less CPU. If you can demonstrate that mysql_real_query() + mysql_use_result() use significant less CPU and about the same amount (or less) memory as the existing implementation I'll be happy to change this part in libzdb.
Apropos MysqlResultSet I just got a bug report on this; the first line of a result set is truncated if it extend the pre-allocated buffer. So I need to do some work on the MySQL implementation in any case. And if you can provide a case, I can do it at the same time.
http://dev.mysql.com/doc/refman/5.5/en/mysql-use-result.html
On Nov 6, 2012, at 6:47 PM, Julien Coloos julien.coloos@gmail.com wrote:
Hi,
It appears libzdb mysql implementation of Connection_executeQuery actually uses a prepared statement to query the database. This may consume a lot more resources than doing a standard query.
At least each query generates 3 actions (statement preparation, execution and then closing). On one of our mysql servers handling up to 4K selects per second, the reported number of operations went up to more than 10K, and the CPU usage went from ~2% user and ~1% system to ~6% user and ~12% system. Some older servers did not have enough CPU available to handle the new load.
Is there a reason for using a (lone) prepared statement for each query, or could there be a way in future versions of the library to at least allow to do standard queries (mysql_query or mysql_real_query, and mysql_fetch_*) ?
Le 06/11/2012 21:03, Jan-Henrik Haukeland a écrit :
Hi
The reason is that the other alternative, mysql_store_result() read the result set into the client and can potentially use a lot of memory. By doing it with statement, we can get away with using a minimum amount of memory, basically the memory required to hold one row only.
Fair enough. We mainly do requests with few results, and were using mysql_store_result before trying libzdb, so this memory usage issue didn't cross my mind at first.
I just took a look at the MySQL API again and came over mysql_use_result() [1] which seems to support the strategy mentioned above. That is, to read result set partially. It might be that this call will use less CPU. If you can demonstrate that mysql_real_query() + mysql_use_result() use significant less CPU and about the same amount (or less) memory as the existing implementation I'll be happy to change this part in libzdb.
Yes, but the documentation indicates that "This ties up the server and prevent other threads from updating any tables from which the data is being fetched". This may seem strange (why would that happen here and not for prepared statements with the same strategy ?) and may need to be checked, but does not look appealing. These kind of things would make me want to be able to choose which strategy to use per-request, alas I fear that's not the kind of thing that could make it into your library.
For now we have to do our own quick fix (for our specific case). We will think about what we can do in the long term later.
-
Jan-Henrik Haukeland -
Julien Coloos