Re: Correctly setting mysql options for a huge DB with intense trafic

[Brian Parris]

Toni Shocker Wrote:
-------------------------------------------------------
> Thanks for your interest!
>
> 1. 64bit
>
Excellent.

> 2. Copy paste from mysqltuner:
> [--] Data in MyISAM tables: 773M (Tables: 299)
> [--] Data in InnoDB tables: 4G (Tables: 72)
>
Drop your innodb_buffer_pool_size down quite a bit(try 1-2GB). Due to issues in the way the table cache/buffer pool is utilized past a certain point you're better off letting the server filesystem cache take care of caching the table files(especially with 24gb ram).

> 3. HyperThreading, 8 shown in /proc/cpuinfo
>
Tune mysql as if you had 4 cores, check out the hyperthreading section in this article: http://mysql.rjweb.org/doc.php/memory

> 4. Yes
>
> 5. Hm, this is quite hard to estimate since there
> are hundreds of different queries. What/how
> exactly does this affect? If it's really important
> I will try to estimate it though, even if it may
> take some time (meaning I can't do it right now
> but if you really need it I will do it)
>
Check out the sort_buffer_size and read_rnd_buffer_size sections is this article i wrote: http://blog.golden-tech.com/2010/11/is-it-time-for-a-database-tuneup/

> 6.
> Sort_merge_passes = 13
> created_tmp_tables = 87k
> created_tmp_disk_tables = 1224
> Connections = 1054201 (the game emulator has
> persistent connections, the others are from
> phpmyadmin/website, and even if the website is set
> to use persistent connections, we all know how
> multi-threaded apache handles persistent
> connections)
> threads_created = 575
> select_full_join = 2056 (the game emulator does 0
> select_full_joins, the 2k come from the website
> and that happens way too rarerly to take in
> consideration)
> opened_tables = 2010
> and uptime = 282521 (~3.2 days)
>
> 7.
> tmp_table_size = 16M (default)
> max_heap_table_size = 16M (default)
> thread_cache_size = 64
> sort_buffer_size = 2M
> read_rnd_buffer_size = 128k

Ratio of tmp tables to disk tmp tables isn't bad, though with the amount of ram that server has it may be worth increasing tmp_table_size and max_heap_table_size.
Not really sure it would be worth increasing sort_buffer_size for only 13 sort_merge_passes as this buffer is allocated per connection, if you see sort_merge_passes go way up after normal use revisit this setting.
Threads_created looks good for the number of connections you've had.



What are the values of key_reads and key_read_requests(MyISAM key_buffer?
What is the memory usage on the server when it tops out(mysql usage/free/fs cache)?
What is the iowait(%wa in top)/cpu used by mysql/idle cpu/system cpu when it tops out?
what are the values of status variables 'innodb_%'?



Edited 2 time(s). Last edit at 11/20/2010 12:27AM by Brian Parris.