2

Our webservers write some tracking information into a MySQL database (using INSERT DELAYED into MyISAM table). When a huge SELECT query is executed on this table or when it is locked for another reason, the webserver processes (with INSERT DELAYED) are waiting for the database and in some cases the MaxServer limit is reached in Apaches, so they will stop serving requests.

We use INSERT DELAYED because

The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that is very useful if you have clients that cannot or need not wait for the INSERT to complete. This is a common situation when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete.

Quote from MySQL documentation.

I am wondering why the Apache processes are waiting for the INSERT DELAYED to finish. And what can I do to just send the data and forget about it. (Since this is logging data, I do not care if we lose some entries.) Even when the table is locked the PHP script should just go on and should not wait for an answer of MySQL.

(We do not want to setup Master-slave for this table but we are thinking about move this data to some NoSQL database. But for now I would like to know why INSERT DELAYED is not working as expected.)

1 Answer 1

1

It sounds to me like you're doing the right stuff to get what you want, and what you describe is the way it's supposed to work. Thoughts:

  • Check your MySQL thread count -- is MySQL running out of threads and therefore blocking the PHP/Apache processes?
  • Are you using mysql_pconnect() in PHP? I'm not sure how the persistent connection is handled but if it's blocked by the long select somehow, it could be interfering with the inserts.
1
  • We are using mysql_connect() and max_insert_delayed_threads = 20. AFAIKS MySQL is not running out of threads but I will have a deeper look into it.
    – sw0x2A
    Jan 4, 2011 at 19:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .