MySQL Forums :: Performance :: Correctly setting mysql options for a huge DB with intense trafic


Advanced Search

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 3594 Toni Shocker 11/19/2010 05:12PM
Re: Correctly setting mysql options for a huge DB with intense trafic 1170 Brian Parris 11/19/2010 05:57PM
Re: Correctly setting mysql options for a huge DB with intense trafic 1114 Toni Shocker 11/19/2010 11:42PM
Re: Correctly setting mysql options for a huge DB with intense trafic 1691 Brian Parris 11/20/2010 12:21AM
Re: Correctly setting mysql options for a huge DB with intense trafic 1181 Toni Shocker 11/20/2010 01:28AM
Re: Correctly setting mysql options for a huge DB with intense trafic 1002 Brian Parris 11/20/2010 09:08AM
Re: Correctly setting mysql options for a huge DB with intense trafic 1105 Rick James 11/20/2010 10:19PM
Re: Correctly setting mysql options for a huge DB with intense trafic 996 Toni Shocker 11/21/2010 08:55PM
Re: Correctly setting mysql options for a huge DB with intense trafic 903 Rick James 11/22/2010 12:02AM
Re: Correctly setting mysql options for a huge DB with intense trafic 910 Toni Shocker 11/22/2010 04:49PM
Re: Correctly setting mysql options for a huge DB with intense trafic 930 Shawn Taylor 11/22/2010 07:07PM
Re: Correctly setting mysql options for a huge DB with intense trafic 933 Brian Parris 11/22/2010 09:19PM
Re: Correctly setting mysql options for a huge DB with intense trafic 974 Shawn Taylor 11/23/2010 07:52AM
Re: Correctly setting mysql options for a huge DB with intense trafic 852 Toni Shocker 11/24/2010 09:25AM
Re: Correctly setting mysql options for a huge DB with intense trafic 1078 Shawn Taylor 11/24/2010 07:13PM
Re: Correctly setting mysql options for a huge DB with intense trafic 877 Toni Shocker 11/25/2010 10:24AM
Re: Correctly setting mysql options for a huge DB with intense trafic 794 Shawn Taylor 11/25/2010 12:17PM
Re: Correctly setting mysql options for a huge DB with intense trafic 920 Rick James 11/25/2010 02:54PM
Re: Correctly setting mysql options for a huge DB with intense trafic 781 Toni Shocker 11/26/2010 10:16AM
Re: Correctly setting mysql options for a huge DB with intense trafic 979 Shawn Taylor 11/26/2010 12:22PM
Re: Correctly setting mysql options for a huge DB with intense trafic 840 Toni Shocker 11/26/2010 02:24PM
Re: Correctly setting mysql options for a huge DB with intense trafic 926 Rick James 11/26/2010 03:09PM
Re: Correctly setting mysql options for a huge DB with intense trafic 778 Toni Shocker 11/26/2010 03:31PM
Re: Correctly setting mysql options for a huge DB with intense trafic 857 Rick James 11/26/2010 05:27PM
Re: Correctly setting mysql options for a huge DB with intense trafic 937 Toni Shocker 11/27/2010 07:13AM
Re: Correctly setting mysql options for a huge DB with intense trafic 1036 Rick James 11/27/2010 01:14PM
Re: Correctly setting mysql options for a huge DB with intense trafic 1094 Toni Shocker 11/27/2010 01:47PM
Re: Correctly setting mysql options for a huge DB with intense trafic 867 Shawn Taylor 11/27/2010 03:19PM
Re: Correctly setting mysql options for a huge DB with intense trafic 919 Toni Shocker 11/27/2010 03:21PM
Re: Correctly setting mysql options for a huge DB with intense trafic 937 Shawn Taylor 11/27/2010 04:28PM
Re: Correctly setting mysql options for a huge DB with intense trafic 919 Toni Shocker 11/28/2010 08:31AM
Re: Correctly setting mysql options for a huge DB with intense trafic 1035 Rick James 11/27/2010 09:00PM
Re: Correctly setting mysql options for a huge DB with intense trafic 869 Toni Shocker 11/28/2010 08:56PM
Re: Correctly setting mysql options for a huge DB with intense trafic 900 Rick James 11/28/2010 10:35PM
Re: Correctly setting mysql options for a huge DB with intense trafic 867 Toni Shocker 12/01/2010 05:05AM


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.