Y'all wanna see some amazing F'N MySQL performance?? - GoFuckYourself.com

Varius · 02-08-2005, 11:26 PM

I was configuring our new master DB server today, to try and tweak it as much as I can for MySQL performance. Now obviously the more powerful hardware helps, but I think these settings I found are pure gold ;p

The machines:

DB1 (new one) = Quad-Xeon 3.2 Ghz w/ 4G ram
DB2 (demo one) = Dual-Xeon 1.8 Ghz w/ 3G ram

Checkout some of these results:

1 - Very complex join query, using subqueries and whatnot through 15-20 tables each containing 1-2 million records

DB1 = 0.57 seconds
DB2 = 38.02 seconds

2 - OPTIMIZE TABLE command on each table in the database (140 tables)

DB1 = 29 mins
DB2 = 5 hours, 16 mins

3 - Complete import of 10 gig database

DB1 = 17 mins
DB2 = 2 hours 34 mins

I've tried many other types of queries, counts on huge tables, etc...and have found the new server handles things 20,50 or even 100x faster !!!!!!!!!

I can only imagine a nice Cluster of identical machines like this....that should scale to 100 million users (well by which time something better should be out heh)

8 Characters · 02-08-2005, 11:28 PM

Well your 1st computer is ridiculously more powerful than the second. What did you expect?

SpaceAce · 02-08-2005, 11:30 PM

Quote:

Originally Posted by Varius

I was configuring our new master DB server today, to try and tweak it as much as I can for MySQL performance. Now obviously the more powerful hardware helps, but I think these settings I found are pure gold ;p

I think the real gold is the gold used to manufacture the hardware in the first box. That's a raw power difference, there.

SpaceAce

Theo · 02-08-2005, 11:34 PM

impressive difference

did you use the same mysql version?

Varius · 02-08-2005, 11:34 PM

Yes but regardless of the power difference, mysql settings have a lot to do with it. I've ran on a quad-xeon before with 2-3 G of ram, and was never this performant.

Usually, I'd only see 2 cpus being used and not much of the RAM....with the right settings, I can see solid 40-70$ usage on all four cpus now, and most of my ram being used.

Anyhow, I'm just so happy because the machine blew away my previous performance expectations

Varius · 02-08-2005, 11:35 PM

Quote:

Originally Posted by Soul_Rebel

impressive difference

did you use the same mysql version?

Yes, MySQL 4.1.9-max running on Redhat Enterprise 3.0 (i think)

tical · 02-08-2005, 11:44 PM

nice work man, im gonna bug you in the future haha

V_RocKs · 02-08-2005, 11:47 PM

Yeah.. going to have to bug you in the future too..

While you are at it, help out MPA(2,3)...

xfalmp · 02-08-2005, 11:51 PM

So share with us the magic lines.

KRL · 02-09-2005, 12:00 AM

Can't wait for Valentines Day!!!

Varius · 02-09-2005, 12:24 AM

Quote:

Originally Posted by xfalmp

So share with us the magic lines.

There are a ton of them, and I'm not sure how much they will help you (as they need to be customized per machine), but here you go:

[mysqld]
back_log = 50
max_connections = 500
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache = 8
thread_concurrency = 8
query_cache_size = 32M
query_cache_limit = 1M
ft_min_word_len = 4
memlock
default_table_type = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log_bin
log_slow_queries
long_query_time = 2
log_long_format
server-id = 1
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 32M
bulk_insert_buffer_size = 16M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 4
myisam_recover
skip-bdb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

* Note, I am using InnoDB for most of my table types.

Enjoy

Varius · 02-09-2005, 12:25 AM

Quote:

Originally Posted by KRL

Can't wait for Valentines Day!!!

Hrm I wonder whatever you coud be excited for ? A Hot date ? hehe

dubsix · 02-09-2005, 03:12 AM

Quote:

Originally Posted by Varius

There are a ton of them, and I'm not sure how much they will help you (as they need to be customized per machine), but here you go:

[mysqld]
back_log = 50
max_connections = 500
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache = 8
thread_concurrency = 8
query_cache_size = 32M
query_cache_limit = 1M
ft_min_word_len = 4
memlock
default_table_type = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log_bin
log_slow_queries
long_query_time = 2
log_long_format
server-id = 1
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 32M
bulk_insert_buffer_size = 16M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 4
myisam_recover
skip-bdb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

* Note, I am using InnoDB for most of my table types.

Enjoy

I run a pretty similar configuration on our larger mysql boxes, also the big question here is how much cache is on those processors? If the old 1.8s were only 512k even bumping them up to 1meg (which all 3.2ghz+ xeons are a minimum of) would make a huge difference, not to mention the 1.8s were non-nt so you've effectively quadrupled your processor count (yeah yeah 4 are virtual) and doubled your cache.... that alone is a HUGE boost

rickholio · 02-09-2005, 03:16 AM

Quote:

Originally Posted by WireSix-Ryan

I run a pretty similar configuration on our larger mysql boxes, also the big question here is how much cache is on those processors? If the old 1.8s were only 512k even bumping them up to 1meg (which all 3.2ghz+ xeons are a minimum of) would make a huge difference, not to mention the 1.8s were non-nt so you've effectively quadrupled your processor count (yeah yeah 4 are virtual) and doubled your cache.... that alone is a HUGE boost

I'm also curious about any changes to disk subsystems... when you start getting into those huge, multi-million record tables, disk performance plays an increasingly significant role.

AlienQ - BANNED FOR LIFE · 02-09-2005, 03:16 AM

Quote:

Originally Posted by V_RocKs

Yeah.. going to have to bug you in the future too..

While you are at it, help out MPA(2,3)...

Brutality! - 5 points!

Varius · 02-09-2005, 03:18 AM

Quote:

Originally Posted by WireSix-Ryan

I run a pretty similar configuration on our larger mysql boxes, also the big question here is how much cache is on those processors? If the old 1.8s were only 512k even bumping them up to 1meg (which all 3.2ghz+ xeons are a minimum of) would make a huge difference, not to mention the 1.8s were non-nt so you've effectively quadrupled your processor count (yeah yeah 4 are virtual) and doubled your cache.... that alone is a HUGE boost

You are correct, I checked and they were 512K cache cpus versus 1 meg ones

One thing I tested out (that I read somewhere) about the virtual cpus, is to not include them where thread_concurrency is concerned. It should be set to 2x your number of cpus, and after a quick test, setting it to 8 (2x4) does perform better by about 10% than setting it to 16

02-08-2005, 11:26 PM	#1
Varius Confirmed User Industry Role: Join Date: Jun 2004 Location: New York, NY Posts: 6,890	Y'all wanna see some amazing F'N MySQL performance?? I was configuring our new master DB server today, to try and tweak it as much as I can for MySQL performance. Now obviously the more powerful hardware helps, but I think these settings I found are pure gold ;p The machines: DB1 (new one) = Quad-Xeon 3.2 Ghz w/ 4G ram DB2 (demo one) = Dual-Xeon 1.8 Ghz w/ 3G ram Checkout some of these results: 1 - Very complex join query, using subqueries and whatnot through 15-20 tables each containing 1-2 million records DB1 = 0.57 seconds DB2 = 38.02 seconds 2 - OPTIMIZE TABLE command on each table in the database (140 tables) DB1 = 29 mins DB2 = 5 hours, 16 mins 3 - Complete import of 10 gig database DB1 = 17 mins DB2 = 2 hours 34 mins I've tried many other types of queries, counts on huge tables, etc...and have found the new server handles things 20,50 or even 100x faster !!!!!!!!! I can only imagine a nice Cluster of identical machines like this....that should scale to 100 million users (well by which time something better should be out heh) __________________ Skype variuscr - Email varius AT gmail

02-08-2005, 11:34 PM	#5
Varius Confirmed User Industry Role: Join Date: Jun 2004 Location: New York, NY Posts: 6,890	Yes but regardless of the power difference, mysql settings have a lot to do with it. I've ran on a quad-xeon before with 2-3 G of ram, and was never this performant. Usually, I'd only see 2 cpus being used and not much of the RAM....with the right settings, I can see solid 40-70$ usage on all four cpus now, and most of my ram being used. Anyhow, I'm just so happy because the machine blew away my previous performance expectations __________________ Skype variuscr - Email varius AT gmail

02-08-2005, 11:44 PM	#7
tical Confirmed User Join Date: Feb 2002 Location: Las Vegas Posts: 6,504	nice work man, im gonna bug you in the future haha __________________ 112.020.756

02-08-2005, 11:51 PM	#9
xfalmp Confirmed User Join Date: Aug 2002 Location: Brazil Posts: 1,474	So share with us the magic lines. __________________ Trust the "WOW" effect. Last edited by xfalmp; 02-08-2005 at 11:53 PM..

02-09-2005, 12:00 AM	#10
KRL Entrepreneur Join Date: Oct 2002 Location: USA Posts: 31,429	Can't wait for Valentines Day!!! __________________ If you would like to develop your domains, you can lease inexpensive foreign labor from the leaders in the field at iWebmasters.com TO LOWER YOUR COSTS AND INCREASE YOUR PRODUCTION! * * * * * * * * * * * * Domains Adult News KRL's Newsletter Biz Tips Just Listed Domains

02-08-2005, 11:28 PM	#2
8 Characters So Fucking Banned Join Date: Jan 2005 Location: This month's character: Dirty Franck Posts: 2,336	Well your 1st computer is ridiculously more powerful than the second. What did you expect?

02-08-2005, 11:34 PM	#4
Theo HAL 9000 Industry Role: Join Date: May 2001 Posts: 34,515	impressive difference did you use the same mysql version?

02-08-2005, 11:47 PM	#8
V_RocKs Damn Right I Kiss Ass! Industry Role: Join Date: Dec 2003 Location: Cowtown, USA Posts: 32,409	Yeah.. going to have to bug you in the future too.. While you are at it, help out MPA(2,3)...