MySQL Forums
Forum List  »  Performance

Re: Help make my big new server fast
Posted by: Jason Rhoades
Date: October 07, 2010 04:43PM

Thanks for the reply Rick, much appreciated.

Made two changes which helped a bunch- one was disabling the query cache. The other was increasing the RAM allocated to the key buffer. MySQL version is 5.1.32.

Performance up to 3500-4000qps now. Was 8000+ on old box. Was 1500-2000 on this box before recent changes. Load on box comes from 4-5 machines processing files, so the workload is very consistent and homogenous. DB is the bottleneck to the processing, was hoping to see performance go from 8000 to 12000+ but it went the other direction instead.

Four cores on old box, 24 (12 real cores, w/hyperthreading) on new box. Old box 16GB RAM, new box 144GB RAM.

Everything is MyISAM at this point. Always has been.

Network is essentially same is before. Disk before was SAN, now either local mechanical or local Fusion i/o. Even processes (simple deletes for instance) hitting only well-indexed DBs on Fusion i/o take a while longer than they did before. Disks are not that busy, they were before.

Here's some updated pieces of the status since the changes:
+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_blocks_not_flushed | 1874087 |
| Key_blocks_unused | 22977994 |
| Key_blocks_used | 38870652 |
| Key_read_requests | 13221236280 |
| Key_reads | 29178144 |
| Key_write_requests | 585696566 |
| Key_writes | 287676717 |
+------------------------+-------------+

| Variable_name | Value |
+-----------------------+------------+
| Table_locks_immediate | 1113221119 |
| Table_locks_waited | 47579025 |


So is the going theory that performance has decreased due to increased contention due to the additional CPU cores? Is there some way to throttle the system down to only using 4 cores for MySQL? Since the new cores are faster than the old, that should speed things up right?

Looks like we need to move to InnoDB, was hoping to buy some time with the new box but it looks like I shot myself in the foot. Lots off app code uses LOCK TABLES in embedded SQL, will require remediation before we can move.

I admit to being a MySQL noob, but man, coming from MS SQL land, yanking a processor to speed up your box just sounds counterintuitive... :-)

Thanks again,

Options: ReplyQuote


Subject
Views
Written By
Posted
2888
October 04, 2010 11:31AM
958
October 05, 2010 07:27PM
Re: Help make my big new server fast
804
October 07, 2010 04:43PM
1419
October 07, 2010 10:01PM
1028
October 08, 2010 02:27PM
924
October 08, 2010 08:03PM
1443
October 08, 2010 09:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.