PHP slow to talk to MySQL on same host

Question

I have a VPS (OpenVZ) running Ubuntu 10.04:

nginx 1.0.5
php 5.3.5
mysql 5.1.41

I have installed WordPress on it and noticed in Firebug that the initial connection to index.php takes several seconds (from 4 up to 13 seconds at one time).

What makes me think it's an issue with MySQL:

if I install a caching plugin that bypasses the database, the lag dissapears
the queries themselves execute in a couple of miliseconds

I've tried addding skip-name-resolve or skip-networking to my.cnf as suggested here, with no luck.

One other thing I noticed is that the php-fpm process spikes to 100% CPU load while the page is generated.

I have tried using apache instead and even nginx in front of apache, but now the apache process hogged all the CPU.

One last thing: I run this exact same application on another VPS with similar configuration and there's no delay; index.php loads in under half a second.

Any suggestions on how I might find out what the problem is?

Does connecting to the database via the command line exhibit the same delay? — Shane Madden, Sep 01 '11 at 18:31
@Shane Madden: Nope; if I log into ssh and then run the `mysql` command, it's fast. — scribu, Sep 01 '11 at 18:36
What process exactly is a top CPU consumer when CPU usage is at 100%? Is it mysql? — Alex, Sep 01 '11 at 18:38
@Shane Madden: Hm... it's actually php-fpm that's spiking. But when I enable the caching, it doesn't anymore. — scribu, Sep 01 '11 at 18:43
What happens when you run all the queries from the page from the commandline? — Dana the Sane, Sep 01 '11 at 20:12
@Dana the Sane: Like I said, the actual query time is small: 0.77 seconds in total. — scribu, Sep 01 '11 at 20:59

score 2 · Answer 1 · answered Sep 01 '11 at 18:49

2

If php-fpm uses 100% of CPU this is hardly MySQL-related problem, so you should start to examine your PHP code to find which piece of code causes the delay. I recommend to use XDebug for this.

answered Sep 01 '11 at 18:49

Alex

7,789
4
36
51

Thanks for the suggestion, but it is MySQL related, since it only happens when MySQL is involved; maybe it has something to do with the PHP MySQL driver. – scribu Sep 01 '11 at 18:53
It's good to get a sample of profiling data anyway. It will show you the exact place where your app spends so much time. Another approach is to try to call `strace -p` on php-fpm process but strace data won't be relevant enough since strace knows nothing about your app internals. – Alex Sep 01 '11 at 18:56
I tried switching to apache and the problem persists, only now it's the apache process hogging the CPU. The funny thing is that I run the same app on a similar VPS with no delay. So yeah, I guess profiling is the last hope. – scribu Sep 01 '11 at 20:10
Ok, did a profiling, looked at it through KCacheGrind; didn't learn anything. :| – scribu Sep 01 '11 at 21:02
Okay, maybe it's time to strace, but just to be sure, could you please upload your cachegrind.out somewhere and post a link here? – Alex Sep 01 '11 at 21:14
Sure: http://lomo.scribu.net/cachegrind.out.3974 (it's 12mb). – scribu Sep 01 '11 at 21:18
Thanks, got it and examined it a bit. `make_entry` method of `MO` class is a clear winner (it's in a `mo.php` file). And it was called 3161 times for a single web request! Something is definitely not right with it. – Alex Sep 01 '11 at 21:24
Yeah, but that's used in every WordPress installation (including on the other VPS, which works fine). Thanks for looking though. – scribu Sep 01 '11 at 21:26
By the way, do you use any kind of PHP opcode cache? I see that `require_once` takes a lot of time, please try to install APC or eAccelerator or XCache, all of them are free. – Alex Sep 01 '11 at 21:27
No, I'm not using any opcode caching; need to figure this out first. I did a strace and php-fpm seems to be making tens of thousands of gettimeofday() calls for a single request. – scribu Sep 01 '11 at 21:41
chat: http://chat.stackexchange.com/rooms/1262/discussion-between-scribu-and-alex – scribu Sep 01 '11 at 21:54

score 1 · Answer 2 · answered Sep 01 '11 at 23:38

1

I would check the firewall, the php-fpm to mysql connection (consider switching to pipe/socket).

With all this I suppose the php-fpm was properly installed/upgraded, maybe disable some of the modules.

Hope this helps somehow.

answered Sep 01 '11 at 23:38

Stas

11
1

Default firewall rules; MySQL has `skip-networking` so I presume a socket is used. – scribu Sep 02 '11 at 02:36

score 1 · Accepted Answer · answered Nov 27 '12 at 09:38

try to use IP, instead of hostname when connecting to your MySQL (use dig to diagnose DNS problems),
use High Performance MySQL Tuning Script, which will give you the idea about some problems in MySQL server,
use strace or tcpdump to debug your issues (here are some examples),
upgrade your php & mysql and check if the problem persist,

PHP slow to talk to MySQL on same host

3 Answers3