Hi, I have an around 25,000/500 concurrent users moodle install running on two identical Intel(R) Xeon(R) CPU E5-2609 0 @ 2.40GHz, 8 cores, 48gb ram servers.
Several times thorough the day MySQL consumes the 8 cpus resources and stays stuck at 100% and the website slows to a crawl until mysql is restarted. Sometimes this coincides with peak times (with around 500 concurrent users), sometimes it doesn't.
The ram usage doesn't go above 24-30gb even in peak times, only the cpu.
Mysqltuner results show a large amount of non-indexed joins and fragmented tables. I ran optimize/repair on all tables, however don't now how to fix non-indexed joins.
I checked the DB and saw that the questions table is around 11GB and 1,100,000 records, taking roughly half the space of the database. Is this normal? Could it be the culprit?
Below is my my.cnf
Any help would be great
[mysqld] datadir = /home/mysql socket = /home/mysql/mysql.sock user=mysql skip-external-locking skip-name-resolve symbolic-links=0 max_allowed_packet = 512M max_connections = 250 table_cache = 4000 key_buffer_size=4096M sort_buffer_size=512K read_buffer_size=128K read_rnd_buffer_size=512K thread_concurrency=8 # GENERAL # user = mysql default-storage-engine = InnoDB socket = /home/mysql/mysql.sock pid-file = /home/mysql/mysql.pid # MyISAM # myisam-recover = FORCE,BACKUP # SAFETY # max-allowed-packet = 8M max-connect-errors = 1000000 # DATA STORAGE # datadir = /home/mysql/ # BINARY LOGGING # #log-bin = /home/mysql/mysql-bin expire-logs-days = 1 sync-binlog = 0 # binary logging format - mixed recommended binlog_format=mixed # CACHES AND LIMITS # tmp-table-size = 128M max-heap-table-size = 128M query-cache-type = 1 query-cache-limit = 16M query-cache-size = 512M max-connections = 250 thread-cache-size = 35 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 4096 join_buffer_size = 256K query_cache_min_res_unit = 2k # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-buffer-size = 48M innodb-log-file-size = 1024M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 4G innodb_buffer_pool_instances = 5 innodb_additional_mem_pool_size = 20M innodb_log_files_in_group = 2 innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 # LOGGING # log-error = /home/mysql/mysql-error.log log-queries-not-using-indexes = 1 slow-query-log = 0 slow-query-log-file = /home/mysql/mysql-slow.log [mysqld_safe] log-error=/home/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [client] socket = /home/mysql/mysql.sock