MySQL High CPU Usage and Permalinks

Question

I have very high CPU spikes in the mysqld process (over 100%, and even in one place - 300%). The average load is: .25, .34, .28.

I read this wonderful article about this issue: High CPU utilization of MySQL

One of the main tasks is to disable persistent connections. So I checked my php.ini and mysql.allow_persistent = on and mysql.max_persistent = -1 , which means no restrictions.

This raises a few questions for me before changing anything to be sure:

• If my mysqld process is gaining more than 100% every couple of seconds, shouldn't my average load level be higher than them?
• What will disable permalinks - will my scripts work as they are?
• If I disable this and restart php, what does this mean for my current users, as there will be many active users.

EDIT:

Processor Information: Core2Quad q9400 2.6 Ghz

Answer 1

Persistent connections will not use any processor on their own - if nothing uses the connection, it just sits idle and consumes only a little memory and takes up a socket.

Averages are averages. If you have a process that alternates between 0% and 100% 10 times per second, you will get an average load of 0.5. They are good for identifying a long-term, stable high processor, but by their nature hide / erase the signs of spikes.

Persistent connections to mysql are usually not needed. MySQL has a relatively fast connection protocol, and any time savings from using persistent connections is pretty minimal. The disadvantage is that as soon as the connection is persistent, it can be left in an inconsistent state. for example, if an application using the connection dies unexpectedly, MySQL will not see it and will not start cleaning. This means that any server variables created by the application, any locks, any transactions, etc., will be left in the state in which they were when the application crashed.

When the connection will be reused by another application, you will start with what constitutes dirty dishes in the sink and uninsulated toilet. This can quite easily cause deadlocks due to broken transactions / locks - the new application will not know about them, and the old application is no longer going to refuse them.

Answer 2

The ears are fine. This is MySQL doing the job. The average load value seems appropriate.

Disabling permalinks simply means that scripts cannot use an existing database connection. I would not recommend disabling this. At least if you want to disable them, do it in the application later, and not in MySQL. It can even slightly increase the load depending on the conditions.

Finally, database persistence has nothing to do with users on your site (usually). Users make a request, and as soon as all the resources of the page are loaded, that is, until the next request. (Except for a few specific cases.) In any case, while the request is executed, the script will still be connected to the database.