Hi All,

Writing an article on performance tuning XOOPS on IIS, and have hit a little snag with the MySQL tuning section. Using the Page Execution Timer & MySQL Counter plugin I wrote a while ago that uses the XoopsLogger class, XOOPS is using 18 MySQL queries to generate the front page. However, using the MySQL command line client if I run the "show status like '%questions%';" command just before and then again just after I do a page refresh, and then subtract the later from the former the number of Questions/queries used by the XOOPS is reported as 105.

This is on my test server where this XOOPS install is the only application using MySQL, so I know these queries aren't been generated by anything else. This leaves an unexplained discrepency of 87 queries between what the XoopsLogger class and MySQL command line client is reporting. I enable the MySQL/Block debug in XOOPS just to be sure, and it was reporting the same number of queries as my counter. As I use XOOPS caching quite heavily I thought that might be the reason, but with all block and module caching off, as well as the "Update module template .html files from themes/your theme/templates directory" option set to on it is even worse. The XoopsLogger class now reports 80 queries to generate the front page, but the MySQL command line client reports 227 queries. I even tried installing a new copy of 2.0.13.1 from scratch, and the descrepencies are still there.

I'm not aware of any inaccuracies with the XoopsLogger class, so I'm left wondering where all these extra queries are comming from. I imagine they are from some kind og background set up overhead, which if is the case I wonder which value I should be using. Any advice would be appreciated .

I'm not a developer, so I may be waaaay off here, but could it be that the queries XOOPS runs are multiple requests/questions in a single query? I mean, if I say 'read 1 thru 10 from table a', it's one query reported by the XoopsLogger class, but may be 10 questions the way MySQL looks at it.

Just a thought...

Herko

Or maybe authentication is counted as a query too? Many queries need to be done on MySQL's system tables to assert rights on tables and databases and to authenticate users.

Hmmm, both suggestions seem logical to me. I'll have to go do some digging in the MySQL manual to see if I can find any clarification. Thanks for the replies ...

I found this thread that discusses the issue:
mysql: status - how are questions counted?

It looks to me like there's not a one-to-one correspondence between "questions" and queries.