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

[Sxooter]

Just remember, what you REALLY need to do is get your db to start logging long runnig queries and start optimizing them. You can get much bigger performance increases by fixing the bad code than by trying to slap a bandaide on it by going to bigger hardware, etc...

[cadkins23]

Sxooter wrote:

What version of mysql are you running, may I ask? I was looking through the docs for 5.0 and found this page, don't know if there's something like it for 3.23 or whatever you're running. But it would be mighty useful I'd think:

http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

Actually I was logging slow queries using something like log_slow_queries or something, I forget. Anyway it produced quite a list of queries but I don't know that there are any problems with these queries. Once they start bottlenecking for taking a while for any reason, they will be recorded in the log. Care to take a look through the log?

[Sxooter]

Sure, I'll take a look. does MySQL save the time it took the query to run?

Also, you know you can adjust the max time it takes to show up. set it to something like 5 seconds at first, we're looking for the big ones first.

[Piranha]

Post one or two of the queries that gives problems here along with the database structure for the tables in the queries and we can take a look to see if there is something obvious that is wrong.

[cadkins23]

I've gone through the log before, and I can't seem to find anything, but I usually hire freelance php developers to take care of changes. If you want to see the slow queries that were logged: www.potspace.com/mysql-slow.log warning it's a 1mb file because I left it on for a few days, and I did only log slow queries taking 5 seconds or more. 🙂

Not to sound like a complete moron, but my server error log shows errors in my current script. I've asked programmers to tackle them in the past, but I don't think they ever did anything.

[cadkins23]

As for the database structure:
members

member_id int(10) UNSIGNED No auto_increment

display_name char(255) latin1_swedish_ci No

member_name char(50) latin1_swedish_ci No

member_email char(100) latin1_swedish_ci No

member_password char(50) latin1_swedish_ci No

email_verify char(1) latin1_swedish_ci Yes NULL

gender char(6) latin1_swedish_ci No

dob char(25) latin1_swedish_ci No

flag char(1) latin1_swedish_ci Yes NULL

last_login char(25) latin1_swedish_ci No

enable int(11) Yes NULL

current_state char(25) latin1_swedish_ci No

birth_day char(30) latin1_swedish_ci No

birth_month char(20) latin1_swedish_ci No

birth_year char(20) latin1_swedish_ci No

salutation char(5) latin1_swedish_ci No

zip_code char(5) latin1_swedish_ci No

online_now char(1) latin1_swedish_ci Yes NULL

country char(50) latin1_swedish_ci No

pack_id int(10) No 0

here_for char(255) latin1_swedish_ci No

zip char(255) latin1_swedish_ci No

music char(3) latin1_swedish_ci No 0

genre int(10) No 0

city char(255) latin1_swedish_ci No

views int(10) No 0

myspace_id int(15) Yes NULL

occupation char(255) latin1_swedish_ci No

body_type char(255) latin1_swedish_ci No

height_feet char(10) latin1_swedish_ci No

height_inch char(10) latin1_swedish_ci No

website char(255) latin1_swedish_ci No

ethnicity char(255) latin1_swedish_ci No

featured char(10) latin1_swedish_ci No

comment_setting int(10) No 1

friend_setting int(1) No 0

status char(10) latin1_swedish_ci No

start_time char(255) latin1_swedish_ci No

expire_time char(255) latin1_swedish_ci No

enabled char(10) latin1_swedish_ci No

night_featured char(10) latin1_swedish_ci No

display_url char(255) latin1_swedish_ci No

send_email char(10) latin1_swedish_ci No

xspf_url char(255) latin1_swedish_ci No

notifications char(10) latin1_swedish_ci No

newsletters char(10) latin1_swedish_ci No

require_email char(10) latin1_swedish_ci No

hide_online char(10) latin1_swedish_ci No

show_birthday char(10) latin1_swedish_ci No

no_pic_forwarding char(10) latin1_swedish_ci No

friend_only_blog_comments char(10) latin1_swedish_ci No

block_friend_music char(10) latin1_swedish_ci No

friend_groups char(10) latin1_swedish_ci No

im_privacy int(10) No 3

group_email_notifications char(10) latin1_swedish_ci No

display_groups_profile char(10) latin1_swedish_ci No

disable_html_profile_comments char(10) latin1_swedish_ci No

disable_profile_pics_comments char(10) latin1_swedish_ci No

disable_html_blog_comments char(10) latin1_swedish_ci No

last_ip char(20) latin1_swedish_ci No

num_pics int(10) No 0

next_email int(10) No 0

forumname char(50) latin1_swedish_ci No

paying tinyint(1) No 0

online_setting int(2) No 0

messages
mess_id int(10) UNSIGNED No auto_increment

mess_by int(11) Yes NULL

mess_to int(11) Yes NULL

deleted int(11) Yes NULL

subject char(200) latin1_swedish_ci Yes NULL

date_posted char(255) latin1_swedish_ci Yes NULL

mess_read int(11) Yes 0

mess_replied tinyint(1) No 0

messages_sent
mess_id int(10) UNSIGNED No auto_increment

mess_by int(11) Yes NULL

mess_to int(11) Yes NULL

mess_text blob BINARY Yes NULL

deleted int(11) Yes NULL

subject varchar(200) latin1_swedish_ci Yes NULL

date_posted varchar(255) latin1_swedish_ci Yes NULL

mess_read int(11) Yes

I copied/pasted, sorry if it's TMI... My girlfriend and I are going to her parents for dinner, but I'll be back later. I truly appreciate everyone's help. Notice all of the fields in the members table. I think each time something small like updating a members online status, it locks the database members table and bottlenecks things. Why couldn't I just create an online_status table and have the member_id if that person is online or something?

Also for messages and messages_sent I think the read status really makes things difficult because each time a message is simply read, the table is locked while the status is updated. I've mentioned this to programmers in the past but they don't really do much.

[Piranha]

First a question, why do you use SELECT * everywhere instead of selecting what to get? It is much better to just select what you need.

Basically I can see 3 queries that are slow:

// Why do you check for deleted <> 1? Just set it to 1. Is the mess_by column indexed?
update messages_sent set deleted = 1 where mess_by = 11888 and deleted <> 1;

// If music is indexed I can't see what would cause it in this query. 
select * from members where music = '1' ORDER BY RAND() LIMIT 1;

// Use gender = 'Male' instead, don't know if it causes that much problem though. I also think that ORDER BY RAND() might cause trouble in both this query and the query above.
select * from members where featured='1' and music != '1' and gender like 'Male' ORDER BY RAND() LIMIT 1;

I'll let you do the work of looking to see if there are other queries that is to slow and post them. I'll have one suggestion as well, don't let freelancers just come in and do changes, if you are unlucky you find freelancers that doesn't know what they are doing and will cause problems.

Edit: In the first query above, why do you delete all messages that is sent by a user, why don't you delete the messages from message_id?

[Sxooter]

You need to get rid of all the "order by rand()" queries. order by rand() is one of the most aggregious mistakes I see in SQL over and over.

Basically, if you do something like:

select .... where field='1' order by rand() limit 1

the query executer has to gather ALL the fields, then shuffle them, then pick the first one.

Shufflling by random order is one of those things that has n² increase in time to computer as the dataset grows in size. It doesn't scale.

If you're using myisam tables, then you can do a fast random selection like this:

select min(id) from table;
select max(id) from table;
$id=random($minid,$maxid);
select * from sometable where id >= $id limit 1;

[Roger_Ramjet]

Sorry, my fault, I should have told you to run netstat -i
That will display statistics for the network card(s). Run it with various loads, but particularly when peak load is causing the server to lock up.
netstat

[Roger_Ramjet]

OK, caught up on all the new posts.

Looking at that table structure I agree, it is poor. Whoever set all that up did not really know what they were doing. Trouble is when a new programmer sees it I doubt they are inclined to make any major changes as that will probably mean recoding most of the queries if not the whole site.

The query that I find strange is

# Query_time: 39 Lock_time: 0 Rows_sent: 882 Rows_examined: 882
select * from messages where mess_to = 22549 and deleted <> 1;

It occurs often with times ranging from 6 to 40. There is no way that a simple select like that should take up to 40 seconds. The range of execution times is also strange. Could you run it with EXPLAIN and also can you show us the indexes on that table.

[bpat1434]

If you do decide later on to custom create your own Dedicated server setup, I'd suggest Logicworks or Rackspace.

One of the best things you could do is get the database off the webserver. That is of course in addition to optimizing your queries properly.

[Sxooter]

I'm gonna go out on a limb and guess that the query planner in 4.1 is making a really bad decision when it sees "where somefield <>1"

If what the logic REALLY means is "where somefield=0" then you should see if you can make the change and if that helps.

But I was looking through the slow query logs, and you've got sections where order by rand() limit 1 occur for long periods of time as 2-3 minute long queries. order by rand() limit 1 is a memory / CPU hog, and I think they're what's killing your server.

[Sxooter]

Alright, I'm gonna post a little guide on how to get fast random results in the db forum. I'll post a link to that post when I'm done writing it.

[Roger_Ramjet]

Yes, I saw them Sxooter and I agree, but you had already spoken about them. The thing is, that query I mentioned is taking eg 32 sec while a RAND query running around the same time only took 8???

[Sxooter]

Good point Roger. some of the rand queries aren't as bad as others. I think it might be a combination of the two things that's killing him. 1: The query planner is making a REALLY bad decision on the <> 1 type thing (or the data's horribly non-selective for the whole where clause) AND the order by random() is taking a lot of CPU overhead.

Definitely wasn't disagreeing with ya.

[Sxooter]

Oh, here's my post in the db forum on how to get fast random results.

http://www.phpbuilder.com/board/showthread.php?p=10796322

[cadkins23]

Roger Ramjet wrote:

Sorry, my fault, I should have told you to run netstat -i
That will display statistics for the network card(s). Run it with various loads, but particularly when peak load is causing the server to lock up.
netstat

102 People online when I did the following:

# netstat -i 
Kernel Interface table
Iface       MTU Met    RX-OK RX-ERR RX-DRP RX-OVR    TX-OK TX-ERR TX-DRP TX-OVR Flg
eth0       1500   0 38152270      0      0      0 37516558      0      0      0 BMRU
lo        16436   0   116811      0      0      0   116811      0      0      0 LRU

or to see the netstat log I generated, www.potspace.com/102online_netstat.txt

[cadkins23]

Sxooter wrote:

Oh, here's my post in the db forum on how to get fast random results.

http://www.phpbuilder.com/board/showthread.php?p=10796322

Thanks I was hoping after reading your previous posts about this that I would be able to find more info. on this. I appreciate the link, it'll help me. Right now I've been going through the huge slow query file and I'm basically taking one of each kind of slow query and making a new list, rather than go through thousands. It's very interesting doing this stuff.

[Sxooter]

definitely look at the ones that are clustered together and really slow. Those are mostly the ones with <> '1' , <> 1, and order by rand() limit 1 in them

[cadkins23]

Yes, I've compiled a small list ordered by table, and have begun to work on some of these. Here are some of the more commons ones that I saw.

>>>>>>>>>>>>members
	15 secs
	select * from members where music = '1' ORDER BY RAND() LIMIT 1;

15 secs
select member_id from members where display_name like 'codyyy';

7 secs
select *from members a where 1=1  order by a.member_id desc;

6 secs
select * from members where featured='1' and music != '1' and gender like 'Male' ORDER BY RAND() LIMIT 1;


messages
	7 secs
	SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages`;

8 secs
select * from messages where mess_to = 21547 and deleted <> 1;


messages_sent
	87 secs
	SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages_sent`;

18 - 81 secs
update messages_sent set deleted = 1 where mess_by = 11888 and deleted <> 1;    ///// no longer update, instead delete, just delete them and not where deleted <> 1

6 secs
select * from messages_sent where mess_by = 14293 and deleted <> 1;


messages_bodies
	83 secs
	SELECT /*!40001 SQL_NO_CACHE */ * FROM `message_bodies`;


member_friends
	9 - 56 secs
	select * from member_friends a, journal b where b.journal_of = a.friend_id and a.member_id = 22549 order by journal_id desc limit 0,8;

9 secs
SELECT friend_id FROM member_friends WHERE member_id = '22549' AND friend_id = '22549' LIMIT 1;

9 secs
select count(*) as re from member_friends where member_id = 27893 and approve = 1 and friend_id != 27893;

7 secs
SELECT /*!40001 SQL_NO_CACHE */ * FROM `member_friends`;

6 secs
select max(top_8_id) as mid from member_friends where member_id = '145';


invites
	9 secs
	select * from invites where member_id=39772 and member_email like 'filteredforpublicdisplay';


blogs
	7 secs
	select count(*) as a from blogs where member_id = 43776; ///////Apparently blogs didn't have member_id index - created


profile interests
	14 secs
	SELECT /*!40001 SQL_NO_CACHE */ * FROM `profile_interests`;


testimonials	
	12 secs
	select count(*) as a from testimonials where member_id = 145 and approved = '1';

If something says select blah from blah where X and Y, then you should have both X and Y indexed? The reason I ask is if it says:

select * from members where featured='1' and music != '1' and gender like 'Male' ORDER BY RAND() LIMIT 1;

then should I index featured, music, and gender? I know that there's no reason in this situation to use gender LIKE when we can use gender = and I understand that I need to take out RAND(). I'm a little slow at this and don't like to break things if I can avoid to, so I'll have to wait until Sunday to do this.

Already I've gone through and removed some code that used to register you on the old auction script that I don't use anymore, and when you'd like it would log you into it also. Stuff like that is unnecessary and I'm sure the more unnecessary things the more likely you are to have problems.

Also I saw some queries that look strange to me, and I can't find them anywhere in the files on my server, so perhaps it's a mysql thing. I just can't research anymore tonight i'm exhausted and can barely type. 🙂 This one took 87 seconds

SELECT /*!40001 SQL_NO_CACHE */ * FROM `messages_sent`;

Thanks again, goodnight. Tomorrow I'll be trying to go through as many lines of code as possible to fix some of those things. Unfortunately when I did some initial code searches I see tons of these types of queries, or things like order by rand, etc.