PHP in combination with MySQL is extremely slow

Question

I am currently experiencing slowness with one of my servers. It is running an apache2 server with PHP and MySQL. The MySQL server is hosted on the same machine as the webserver itself.

Whenever I request a PHP file containing MySQL queries the page needs approximately 24 seconds to show up. While requesting the page the CPU usage of apache2 goes up to 11% (!) which is very much in comparison to what it used to be a week ago.

Non-PHP files or PHP files without MySQL queries are showing up immediately.

What could be causing the problems with scripts containing MySQL queries? I was unable to find any useful information inside the apache error logs.

I have access to the entire server. Unfortunately I'm unsure where the logs are being saved under Debian. The directory /var/log/mysql is empty, thus I assume it's storing them elsewhere. And the files /var/log/mysql.err and mysql.log are empty. — beta, Commented Dec 9, 2011 at 19:55
Does query size effect the response time at all? That is, if you write a simple identity that doesn't touch any tables does it still take half a minute to respond? — Jonathan Rich, Commented Dec 9, 2011 at 19:58
I tried to do a couple of MySQL queries manually and they responded really fast when I logged in to the MySQL server via a terminal. They are very slow when I do them via a PHP script though, it takes ages to respond. — beta, Commented Dec 9, 2011 at 20:03
Make an isolated PHP page with just db connection code and a single query and run it then run the same query in the terminal, see if there is any difference in speed... — nobody, Commented Dec 9, 2011 at 20:11

ajreal · Accepted Answer · 2011-12-09 20:14:56Z

1

In mysql console

show full processlist;  <-- to show what are the current SQL

To check where is the log file:-

show variables like '%log%'; <-- to show mysql variables

When doing query benchmark / testing, always remember to turn off query cache, using :-

set session query_cache_type=off;

edited Dec 9, 2011 at 20:14

answered Dec 9, 2011 at 20:03

ajreal

47.4k11 gold badges96 silver badges119 bronze badges

Thanks for your answer. show full processlist returned two rows - apparently the processlist query itself and a query from a site of mine: | 59 | root | localhost | db1 | Sleep | 20 | | NULL | Time: 20 seems to be fairly high.
– beta
Commented Dec 9, 2011 at 20:09
sleep is sleep zzzzZZZZ ... you should open some of your slow pages, and do show full processlist at the same time for comparison.
– ajreal
Commented Dec 9, 2011 at 20:10
Well but it seems like the page finally loads after this sleep entry disappeared. I tested it with a slower page from a wordpress blog.
– beta
Commented Dec 9, 2011 at 20:26
did you site connect to any external resources? such as your wordpress is hosted at another network or so ? Sleep state is the mysql connection is opened, but is idle, waiting for next query.
– ajreal
Commented Dec 9, 2011 at 20:30
The wordpress blog is hosted on the same server as well.
– beta
Commented Dec 9, 2011 at 20:31

| Show 4 more comments

Kae Verens · Accepted Answer · 2011-12-09 19:57:24Z

1

database queries take time to run, and each query involves opening up at least one file. file access is slow.

you can speed up the requests by running the database in RAM instead of from the hard-drive, but the real answer is probably to cache as much as you can so you're doing as little database querying as possible.

answered Dec 9, 2011 at 19:57

Kae Verens

4,0773 gold badges24 silver badges43 bronze badges

Thanks for your answer, but the site used to run really fast like a week ago. All of sudden it's very slow though, so I assume some kind of timeout is triggered? I lack the background knowledge though.
– beta
Commented Dec 9, 2011 at 20:01

Add a comment |

Cybercartel · Accepted Answer · 2011-12-09 20:09:05Z

1

You can check if the mysql database is greater then 2GB (or 4GB) because of some cms logging function and exceed a file size limit.

answered Dec 9, 2011 at 20:09

Cybercartel

12.6k7 gold badges39 silver badges73 bronze badges

I googled for a query to obtain the database size and it seems like it's only about 2.1 MB small.
– beta
Commented Dec 9, 2011 at 20:14

Add a comment |

Collectives™ on Stack Overflow

PHP in combination with MySQL is extremely slow

3 Answers 3

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related