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) :thumbsup

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

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

impressive difference


did you use the same mysql version?

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 :upsidedow

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

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

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

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

So share with us the magic lines. :winkwink:

Can't wait for Valentines Day!!! :winkwink:

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 :thumbsup

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

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.

Brutality! - 5 points!

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 :)