optimization problems with Mysql/Php

[bhavesh]

I have optimization problems on my web site (http://www.backwash.com).

The configuration of the server is as follows:
I have one box running on Linux (RH7.3)/Apache (1.3.26)/PHP (4.2.2) acting as my Web server and a separate box running on Linux (RH7.1)/MySQL (3.23.49a) acting as my database server. Both these are connected using a VPN.

The problem we face is that the site is intermittently slow (comes to almost a crawl at times). The same page that loads sometimes in 5 seconds takes as much as 400 seconds. I checked the traffic stats on the site and noticed that the traffic is almost similar, averaging at about 600 page views in an hour.

To try to debug the problem further, I tried to run a small test on a local server (intranet) in a controlled environment to see some astonishing results:
We created a file with 1 select query (basically selects some rows from the database and displays them in a table). We had 1 user access this file. We noticed that the page loaded in less than a second (expected). We then asked 10 people to access the same page simultaneously. To our surprise the page loaded in 6 seconds for ALL 10 users. If we increased the number of simultaneous users, the load time increased for EVERYBODY.

I am not sure if this is an issue with locks in MySQL OR is this a Apache/PHP related issue.

Any help would be highly appreciated.

Bhavesh

[tha_mink]

Well...why connected via VPN? I suspect the delay is due to having simultaneous vpn tunnels to the db box. Why not just directly connect to the db box through the mysql port. I assume the db box is protected from the world so why vpn? I bet that is where the delay is.

tha_mink

[[deleted]]

" the load time increased for EVERYBODY."

Why is that surprising? The server has only one CPU, so if 6 people access the page, they can each get 1/6th of the CPU's attention. 600Mhz turns into 100Mhz per user (roughly)

Have you checked the indexes on the database? Even crappy old mysql should not be complaining at only 6 users.

But MySQL does rely heavily on small and simple queries, it tends to choke under heavy load.

Check your indexes, and check your mysql config. The out-of-the-box configuration has very little cache memory allocated. As you are using a dedicated database machine, make sure that mysql is able to use all the available memory.

[tha_mink]

That's rubbish Vincente. We're talking about 6 users. It shouldn't take 6 seconds to do 6 queries. The problem is in the connection to the db box. I have a db box that does 1000 times that without blinking. MySQL on linux should be able to do 20000 selects in 40 seconds not...40 selects in 40 seconds.

[[deleted]]

"That's rubbish Vincente. We're talking about 6 users. It shouldn't take 6 seconds to do 6 queries. The problem is in the connection to the db box. I have a db box that does 1000 times that without blinking. MySQL on linux should be able to do 20000 selects in 40 seconds not...40 selects in 40 seconds."

Don't you think it is justa little naive to assume that it must be the connection just because your machine is fast?

You don't know anything about what his database is like, he could be doing a seq-scan on 40 million records. But your database is fast, so his database must be fast too.

The connection is probably the last thing to cause a 400(!) second slowdown.

But with MySQL doing seq-scans however, a 500 second slowdown is very common.

But you seem to know all about these things, so good luck fixing this one!

[tha_mink]

You're right vincente I know nothing about his database but taking a look at his website I assumed we were dealing with fairly simple dataset that was reasonably organized. That said with my p2 300 machine with 64 mb ram running the 2.4 linux kernel (which handles forking a little better than 2.2) I have clocked mysql doing over 600 transactions per second for about 6 minutes with an average of about 30% of that. For me...the network was always the bottleneck. Plus...if you are opening a vpn tunnel every time a user connects...I can only imagine that that takes cycles too. But...with that said..."rubbish" was prolly a bit strong.

Plus...if we WERE dealing with 40 million records it would be a disc I/O and RAM issue and not a cpu issue. wrong?

[vhshah]

Thanks for the replies so far. To give you some additional details, I did a MySQL SHOW STATUS on the live site. You can see it from the following URL:
SHOW SCAN

This might give a clue.

Also, I am not sure about the VPN issue. The servers are a part of the hosting company and they say that they are connecting in their VPN. However, we access the DB server using the -h option in mysql. Is this still a problem?

Regarding increasing the cache memory, could you direct me to where I could find it and what is an ideal value for this (not the out-of-the-box) value.

I did not understand the seq-scans? Does this mean sequential scans? If you are talking about the EXPLAIN syntax, I tried this and got a result of 22589.

As you said, why should the same page take a few seconds to load in certain circumstances and as high as 400 seconds in other circumstances. We have put traces in every page on the site to record the start time and end time (we record this in a separate server_stat table and clean it every day after taking a dump as it can build very rapidly). From the traces, I am unable to predict a pattern or failure. The server seems to be running just fine (all pages load within 10 - 15 seconds on an average) for maybe couple of hours and then for a period of 15 - 20 minutes, the server comes to a crawl. We notice a lot of "unloaded" pages during this time (users who have got tired of waiting as their page have not loaded and closed the browser to moved on or clicked on refresh).

The 10 simultaneous user test was carried out on a local machine connected on a LAN (webserver and db on the same box) just to see the behavior of mysql when multiple people access the same page. We were trying to build on the theory of locking affecting the performance. However, it did not make sense as a read lock does not prevent other users from reading the same table, right? Though the results, in my eyes, were surprising as 10 people could make a huge difference to the performance of a query.

Regards.

[jweissig]

hi,

does this "slow down" happen every day. If so what time of day is there a pattern to the slowness. Do you notice the load increase when users perform specific tasks.

Can you please explain the VPN configuration so that we all can better understand what is happening here. By VPN do you mean local LAN or a remote location? If it is a remote location what type of connection connects them? Also how much data is being transmitted over the VPN?

Also have you check on the linux system to see how the i/o is doing. Run "vmstat" and "ps ax" to check the processes also check out "netstat -na" to see how many open mysql and http connections there are.

The list goes on and on. Check out good for trouble shooting performance problems. This is a science not an art.

• Justin

[jweissig]

Hi,

I noticed that http://adult.backwash.com/content.php?id=170 takes a long time to load. How many database querys are being processed. Can you paste the "exact" sql statments that you are running on the database server.

Thanks
- Justin

[[deleted]]

My comments on more than one reply:

" I assumed we were dealing with fairly simple dataset that was reasonably organized."

Assumption is the mother of all f*ckups. 🙂

"That said with my p2 300 machine with 64 mb ram running the 2.4 linux kernel I have clocked mysql doing over 600 transactions per second"

Nice stats, but quite meaningless.
Try adding a few hunderd thousand records and add a few JOIN/ORDER BY queries, you'll soon find that 600 drops to 20-30 tops.

You cannot say anything about the performance of a database without knowing the databas structure, the amount of records and the types of queryies.

The network is 'a' bottleneck, but with only 6 users I refuse to believe that this is the problem.

"with that said..."rubbish" was prolly a bit strong."

My point exactly 🙂

"Plus...if we WERE dealing with 40 million records it would be a disc I/O and RAM issue and not a cpu issue. wrong?"

A disk/ram issue caused by bad database design. Indexes prevent sequential scans. If you have a good index you can read from a multi-million row table in a few milliseconds. Without a good index you can easily write a query that takes a minute on just 1k rows.

" If so what time of day is there a pattern to the slowness"

Good point, there could be some cron-based task like 'OPTIMIZE' that works heaviliy on the database server, eating away at its cpu-cycles.

MySQL is notoriously awfull at handling high-concurrency, it quickly snowballs to a halt. Well, you've seen that happen already haven't you. 🙂

As both machines are located at a hosting company, I think it's safe to say that the servers are 'part of' a network that has a VPN tunnel to vhshah's location. No sane hosting company would setup a VPN between two servers in the same room, let alone between a webserver and it's database server. (if they did: smack them over the head with a trout!)

Is it a shared database server or dedicated for you alone?

As for the show-status, the mysql manual has a few hints and tips about what's going on when certain values are high: http://www.mysql.com/doc/en/SHOW_STATUS.html

* If Opened_tables is big, then your table_cache variable is probably too small.
* If Key_reads is big, then your key_buffer_size variable is probably too small. The cache hit rate can be calculated with Key_reads/Key_read_requests.
* If Handler_read_rnd is big, then you probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
* If Threads_created is big, you may want to increase the thread_cache_size variable. The cache hit rate can be calculated with Threads_created/Connections.

• If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size variable to get the temporary tables memory-based instead of disk based.

"We were trying to build on the theory of locking affecting the performance."

Simple: Lock = bad.
What kind of operations do you do that 'require' such a lock? And why not change to InnoDB tables, because they support transactions.

[vhshah]

I think I have caused a huge confusion by mentioning the VPN connection. 🙂 Here is the info I have:

We are renting 2 dedicated servers from Rackspace. Earlier (when the site started and was smaller), we just had 1 server that housed both the webserver and database server. We then moved to this 2 machine configuration as we were experiencing increased traffic. Rackspace told us that they have connected these 2 servers on their VPN so that in future scalability is easy. I guess their VPN is connected to their backbone??? As far as I can tell, the 2 machines are connected on a LAN.

We connect to the database server using -h and IP of the database server. mysql_connect (IP of DB server, username, password).

I hope the above anwers the questions about the VPN configuration. If not, please ask specific quesions so that I can get the answers from Rackspace.

I noticed that http://adult.backwash.com/content.php?id=170
takes a long time to load

How long is a "long time"? I tried this and it loaded in under 5 seconds. If you know your IP, I can tell you the exact time it took for you to load the page (from the traces we have put on each file). Probably what you noticed is the problem. If you keep trying this throughout the day (at different times), you will probably notice different load times.

does this "slow down" happen every day. If so what time of
day is there a pattern to the slowness. Do you notice the load
increase when users perform specific tasks

This slow down happens inconsistently, but many times during the day. It kind of happens at least 4 - 5 times on "bad" days and 1 - 2 times on "good" days. From the trace, I could not predict a pattern that made sense. The only thing I do not know is what is the server doing during the times of a "slow down". Is there anyway I can log the activities of a server (cpu utilization, processes, etc.) so that when the site is sluggish, I can cross verify what the server is doing to get a clue.

As far as pasting the exact queries are concerned, let me explain the concept at Backwash a little:
1) We have pages (or columns) - you might have seen "Surf a Column" box on the index page.

2) A page is maintained by a columnist

3) A columnist posts content grouped in journals (think of it as the different issues of a newspaper).

In fact, newspaper is a great simily where the newspaper is a page, a specific issue of the newspaper is a journal and the content inside the newspaper is content. The link you tried to access (content.php) is a journal (which houses multiple content).

Therefore, the queries are as follows:
1) Get the latest journal for the page:
SELECT journal_id, journal_desc, journal_date, journal_title FROM journal WHERE page_id = $id AND journal_current = 'Y';

2) Get the content for this journal:
SELECT content_id, content_title, content_url, content_desc, con.con_expirydate FROM content AND journal_id = $journalid ORDER BY content_id

Loop through these content to display them on the page.

I hope this helps.

[jweissig]

Hi,

Yeah this does help! How are you doing the loop for the results? Can you give me a run down. The page loaded in about 5 seconds like you said. Is this acceptable?

If you are using rackspace i think it is safe to say that your servers are connected to their LAN which is connected to a VPN. So your servers have a direct connection to each other with NO VPN inbetween.

I have an idea on how to find out what times the problems are happening at. Here is what i am thinking will help you trace the problem to a specific time.

On each content page you set a script to track the load time (seems you already have something like this) you should track load time, ip, and time& date stamps and insert these to a table.

at the end of each day run a query that will group by day/hour with a load time over your threshhold.

so you would have something like this

date/time threshhold_over
12 am 1
1 pm 5
2 pm 50
3 pm 200
4 pm 50
5 pm 5

do you see what i mean.. if not i can explain a little better.. i have been up for a long time!

• Justin

[vhshah]

Hi Justin,

Thanks for your valuable time. Here are some of the responses:

How are you doing the loop for the results? Can you give me a
run down

It is a simple while($objRS = mysql_fetch_array($objResult)) loop. Inside the loop, we are building the HTML for the individual rows.

We are already maintaining a similar record of the hours when the load time of some key files are over the acceptable limit. I am attaching an XLS file that has the data for the past 4 days for index.php

As you will see from the file that server does not slow down consistently at the same time everyday.

Regards.

[jweissig]

hi,

Do you have a loop inside of the outer loop?

As for the data that you posted this is only a small snapshot of days.. But it does show that most timeouts happen later in the day than in the morning hours. Do you have web_server logs that show what how many people are accessing for pages through these trouble times. maybe that will help in solving this problem.

If you are only running the 2 querys above then this is really weird because these should NOT produce a huge load on the server. How many data records are being returned? Also have you run these querys on the actual database server during these time and how happens?

Maybe this has nothing to do with the database and some things to do with your webserver of even something else. Next time this happends login to the database server and from console run the specific querys and see what happens.

Thanks for your time,
- Justin

[jweissig]

hi,

Also how large (many records) do you have in the journal table? Did you create and index on the fields you are doing the WHERE by. This might help improve performance.

• Justin

[jweissig]

hi,

sorry for the many, many posts but i just thought of this. I just checked out your main page and it took about 25 seconds to load. It seems to stop is some places and then fast in other.

For example the header on your website loads really fast but it seems to pause in places. So what if you have something that will take multiple timestamps throughout a page.

For example you have 5 places you take timestamps.

1 = init
2 = db_connect#1
3 = db_end#1
4 = db_connect#2
5 = end

so then is it takes a really long time from 2 - 3 you know that there is something wrong with the first database connection.

check http://www.pornresource.com/Articles/tdnewarticle12.shtml & http://www.onlamp.com/lpt/a/1592 for example code!

here is something i found on optomiing php http://phplens.com/lens/php-book/optimizing-debugging-php.php

Thanks
- Justin

[vhshah]

Originally posted by jweissig
sorry for the many, many posts but i just thought of this. I just checked out your main page and it took about 25 seconds to load. It seems to stop is some places and then fast in other.

For example the header on your website loads really fast but it seems to pause in places.

The reason for this is that the header is a separate HTML table so that it is displayed by the client browser. In the next segment there are lots of "boxes" where we load "pre-compiled" text files to avoid queries (as data in these boxes remain static for a day). Some data is dynamic and hence queried from the database. Thus you see different load times for different boxes.

Also, you must understand that the example of 2 queries I gave you earlier is not the only thing that slows down the site. What I was trying to communicate was that even a page with 2 simple queries that typically loads very fast can take long time when the server is slow. I am not implying that the 2 queries slow down the server.

As you have suggested, there could be something with the webserver or other server processes that could be hogging CPU time, slowing the responsiveness of MySQL. Can you tell me if there is a way from within PHP to trap the CPU utilization so that I can include that in my trace. Therefore, I can figure out if the slow load time is related to CPU utilization of the database server. Also, can I know the different processes being run - that might give me a clue that a "backup" process is hogging CPU hence slow load times. Can I get any useful information from SHOW PROCESSLIST mysql command?

I really appreciate your time. I am going through the URLs given by you to see if I can get something from there.

Regards.

[bhavesh]

Is there any way by which i can avoid the call to the database every time i fetch a row like below.

while ($row = mysql_fetch_array ($result))
{
// Code to display result and loop
}
mysql_free_result ($result);

In the above case for every row i fetch i am accessing the database, am i right? Hence the more the number of rows the more trips to db. And in our case since the two boxes (web server and mysql ) are connected in a LAN, i guess it will take even longer.

Instead i would like to store the entire result set in an array and process accordingly, which i think would be much faster.

Any help on this would be appreciated.

Bhavesh

[jweissig]

Hi,

I think it should not connect to the database if you are calling the result set but i am not sure because you include the free result function at the end of the code block!

Here is a little tip that i do when i am developing an application in my dev environment. I load the database with far more information that i will ever use. For example i am working on something now that i expect will have a about 50,000 rows at all times and will not grow. I have loaded it with about 500,000 so that i know how to scale my code.

Also you can run mysql so that it will output all queries made by a specific session to a log file! If you have a development server run safe_mysqld using the "--log=" option so that you can see all queries when you load a page!

i use "/usr/local/mysql/bin/safe_mysqld --log=/var/log/mysqld.log" and then i "tail -f /var/log/mysqld.log" (makes it scroll) in a console windows so that i can see when i load a page what is happening on the database side! This is a really good trick that has really helped me solve some performance problems.

Thanks for your time,

• Justin

[jweissig]

Hi,

I think it should not connect to the database if you are calling the result set but i am not sure because you include the free result function at the end of the code block!

Here is a little tip that i do when i am developing an application in my dev environment. I load the database with far more information that i will ever use. For example i am working on something now that i expect will have a about 50,000 rows at all times and will not grow. I have loaded it with about 500,000 so that i know how to scale my code.

Also you can run mysql so that it will output all queries made by a specific session to a log file! If you have a development server run safe_mysqld using the "--log=" option so that you can see all queries when you load a page!

i use "/usr/local/mysql/bin/safe_mysqld --log=/var/log/mysqld.log" and then i "tail -f /var/log/mysqld.log" (makes it scroll) in a console windows so that i can see when i load a page what is happening on the database side! This is a really good trick that has really helped me solve some performance problems.

Thanks for your time,

• Justin