Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a server with multiple WordPress sites running on it. The server configuration is quite standard: CentOS, Apache, MySQL, cPanel.

I have noticed that often I see the following lines in the mysqladmin process list for different WP databases:

| Id | User | Host | db | Command | Time | State | Info
+---------+------------------+-----------+------------------+----------------+------+----------------------+---------
| 4688637 | DELAYED | localhost | salr4e1_wrdp2 | Delayed insert | 208 | Waiting for INSERT |

I cannot understand why the insert is shown as delayed? If there are several such databases in the process list, then the load is growing up. It does not make my server feel well.

I'm trying to use mysqlcheck -aro to repair such databases. Sometimes it helps but often not and I have to kill such processes to decrease the server load.

share|improve this question
    
What storage engine are you using for that table? –  EEAA May 2 '11 at 14:42
    
@ErikA MyISAM, I should have mentioned it in the first post, sorry. –  Andrew May 3 '11 at 13:34

1 Answer 1

up vote 1 down vote accepted

Please notice your username is DELAYED. This is an internal username for mysqld to process any INSERT DELAYED statements, which only works on MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables.

This may be the right time to switch your WordPress databases from MyISAM to InnoDB. If you do switch to InnoDB, change all INSERT DELAYED statements to INSERT or the statements will generate errors.

UPDATE

You have several options

Option 1) Move each WP database into a separate DB server (Bare-Metal or Virtual)

You can configure each database to the needs of the particular WP database. Of course, there are budget considerations outside the scope of this question.

Option 2) Create several instances of MySQL on the same DB server, one for each WP database

You can configure each database to the needs of the particular WP database, but datadir for each WP database should reside on a separate disk volume.

Option 3) Scale up the DB server

Add lots of more RAM and uses a faster disk volume. Then, configure the MySQL instance to utitlize as much RAM for MyISAM as possible. Keep in mind, MyISAM caching is only for Index Pages and not Data.

Option 4) Combination of Options 2 and 3

Option 5) Configuration of delay options

delayed_insert_limit: after performing this many INSERT DELAYED statements, pending SELECTs statements is given priority to run before allowing new INSERT DELAYED statements.

delayed_insert_timeout: seconds handeler thread waits before timing out.

delayed_queue_size : per-table limit of the number of rows. If queue is full, any new INSERT DELAYED statements will pause until the queue has room.

Options 6) My original proposal. Convert everything to InnoDB !!! You could cache both Data and Index pages in the InnoDB buffer pool.

share|improve this answer
    
It will take some time to switch all WP sites to InnoDB because I also have other CMS on the server set up. Is there any other solution to prevent multiple WP databases from overloading the server? Should I play with such variables like delayed_insert_timeout (currently as 300) or delayed_insert_limit? Thank you. –  Andrew May 3 '11 at 13:56
    
@user78650 You should also play with delayed_queue_size. I also updated my answered with your question as part of option 5. –  RolandoMySQLDBA May 3 '11 at 15:40
    
Thank you very much for your comprehensive answers. –  Andrew May 3 '11 at 17:50

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.