Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I found a superb post here about optimising WordPress for large installs.

One of the steps refers to lighthttpd which sounds excellent but isn't currently supported by DirectAdmin. Last time I messed about with a non-DA installation I managed to break my server and had to pay a professional to get it fixed...

One of its other steps is to ALTER all of my MySQL tables to use the InnoDB engine instead of MyISAM.

This appeals to me because it would appear to fix some of the major problems I've been having - i.e. table locks. The following is an example of my MySQL's SHOW PROCESSLIST; including SQL statements:

| 17181 | my_db | localhost | my_db | Query   | 35117 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'boraras-brigittae' AND wp_po |
| 17182 | my_db | localhost | my_db | Query   | 35118 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page |
| 17183 | my_db | localhost | my_db | Query   | 35117 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'boraras-brigittae' AND wp_po |
| 17184 | my_db | localhost | my_db | Query   | 35117 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'boraras-brigittae' AND wp_po |
| 17185 | my_db | localhost | my_db | Query   | 35041 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'akysis-vespa' AND wp_posts.p |
| 17186 | my_db | localhost | my_db | Query   | 35050 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('images','profiledotline-gif') A |
| 17187 | my_db | localhost | my_db | Query   | 35050 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('images','picarrow-gif') AND (po |
| 17188 | my_db | localhost | my_db | Query   | 35043 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page |
| 17189 | my_db | localhost | my_db | Query   | 35041 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('m','marginal') AND (post_type = |
| 17190 | my_db | localhost | my_db | Query   | 34989 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'piaractus-brachypomus' AND w |
| 17191 | my_db | localhost | my_db | Query   | 34989 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('w','wet-dry-filter') AND (post_ |
| 17192 | my_db | localhost | my_db | Query   | 34990 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17193 | my_db | localhost | my_db | Query   | 34954 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'corydoras-sterbai' AND wp_po |
| 17194 | my_db | localhost | my_db | Query   | 34954 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'piaractus-brachypomus' AND w |
| 17195 | my_db | localhost | my_db | Query   | 34955 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17196 | my_db | localhost | my_db | Query   | 34954 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'piaractus-brachypomus' AND w |
| 17197 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17198 | my_db | localhost | my_db | Query   | 34955 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17199 | my_db | localhost | my_db | Query   | 34898 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page |
| 17200 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17201 | my_db | localhost | my_db | Query   | 34898 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('forums','lofiversion','index-ph |
| 17202 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('forums') AND (post_type = 'page |
| 17204 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page |
| 17205 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'sawbwa-resplendens' AND wp_p |
| 17206 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'badis-sp-buxar' AND wp_posts |
| 17207 | my_db | localhost | my_db | Query   | 34898 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'melanotaenia-boesemani' AND  |
| 17208 | my_db | localhost | my_db | Query   | 34898 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'betta-prima' AND wp_posts.po |
| 17209 | my_db | localhost | my_db | Query   | 34865 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('forums') AND (post_type = 'page |
| 17210 | my_db | localhost | my_db | Query   | 34897 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17211 | my_db | localhost | my_db | Query   | 34898 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page |
| 17213 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'piaractus-brachypomus' AND w |
| 17214 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17215 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('knowledge-base') AND (post_type |
| 17216 | my_db | localhost | my_db | Query   | 34865 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17217 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'piaractus-brachypomus' AND w |
| 17218 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'badis-sp-buxar' AND wp_posts |
| 17219 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('forums','lofiversion','index-ph |
| 17220 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'betta-prima' AND wp_posts.po |
| 17221 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17222 | my_db | localhost | my_db | Query   | 34865 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'piaractus-brachypomus' AND w |
| 17223 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'piaractus-brachypomus' AND w |
| 17224 | my_db | localhost | my_db | Query   | 34865 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'hemichromis-lifalili' AND wp |
| 17225 | my_db | localhost | my_db | Query   | 34864 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page |
| 17226 | my_db | localhost | my_db | Query   | 34862 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'apistogramma-borellii' AND w |
| 17227 | my_db | localhost | my_db | Query   | 34863 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'melanotaenia-lacustris' AND  |
| 17228 | my_db | localhost | my_db | Query   | 34862 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'melanotaenia-lacustris' AND  |
| 17229 | my_db | localhost | my_db | Query   | 34861 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'mikrogeophagus-altispinosus' |
| 17230 | my_db | localhost | my_db | Query   | 34861 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.ID = 25503 AND wp_posts.post_type = 'atta |
| 17231 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'piaractus-brachypomus' AND w |
| 17232 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17233 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('knowledge-base') AND (post_type |
| 17234 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page |
| 17235 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('forums') AND (post_type = 'page |
| 17236 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17237 | my_db | localhost | my_db | Query   | 34803 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('forums') AND (post_type = 'page |
| 17238 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('m','migrate') AND (post_type =  |
| 17239 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'hemichromis-lifalili' AND wp |
| 17240 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page |
| 17241 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('forums') AND (post_type = 'page |
| 17242 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17243 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page |
| 17244 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('forums','lofiversion','index-ph |
| 17245 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page |
| 17246 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'publish' AND post_type IN ('post', 'page |
| 17247 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('forums') AND (post_type = 'page |
| 17248 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'geophagus-brokopondo' AND wp |
| 17249 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT   wp_posts.* FROM wp_posts  WHERE 1=1  AND wp_posts.post_name = 'hyphessobrycon-amandae' AND  |
| 17253 | my_db | localhost | my_db | Query   | 34788 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AN |
| 17255 | my_db | localhost | my_db | Query   | 34786 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('knowledge-base') AND (post_type |
| 17260 | my_db | localhost | my_db | Query   | 34786 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('s','silver-sand') AND (post_typ |
| 17262 | my_db | localhost | my_db | Query   | 34786 | Waiting for table level lock | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name IN ('forums') AND (post_type = 'page |
| 25505 | root      | localhost | NULL      | Query   |     2 | NULL                         | SHOW PROCESSLIST   

I've enabled the slow-query-log and the output (having been run through an analysis script) can be found here.

However, as I've attempted to ALTER my tables, the following error has occurred on wp_posts: ERROR 1214 (HY000) at line 14: The used table type doesn't support FULLTEXT indexes

So, my questions are kinda three-fold, but kinda just one:

  • Can I use InnoDB and keep the FULL-TEXT INDEXES that WordPress appears to use?
  • Will using InnoDB help to alleviate the problems I've been having with table-locks?
  • Can I use InnoDB without having an adverse effect on my WordPress installation?*

*I have myriad plugins including W3 Total Cache, Simple Forums, AdRotate and many more.

share|improve this question
    
Note: WordPress does not create or use FULLTEXT indexes on any of its tables. If you have those, then you had a plugin create them. –  Otto Jul 21 '12 at 20:56
add comment

2 Answers

There's quite a lot of information on here about switching to InnoDB

http://wordpress.stackexchange.com/search?q=innodb

There are a number of things to think about:

  • InnoDB is helpful with you're faced with contention - ie when you have tables that are being written to as well as being read
  • InnoDB does not support FULLTEXT indexes so plugins that rely on that will either break or work slowly.
  • You can choose which tables to convert to InnoDB - it's not an all or nothing deal - on some of my sites, wp_comments is the only table that's InnoDB because of the massively bursty nature of comments on those sites.

However, it sounds like you've got more fundamental issues that need to be sorted out before you worry about switching to InnoDB.

  • you really need to log your slow queries and then EXPLAIN them to understand what's going on - you might just need to optimise tables or add additional indexes - it's quite likely that some of your plugins are executing queries that are forcing sequential searches on your database. Find out and add indexes to speed these up, if that's the case.
  • Once that's done, you can then look at optimising Apache or switching to a more advanced setup (eg nginx + PHP-FPM).
  • Check your caching. If the site isn't changing much, most content should be served up from the cache instead of hitting the database. Find out why this isn't happening.

In short, it doesn't feel like you've really got to the root causes of the problem. Until you get a better handle on that, anything you do is going to be a bit random.

share|improve this answer
    
Well, I've tried - the two posts linked in my OP weren't exactly well responded-to, and I have zero experience in this department :( –  dunc Jul 18 '12 at 23:33
    
@dunc the first thing you should do is search for info on MySQL slow query logging - start with that - it's really unlikely that a simple switch to InnoDB is going to help unless you're actually getting lock contention - ie lots of writes at the same time as reads (which is possible if you've got a busy forum or lots of people commenting). –  anu Jul 18 '12 at 23:40
    
Also, there's some excellent advice in the answers to your DBA post: dba.stackexchange.com/questions/20652/optimising-mysql. Follow through on that. –  anu Jul 18 '12 at 23:43
    
I think as Anu said there are fundamental issues here that are not related to the SQL and these need to investigated first. you could have the best tuned SQL DB but if you have low server memory and do not control apache its all a waste –  Damien Jul 19 '12 at 8:02
    
@anu if you check this post ( stackoverflow.com/q/11410343/864245 ), you'll be able to see my SHOW PROCESSLIST; which has a load of Waiting for table level lock statuses. We have semi-busy forum, comments and Q&A sections and our content is updated multiple times every day. I've enabled slow query logging - including being run through an optimiser - and the output can be found here ( pastebin.com/6m8RuX2F ). I'll update my post with this information too. –  dunc Jul 19 '12 at 11:26
show 3 more comments

I recently played around with an Ubuntu LAMP stack and a WordPress install and looked at changing to InnoDB or NGINX.

Just to set the scene, I had Next Gen Gallery plugin running and I noticed it's tables were 'different'. So I thought to investigate changing to InnoDB.

Can I use InnoDB without having an adverse effect on my WordPress installation?
First things first, don't change to InnoDB it will break stuff in ways you can't imagine. And you're trying to optimise your website and not have to start everything all over again. You are better off working with what you have first.

If you want to see what happens set up a test site and use an export of your current database, with all the same plugins, etc. It will generate a DB failed connection error and you'll just have to go to SQLyog or Sequel Pro and restore from a SQL backup.

Optimisation
I've setup a site which is scaled across two 2 DB using hyperDB 3 front end servers, Load balanced and a CDN. It was load tested to 2000 concurrents / 30000 visitors per day.

All this is managed very well with W3 Total Cache, a MemCached server, Hyper DB and CDN. I'm using the default mysql profile for a medium website.

In my case, I'm lucky, the content changes only weekly so i can cache everything.

Apache Max Connections Default profiles for Apache will allow for 150 max clients and 1000 max requests so if you have 150 visitors you could have 150,000 requests on your one server.

Set this to 5 or 15 max clients to start with increase Max Requests to 2000 (which means before Apache spawns a new process)

Set Spare Threads to 1 or 2 - This is memory reservation that Apache keeps in reserve.

Set Keep Alive timeout to 15 - which means 15 seconds - so if the same request comes to apache, a new thread is not started

see http://www.devside.net/articles/apache-performance-tuning

out of memory - apache / database / timeout
What I have learnt about locked tables is that database sessions stay active too long and this causes the out of memory you're having. From painful experience. it's a one / two combo punch - you think you need more database connections, so you increase these and the knock on effect is running out of memory on the DB.

You didn't say why you can't cache more and reduce the DB requests? Also is the DB server on a different box to apache - that makes a big difference.

mqsql.cnf
this will be empty by default ... do a scan under i think usr/share/doc/mysql-server-5.0/examples/my-medium.cnf.gz

I know I started with my-small.cnf and after separating the DB from Apache everything stabilised

Alternative PHP Cache (APC)
In reference to your 5 essential optimisation steps http://www.619cloud.com/blog/5-essential-steps-for-hosting-wordpress/
You'll probably find that APC is not running on your server. What does W3 Total Cache tell you when you go to WordPress > Performance > General and click on Compatibility Check -- what is the results?

Finally ... this is what the site I setup is identical too ...
http://www.rackspace.com/blog/deploying-scalable-wordpress/

share|improve this answer
    
Hi mate. Thanks for that. I've actually got APC running correctly (installed, verified by W3 and actually being used by W3). The problem is, we're paying £80/month for this dedicated server - why should I need additional servers and CDNs? It's not like we're getting a massive amount of visitors (30K/week) –  dunc Jul 18 '12 at 21:56
    
CDN will reduce the number of requests on your webserver which means less apache connections (which eats up more server memory). What about going to WPEngine or similar that will manage your wordpress server for you as £80 per month / 30k per week is probably a best option. ALSO ... Go check your Apache Max Connections, usually by default this will eat up your server ram and that causes SQL to run out of memory. –  Damien Jul 18 '12 at 22:06
    
@dunc actually ... focus on Apache Tuning - reduce the number of active sessions and you'll stabilise your DB. What monitoring tools do you have on your server? Get Webmin and the Server History extension to watch the load and performance –  Damien Jul 18 '12 at 22:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.