MySQL Forums
Forum List  »  Performance

Correctly setting mysql options for a huge DB with intense trafic
Posted by: Toni Shocker
Date: November 19, 2010 05:12PM

Currently I have 2 big databases on my server (24GB RAM + Intel i7 + 2 HDDs@7200RPM@RAID0, Ubuntu 10.10):
One with static data (~300MB, MyISAM tables) and one with dynamic data (6GB and continuing to increase, InnoDB tables)
It's for a mmorpg game, it needs to support thousands of online players. I don't need safety/error-safe/data-integrity, I need performance (daily backups are enough for data integrity/safety)
I managed to get it to handle at most ~1800 online players, after that it starts to "choke". I know it's not because of the hardware, at first the problems were starting at fewer players but through trial and error regarding mysql settings/fine tuning I managed to get it to a higher amount of supported players...
But now I don't have any ideas on what to change/increase/decrease next, so I am asking for some help in case I'm setting some of the variables wrong (for my database)

So, enough with the stories, this is how my relevant variables look like in my.cnf

join_buffer_size      = 1M -- all JOINs use indexes
sort_buffer_size      = 2M
read_buffer_size      = 128k
read_rnd_buffer_size  = 128k
table_open_cache      = 550
table_cache           = 550
key_buffer              = 64M
key_buffer_size         = 64M
thread_concurrency      = 16 -- CPU has 8 "logical cores"
thread_cache_size       = 64
query_cache_type         = 0 -- I don't need query cache since heavily trafic data changes constantly

max_allowed_packet       = 4M
thread_stack             = 128K
myisam-recover           = BACKUP

innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=7G -- DB has ~6GB, assuming 7GB works ok currently
innodb_additional_mem_pool_size=50M
innodb_log_file_size=5M
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=0
innodb_thread_concurrency=16 -- same as above?
innodb_file_per_table
innodb_table_locks=0

expire_logs_days    = 10
max_binlog_size         = 100M

Options: ReplyQuote


Subject
Views
Written By
Posted
Correctly setting mysql options for a huge DB with intense trafic
3647
November 19, 2010 05:12PM


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.