1

We've have a shop with WordPress + WooCommerce.

Our Server has 24CPUs and 128GB RAM. A few days we had multiple orders at the same time and the CPU was totally overloaded and the website was down for a long time.

We're using MariaDB 10.2

Does anyone have experience if this is normal or is something wrong configured?

Here is the relevant part of my config file:

#
# * Fine Tuning
#
max_connections         = 500
connect_timeout         = 5
wait_timeout            = 600
max_allowed_packet      = 16M
thread_cache_size       = 128
sort_buffer_size        = 4M
bulk_insert_buffer_size = 16M
tmp_table_size          = 32M
max_heap_table_size     = 32M

#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size         = 128M
#open-files-limit       = 2000
table_open_cache        = 400
myisam_sort_buffer_size = 512M
concurrent_insert       = 2
read_buffer_size        = 2M
read_rnd_buffer_size    = 1M

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine  = InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size   = 50M
innodb_buffer_pool_size = 256M
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 400
innodb_flush_method     = O_DIRECT
  • I/O wait? user-time? how many cpu threads impacted? give us more informations pls. If you are full innoDB, innodb_buffer_pool_size = 256M for a 128GB ram server looks strange. – Jérémy Munoz Apr 5 '18 at 12:20
2

I suppose that you have a lot of disk i/o because of too small key_buffer_size and innodb_buffer_pool_size values. There is no exact recommendations how to tune them but you should notice the next:

key_buffer_size should be bigger than total amount of MYISAM indexes to fit them all in the RAM.

innodb_buffer_pool_size is good to be 1.4 * as big as all your INNODB tables, if RAM is available.

You have to be very careful tuning join_buffer_size and sort_buffer_size (and others such as read_buffer_size and read_rnd_buffer_size, thread stack) values because they are used for each connection and for max of 500 simultaneous connections you can easily exceed your actual available RAM.

Anyway you have to launch the mysqltuner first and thoroughly inspect every line of the report before tuning. Keep in mind that recommendations given by mysqltuner are general and can be inadequate for your specific case.

  • The mysqltuner looks promising. Thanks for the tip and your explanation. – Nepo Znat Apr 5 '18 at 16:39
  • @Sascha Additional information request, please. current complete my.cnf-ini Text results of: A) SHOW GLOBAL STATUS; B) SHOW GLOBAL VARIABLES; C) SHOW ENGINE INNODB STATUS; Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, free -m for a linux/unix free memory report, complete MySQLTuner.com report if readily available. – Wilson Hauck Apr 10 '18 at 17:17
  • @Sascha If you could post the information requested Apr 10, 2018 we couild provide additional helpful suggestions. If any of the tips already provided have been helpful, please upvote and/or accept. Thanks – Wilson Hauck May 6 '18 at 22:47
0

Suggestions to consider for your my.cnf-ini [mysqld] section (even with only part of your my.cnf-ini visible at this time.

read_buffer_size=128K  # from 2M to minimize read data requirements 

read_rnd_buffer_size=256M  # from 1M  to minimize read data requirements 

thread_cache_size=100  # from 128  for V8 suggested CAP to avoid Out Of Memory 

other opportunities will become apparent with data from requested information being available for analysis.

  • @Sascha These three changes to your my.cnf-ini will result in significant improvements for your associates/clients/customers. – Wilson Hauck May 7 '18 at 11:56
  • @sascha Have you been able to apply the 3 suggestions of Apr 10, 2018? If they helped, please upvote and accept. Thanks – Wilson Hauck Jun 11 '18 at 23:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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