[RESOLVED] Outgrowing a Dual Xeon server, what now?

[Sxooter]

hehe. yeah, mysql and php use rand() postgresql uses random()

[cadkins23]

Ok, they're all gone. Now I need to figure out again what I have to put in my.cnf in order to log the slow queries again. 😉 I'll get it up now in time for any rush tonight.

[cadkins23]

Hey, --log-queries-not-using-indexes this is an option, but I've tried to put it in the my.cnf file before but without success. I figured I could see if any queries aren't utilyzing an index. It just doesn't seem to log, even when I knew we had some indices missing.

[cadkins23]

So we were talking about switching to myisam or whatever and innodb or whatever a few posts ago. When I look in the mysqld.log file I see this after restarding mysql:

70415 17:42:36 mysqld started
070415 17:42:36 InnoDB: Started; log sequence number 0 67871723
/usr/libexec/mysqld: ready for connections.
Version: '4.1.20' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution

[cadkins23]

Oh no 119 members on the site.. It's a new record. Ahhhh, any moment I guess it'll crash. Actually it's not too slow yet and nothing on the slow query log yet.

[cadkins23]

Ok the site didn't crash, and it didn't log anything. I moved the second limit from 5 down to 3 to hopefully catch some slow queries. 😉 ....WAITING

[Weedpacket]

Sxooter wrote:

the reason you need >= and limit 1 is in case you have gaps in your sequence of ids in the table for things like deleted rows.

That doesn't sound ideal: an id with a single gap before it will be selected twice as often as one that doesn't. The larger the gap, the more likely it will be selected.

Would obtaining the COUNT(*) and picking a random offset from [0,COUNT) be better?

For some reason I didn't get around to posting my comment about OPTIMIZE; Roger Ramjet has already done that (I'm looking very dourly at messages_sent here).

[Sxooter]

No, using offset is not as efficient, as the table gets bigger it has to materialize more and more of the table. The other method I posted in the db forum on selecting random rows gets around the issue of needing to use >= id limit 1, but requires updates to a secondary table every so often.

[Sxooter]

So, what did top / vmstat etc... say during the heavy load?

[Sxooter]

Yeah, you can switch to innodb tables at some later date. It's a minor issue right now. worry about it after you're past this major issue.

[cadkins23]

Here's top at 120 online: (What is zombie, and why so many sleeping?)

top - 19:42:14 up 2 days, 10:46,  2 users,  load average: 17.98, 17.46, 14.94
Tasks: 174 total,   9 running, 164 sleeping,   0 stopped,   1 zombie
Cpu(s): 87.2% us, 10.4% sy,  0.0% ni,  1.8% id,  0.0% wa,  0.2% hi,  0.4% si
Mem:   3115168k total,  2819844k used,   295324k free,    95464k buffers
Swap:  2105336k total,     9556k used,  2095780k free,  1146548k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                    

16514 mysql     15   0  136m  31m 4072 S 99.9  1.0   4:13.45 mysqld                                   

19621 apache    15   0 38012  17m 4368 S 11.9  0.6   0:02.47 httpd                                       

18539 apache    15   0 36516  16m 4516 S 11.6  0.5   0:05.12 httpd                                       

19752 apache    15   0 33472  13m 4240 S 11.2  0.4   0:01.73 httpd                                      

19758 apache    15   0 33536  13m 4280 R 10.3  0.4   0:02.26 httpd                                       

16939 apache    15   0 38332  18m 4472 S  9.9  0.6   0:12.40 httpd                                      

19750 apache    15   0 36176  16m 4344 S  9.6  0.5   0:02.36 httpd                                       

16740 apache    16   0 37040  17m 4448 S  9.0  0.6   0:13.34 httpd                                      

18540 apache    15   0 38448  18m 4556 S  9.0  0.6   0:05.30 httpd                                      

19757 apache    15   0 33476  13m 4268 S  9.0  0.4   0:01.48 httpd                                       

18516 apache    15   0 36420  16m 4384 S  8.7  0.5   0:05.19 httpd                                       

19742 apache    15   0 33768  13m 4300 R  8.7  0.5   0:01.65 httpd                                      

16643 apache    15   0 39240  19m 4576 S  8.0  0.6   0:11.16 httpd                                      

16726 apache    15   0 39024  19m 4584 S  8.0  0.6   0:11.09 httpd                                       

15581 apache    15   0 38340  18m 4440 S  7.4  0.6   0:09.24 httpd                                       

18504 apache    16   0 35276  15m 4316 S  7.4  0.5   0:04.34 httpd                                      

19753 apache    15   0 37568  17m 4392 S  7.4  0.6   0:02.28 httpd                                      

19756 apache    15   0 38116  18m 4412 S  7.4  0.6   0:01.73 httpd                                      

16630 apache    15   0 36524  16m 4440 S  7.1  0.5   0:09.70 httpd                                      

14587 apache    15   0 38744  18m 4464 S  6.7  0.6   0:17.70 httpd                                      

18234 apache    16   0 38524  18m 4440 R  6.7  0.6   0:07.14 httpd                                       

20107 apache    15   0 33952  13m 4256 R  6.7  0.5   0:00.54 httpd                                       

15993 apache    15   0 39092  19m 4588 S  6.4  0.6   0:14.37 httpd                                      

19710 apache    15   0     0    0    0 Z  6.4  0.0   0:02.03 httpd <defunct>                            

19730 apache    16   0 34540  14m 4272 S  6.1  0.5   0:01.62 httpd                                       

19731 apache    15   0 33516  13m 4260 S  6.1  0.4   0:01.93 httpd                                       

14590 apache    15   0 37772  17m 4476 S  5.8  0.6   0:12.79 httpd                                       

16743 apache    15   0 38348  18m 4516 S  5.8  0.6   0:11.57 httpd                                       

16949 apache    15   0 38536  18m 4484 S  5.8  0.6   0:11.42 httpd                                      

15665 apache    15   0 38016  18m 4444 S  5.1  0.6   0:13.59 httpd                                      

16636 apache    15   0 37632  17m 4444 S  4.5  0.6   0:12.11 httpd                                       

18543 apache    15   0 37008  16m 4388 R  4.5  0.6   0:04.99 httpd                                       

16612 apache    16   0 38012  18m 4524 S  4.2  0.6   0:10.81 httpd                                       

17810 apache    15   0 38428  18m 4456 S  4.2  0.6   0:07.72 httpd                                       

18537 apache    15   0 36536  16m 4428 S  4.2  0.5   0:04.92 httpd                                       

14639 apache    15   0 37660  17m 4544 S  3.9  0.6   0:15.50 httpd                                      

16584 apache    15   0 38984  18m 4456 S  3.9  0.6   0:12.40 httpd                                      

16707 apache    16   0 34024  14m 4444 R  3.9  0.5   0:08.72 httpd                                       

17674 apache    15   0 38476  18m 4544 S  3.5  0.6   0:07.22 httpd                                       

20135 apache    15   0 34000  13m 4072 S  3.5  0.5   0:00.64 httpd                                       

17807 apache    15   0 37636  17m 4456 S  3.2  0.6   0:07.00 httpd                                       

 9682 apache    15   0 42976  23m 4676 S  2.9  0.8   0:20.79 httpd                                      

17794 apache    15   0 36572  16m 4372 S  2.9  0.5   0:07.29 httpd                                      

19637 apache    15   0 36564  16m 4364 S  2.2  0.5   0:01.38 httpd                                       

18517 apache    15   0 36540  16m 4428 S  1.9  0.5   0:05.64 httpd                                       

20136 apache    15   0 33592  13m 4188 S  1.9  0.4   0:00.74 httpd                                      

19755 apache    15   0 33712  13m 4240 S  1.3  0.4   0:02.09 httpd                                      

18538 apache    15   0 36844  16m 4384 S  1.0  0.6   0:05.98 httpd                                       

17864 root      15   0  2152 1084  804 R  0.3  0.0   0:00.58 top                                        

19743 apache    15   0 33516  13m 4256 S  0.3  0.4   0:01.46 httpd             

[Sxooter]

Zombie is a crashed / dead process, i.e. something went wrong. Sleeping is waiting to run, usually waiting on the db or I/O access.

[cadkins23]

I did netstat and got this:
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
19 1 9556 141732 96352 1172700 0 0 43 22 38 7 53 12 33 2

Then I did nestat -t > 128.txt and got this: (128 people online when I did this)
www.potspace.com/128.txt

[cadkins23]

Sxooter wrote:

Zombie is a crashed / dead process, i.e. something went wrong. Sleeping is waiting to run, usually waiting on the db or I/O access.

Ok well I would see it disappear, and a minute later another zombie for a few seconds, etc.

[Sxooter]

Overall, this is a big improvement, since the server isn't crashing and you actually have some idle time on your CPUs showing up. Only 2% idle in vmstat and the 0.0% wait in top mean you're NOT I/O bound, which means you've still got a lot of room left for improvement.

How big is your database, in megabytes? If it's around 130 meg or so then mysql is fitting it all into memory and you don't need to give it more memory to play with. If it's noticeably bigger than that you might need to tune your db to use more memory.

Did you find anymore slow queries? I'm guessing that elimintating the super slow order by rand() limit 1 queries gave you quite a bit of breathing room.

The zombie processes bother me. They mean that for some reason the apache / php process they're running crashed. that normally shouldn't be happening.

[cadkins23]

Well I wasn't putting the log code in the right area, so after retrying for 2 hours I finally figured out where to put it lol and then the logging began. I had lowered it down to 1 second so now I raised it back to 5 seconds, cleared it and am waiting to see. We have 136 online logged in, wow.

[Sxooter]

Well, start planning on your second server now. If you keep growing like this, your first step will be to move the db to its own server. Or move the apache to another machine and dedicate this machine to db work, depending on what the new machine looks like.

Congrats on handling the load. How's the user experience from your end if you log in and use the site when it's under load?

[Sxooter]

As long as the zombies are short-lived (hahaha) you're ok. They're just waiting to be reaped. It's the ones that stick around for a long time you need to worry about as they're stuck waiting on something.

Are you showing any crashes in your apache error logs? Or the system /var/log/messages log?

[cadkins23]

# User@Host: youradul_dbadmin[youradul_dbadmin] @ localhost []
# Query_time: 8  Lock_time: 0  Rows_sent: 44936  Rows_examined: 89872
select *from members a where 1 = 1 order by a.member_id desc;

How come the rows examined is double the members I have? I know why 1 = 1 is used, because it's more or less a space filler if other filters are used in the search of my site.

[Sxooter]

cadkins23 wrote:

How come the rows examined is double the members I have? I know why 1 = 1 is used, because it's more or less a space filler if other filters are used in the search of my site.

I'm gonna guess that it means that the sort had to hit all the rows a second time. Why are you selecting ALL the users? I'm guessing a limit on that query would speed it up quite a bit.