-1

Our site has 3,000+ concurrent users when we open our registration on our website. The site was too slow and get the server down sometime, even sub accounts websites are down. Free memory was 400MB.

We upgraded our server to 8Gb for us to have a larger allocated memory. After upgrading our site still encountering server down/ slow site. What should be the best setting for our my.cnf so that our MySql Server would not give up?

This is currently our setting:

[mysqld]
default-storage-engine=MyISAM
performance-schema=0
max_allowed_packet=512M
thread_cache_size=256K
tmp_table_size=1G
max_heap_table_size=1G
open_files_limit=1000
max_connections=3000
interactive_timeout=180
wait_timeout=180
key_buffer_size=400M
slow_query_log = ON

And now, this is the top result: enter image description here

Any suggestion is highly appreciated.

  • Have you identified any queries that could be tuned? – Philᵀᴹ Jul 16 '18 at 9:06
  • Yes, we also have set indexes and recode some heavy queries. But its not enough I think. – c.k Jul 16 '18 at 9:10
  • Any (error) messages in the logs? – John aka hot2use Jul 16 '18 at 9:17
  • 1
    default-storage-engine=MyISAM means nothing until DDL queries are executed. What engine(s) is used on existing tables? If MyISAM, to convert them to InnoDB seems to be a good idea. – Akina Jul 16 '18 at 9:31
  • 2
    3000+ concurrent users and you’re running the webserver and database on the same machine? – Colin 't Hart Jul 16 '18 at 19:09
2
default-storage-engine=MyISAM  -- change all your tables to InnoDB
performance-schema=0    -- good
max_allowed_packet=512M
thread_cache_size=256K  -- NO!  20 is usually good enough; it is not in bytes!
tmp_table_size=1G       -- Not more than 1% of RAM
max_heap_table_size=1G  -- Not more than 1% of RAM
open_files_limit=1000
max_connections=300
interactive_timeout=180
wait_timeout=180
key_buffer_size=400M     -- For MyISAM, 20% of RAM; change after adding RAM
slow_query_log = ON      -- Use to find 'worst' queries

3,000+ concurrent users - Meaning that 3000 are logged in at the same time? No; you have a limit of 300. Meaning that 3000 might each log in once a month? What? "Queries per second" is a more interesting metric.

The main issues:

  • MyISAM does "table locking", which slows down activity when you have several users simultaneously running queries. InnoDB is much better at concurrent use.
  • The two table_sizes are dangerously high, especially without any swap space. But, since you did not mention crashing, those settings won't explain your issues.
  • thread_cache_size is chewing up many megabytes, maybe a gigabyte -- Unnecessarily!
  • Anything that says (or implies) "cache" should not be raised arbitrarily. Running out of RAM is much worse than slowing down some cache a little.
  • For MyISAM (not for InnoDB), key_buffer_size is the main tunable -- it controls how much 'cache' to have for indexes on the tables. It does not need to be any bigger than the total size of all the tables' indexes (see SHOW TABLE STATUS, but also it should not take up too much of RAM -- the "free" RAM is needed for caching data blocks. 20% of RAM (800M for 4GB of RAM, or 1600M for 8GB) is a reasonable setting without further details.
  • Use pt-query-digest to find out what is already in the slowlog. Let's study the worst one or two queries. But note long_query_time is probably defaulted to the nearly-worthless 10 seconds. Crank it down to 1.

For a more thorough analysis of the settings, follow the instructions here .

| improve this answer | |
  • 1
    @c.k Have you applied any of Rick James's Jul 16 suggestions? Any progress? Please follow the instructions for a 'more thorough analysis' as well. – Wilson Hauck Jul 23 '18 at 18:48
  • @RickJames Thank you. I have follow some of you advice. This is my setting as of now. default-storage-engine=InnoDB query_cache_type=2 innodb_buffer_pool_size = 4000M performance-schema=0 max_connections=3000 slow_query_lo = ON key_buffer_size=50M – c.k Aug 1 '18 at 9:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.