SQL Field Indexes (Optimizing Wordpress?)

Portal Home > Knowledgebase > Articles Database > SQL Field Indexes (Optimizing Wordpress?)

Posted by sOliver, 10-24-2010, 07:03 AM
Hi, I am currently trying to optimize my database, because sql makes my server crash couple times per day. Slow query: I've read that it helps to put an index on fields if there are many rows to fetch. However, Wordpress already has a lot of Indexes in the table wordpress_posts E.g. a multi-column index called "type_status_date" for the fields *post_type, post_status, post_date and ID*. Should I remove that index and add a new one for post_date only? Did anyone here optimize their table indexes for their large websites?
Posted by fabin, 10-24-2010, 07:13 AM
Try running See if that speeds it up.
Posted by sOliver, 10-24-2010, 08:12 AM
I've already tried to repair the database and even ran a myisamchk on it.. didn't help :/ Pretty clueless now... my server just crashed again. WIll have to check mysql.log but it's probably the same query
Posted by fabin, 10-24-2010, 08:15 AM
how many rows exist in table wordpress_posts?
Posted by sOliver, 10-24-2010, 08:30 AM
About 1700 posts so 1700 rows ?
Posted by madaboutlinux, 10-24-2010, 08:34 AM
Are you sure it's SQL that is crashing your server? If yes, you can check the exact queries that is crashing the server using Whether a plugin was enabled recently before the problem started? A poorly coded plugin use a lot of resources and thus crashes Mysql.
Posted by sOliver, 10-24-2010, 08:36 AM
The server ran just fine for 12 hours, then again server load up to 190+ and totally unresponsive. Time of occurence: The server crashed (unresponsive) on three days in a row at a time around 8AM .. but I think that was a coincidence because it also crashed at other times like 2AM (EST) today. I am running Wp Supercache, Nextgen Gallery and Wp Pagenavi and some others but there's one specific core query that slows my server down Nextgen Gallery seems to have a slow SQL_CALC_FOUND_ROWS query as well, but here are the queries that are actually slow: I read that when someone uses the page navigation that Wordpress has to fetch all post rows and SELECT SQL_CALC_FOUND_ROWS is known for being really slow. Last edited by sOliver; 10-24-2010 at 08:40 AM.
Posted by sOliver, 10-24-2010, 08:44 AM
Some slow query that might be simply affected by the other one: Could there be something else slowing down the SQL queries? Server load goes up, then the whole server becomes unresponsive, I have to reboot via remote reboot (SSH, WHM all not responding)
Posted by sOliver, 10-24-2010, 08:53 AM
Omg, I just noticed that all server crashes "begin" with this query at almost the same times (2-3AM and 8AM): Could this be somehow related? I am wondering why wordpress is using doing that, because it looks like it wants to pull everything from the user table...since it's quite a large query this could be causing sql to get stuck somehow?
Posted by sOliver, 10-24-2010, 11:44 AM
I took some screenshots when the server load was up again. As soon as I restarted mysql the load went down.. I also restarted apache. http://www.tinyimg.org/images/118top1.jpg http://www.tinyimg.org/images/598top4.jpg http://www.tinyimg.org/images/353top5.jpg What is %wa? Can anyone help me? SQL process wasn't using 100% CPU all the time, but there was a memory leak I think and that's why the server load went up. So an SQL memory leak? What can I do about it?
Posted by sOliver, 10-24-2010, 11:46 AM
Ok I'm close now .. %wa = iowait: Amount of time the CPU has been waiting for I/O to complete and it was doing a kswapd0 which was consuming quite a lot of CPU... what can I do about it?
Posted by sOliver, 10-24-2010, 12:01 PM
I am now going to reindex and defrag my database.. What should I do first, defrag then reindex? Are there any scripts or tools that you use for this job?
Posted by Steven, 10-25-2010, 12:09 PM
We manage some pretty large wordpress installs - 30k posts - a million comments or more. We have ran into various issues - such as doing a single post locking several tables and bringing the entire db server (and site) to its knees. What we have been doing lately is utilizing percona's rpms, wp-supercache, selective use of innodb tables, and related my.cnf changes. With these changes, the sites fly. The query in question was a issue for us in the past, but with the changes done (such as innodb being used) it doesn't affect the system performance.
Posted by sOliver, 10-25-2010, 03:06 PM
Selective use of innodb tables is something I will look into, but I don't have a clue how yet. Supercache is a must. Mashup.com use that W3C Cache, but it's difficult to set up.. ____________________________ Anyway, here's a little update on what I did to fix the issues: 1httpd.conf ## Stop people from opening connections and leaving them open ## If you have many images use KeepAlive Off Apache Timeout 5 KeepAlive Off 2my.cnf max_connections 200 wait_timeout 15 3 Optimize ALL databases 4 Repair ALL databases 5 Tuning SQL Query Cache _____________________________ What I am about to do is to look into MySQL Indexes and find out how I can optimize them for my SELECT queries. Does anyone know an easy way to rebuild an index btw instead of doing it manually?
Posted by Johnburk, 10-25-2010, 05:56 PM
Why set keepalive off with many images?
Posted by Steven, 10-25-2010, 06:22 PM
I recommend supercache - you will have less overhead since you will be serving static files rather then sending content through the php interpreter with total cache.
Posted by TonyB, 10-25-2010, 06:57 PM
What sort of selective use of innodb? People suggest using innodb but there are other consequences in using it. It is typically significantly slower in reads. So you may lose issues with table locks but now you have a problem with data retrieval being much slower as well. I can't find the benchmarks on this now but I noticed it and read about this same thing. I just can't find where they tested this theory and noted it was detrimental in some use cases. The better solution was to figure out ways to avoid the locks. As far as the OP: Last I checked SQL_CALC_FOUND_ROWS does not use indexes. So it becomes a table scan when running this then returns x amount of rows from it. People have suggested wordpress should not being doin this because on larger datasets this query is much slower than a count and a select of just the limited data set. The reason for this is obvious the key lookup for a count is way faster than a complete scan of the table then dropping the dataset not needed. There has been suggestions of improvements to this but I have not seen it. One way to make this faster is to run a repair on the table. Which removes the fragmentation of the table which makes the scans faster. The real solution though is wordpress shifts to a count + select combination for this. It may be faster with a few hundred rows but a few thousands and a count + select is way faster.
Posted by Steven, 10-26-2010, 03:28 AM
The people who have issues with it apparently have not done much work with it. I won't give out my trade secrets on open forum. To many competitors pick it up and state it is their own - and it ends up coming back to me when those customers come to me for additional help. By losing table locking issues you are going to increase the performance of the blog 10 fold. Also - percona rpms have several changes that make innodb desirable. (in case your not familiar, they are the guys who also run http://www.mysqlperformanceblog.com/) Smart my.cnf configurations also increase performance. Last edited by Steven; 10-26-2010 at 03:32 AM.
Posted by sOliver, 10-26-2010, 07:34 PM
I've been using supercache for a very long time, but thanks for the tip. percona rpms are definitely worth a try when I optimize my server again. I still have a lot of overhead on my tables, but I will add a cronjob to optimize my tables regularly to prevent that. Anyway, for me it was enough to improve sql and httpd settings, run repair on all my tables (not just wordpress tables!) and optimize slow queries(disable plugins etc) Before: Server load 1-2, Memory 70-80% After: Server load 0.3 - 1, Memory 30-50% SQL queries on index page: 16 .. site is now loading in less than 0,3 seconds. Pretty happy about it and so will be Google.