2

Possible Duplicate:
How can “set timestamp” be a slow query?

I am running a web application and Wordpress blog using PHP and MySQL 5.1, residing on a VPS server. The slow query log is showing many entries for items that should not be taking very long to complete. The strange thing is that it when I click on my blog link, it usually take a long time to load initially, but after that it is pretty quick. Then in a few hours it is back to the slow first click again. It seems as if the "SET timestamp= " query is present in nearly all of the slow entries. Here is an example of what it looks like:

# Time: 110129  4:02:06
# User@Host: appsadmin[appsadmin] @ localhost []
# Query_time: 1.367264  Lock_time: 0.000043 Rows_sent: 18  Rows_examined: 18
use apps;
SET timestamp=1296291726;
show tables;
# Time: 110129  4:02:07
# User@Host: wp_user[wp_user] @ localhost []
# Query_time: 0.635450  Lock_time: 0.000041 Rows_sent: 11  Rows_examined: 11
use wordpress_user;
SET timestamp=1296291727;
show tables;
# Time: 110130  4:02:03
# User@Host: appsadmin[appsadmin] @ localhost []
# Query_time: 0.592159  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use apps;
SET timestamp=1296378123;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `mail_queue`;
# Time: 110131  4:02:08
# User@Host: appsadmin[appsadmin] @ localhost []
# Query_time: 2.789990  Lock_time: 0.000047 Rows_sent: 18  Rows_examined: 18
SET timestamp=1296464528;
show tables;
# Time: 110131  4:02:09
# User@Host: wp_user[wp_user] @ localhost []
# Query_time: 0.535981  Lock_time: 0.000041 Rows_sent: 11  Rows_examined: 11
use wordpress_user;
SET timestamp=1296464529;
show tables;
# Time: 110201  3:19:03
# User@Host: wp_user[wp_user] @ localhost []
# Query_time: 1.401393  Lock_time: 0.000071 Rows_sent: 120  Rows_examined: 145
SET timestamp=1296548343;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

This shows that it is not an issue with my DB design, but some kind of configuration problem.

Community
  • 1
  • 1
CrossProduct
  • 323
  • 3
  • 10
  • Isn't it always the SHOW TABLES query that's slow? It seems like you're using all the memory available to MySQL and those tables are falling out of the cache and have to be read from disk, which is slow. – Dan Grossman Feb 01 '11 at 11:05
  • It is not only the SHOW TABLES query that does this, but it does show up a lot. Is there a way to display RAM usage from MySQL? – CrossProduct Feb 01 '11 at 22:00

1 Answers1

0

Are you running into swap on your VPS? Perhaps the lack of memory is causing your queries to be slow. Also I would try enabling the mysql query cache and disable unnecessary things on the system (e.g. unused apache modules, system services, etc) to recover memory.

Slightly unrelated, if you are having performance problems with your wordpress site, I would look into "WP Super Cache" (http://wordpress.org/extend/plugins/wp-super-cache/)

asterisk
  • 11
  • 1
  • This might be the case. How could I go about verifying this? The server is CentOS. – CrossProduct Feb 01 '11 at 21:57
  • use `top` to see if you are using any swap memory; generally for webservers, hitting swap is not a good thing for performance reasons – asterisk Feb 07 '11 at 02:18