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_*) ?