6

I am having very high CPU spikes on mysqld process (greater than 100%, and even saw a 300% at one point). My load average is around: .25, .34, .28.

I read this great post about this issue: MySQL high CPU usage

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

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

  1. If my mysqld process is spiking over 100% every couple seconds shouldn't my load average be higher then they are?
  2. What will disabling persistent links do - will my scripts continue to function as is?
  3. If I turn this off and reload php what does this mean for my current users as there will be many active users.

EDIT:

CPU Info: Core2Quad q9400 2.6 Ghz

Community
  • 1
  • 1
bMon
  • 962
  • 4
  • 15
  • 33

2 Answers2

8

Persistent connections won't use any CPU by themselves - if nothing's using a connection, it's just sitting idle and only consumes a bit of memory and occupies a socket.

Load averages are just that - averages. If you have a process that alternates between 0% and 100% 10 times a second, you'd get a load average of 0.5. They're good for figuring out long-term persistent high cpu, but by their nature hide/obliterate signs of spikes.

Persistent connections with mysql are usually not needed. MySQL has a relatively fast connection protocol and any time savings from using persistent connections is fairly minimal. The downside is that once a connection goes persistent, it can be left in an inconsistent state. e.g. If an app using the connection dies unexpectedly, MySQL will not see that and start cleaning up. This means that any server-side variables created by the app, any locks, any transactions, etc... will be left at the state they were in when the app crashed.

When the connection gets re-used by another app, you'll start out with what amounts to dirty dishes in the sink and an unflushed toilet. It can quite easily cause deadlocks because of the dangling transactions/locks - the new app won't know about them, and the old app is no longer around to relinquish those.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • thanks for the toilet analogy :) So persistent links option in my php.ini is fine but just don't use mysql_pconnect - got it. – bMon Aug 18 '11 at 16:09
  • At what point (number) should one start worrying about the load averages? Over 1.0, 5.0, 20.0? – bMon Aug 18 '11 at 16:14
  • 2
    nothing wrong with persistent connections, as long as you have proper "zomg I just loaded up my pants" error handling in case any of your scripts - if the script dies, then issue a close call on the connection so mysql can clean up. – Marc B Aug 18 '11 at 16:15
  • I've seen systems with loads in the 20+ range that were still perfectly responsive. Load average is just a simple diagnostic and doesn't really tell you anything about how busy the rest of the system is. You can have a load average of 0.01 and the system is crawling because something's tying up the disks completely. High cpu load by itself means nothing. – Marc B Aug 18 '11 at 16:17
  • Acceptable load depends on the number of CPUs you have. A single CPU with a consistent load of 1 will see degredation, but 4 CPUs may not until they hit a load of 4. In a virtual environment where the CPUs are shared, this is also impacted by the need to step all the CPUs at the same time, waiting for loads on other VMs. Point is, it gets a little complex to talk load outside the context of the specific machine. – Josiah Jul 25 '16 at 14:45
0

Spikes are fine. This is MySQL doing work. Your load average seems appropriate.

Disabling persistent links simply means that the scripts cannot use an existing connection to the database. I wouldn't recommend disabling this. At the very least, if you want to disable them, do it on the application later, rather than on MySQL. This might even increase load slightly, depending on the conditions.

Finally, DB persistence has nothing to do with the users on your site (generally). Users make a request, and once all of the page resources are loaded, that is it, until the next request. (Except in a few specific cases.) In any case, while the request is happening, the script will still be connected to the DB.

Brad
  • 159,648
  • 54
  • 349
  • 530