my.cnf Configuração do MariaDB - atingindo o teto da CPU mesmo com as otimizações em vigor

Question

Estou tentando resolver essa questão do mySQL atingindo o teto em termos de carga de CPU / Servidor, já que as especificações do servidor são as seguintes:

• Item da lista

• 2 x 2,4 GHz Octa-Core E5-2630 v3 Haswell Xeon

• 128 GB de RAM
• Unidade principal SSD
• CloudLinux 7.5
• MariaDB 10.2.16
• LiteSpeed 5.2.8

E com a configuração abaixo mySQL / MariaDB constantemente atinge entre 80-150% da CPU e está colocando bastante pressão sobre o servidor.

Sugestões sobre o que eu poderia ajustar / ativar / desativar / adicionar / remover para tornar o mySQL menos sobrecarregado com CPU, já que o uso de RAM é inferior a 25%, mas a CPU passa muito pelo telhado.

[mysqld]
skip_name_resolve               = 1
default_storage_engine          = InnoDB
log_error                       = /var/lib/mysql/mysql_log.err
performance_schema              = 0
sql_mode                        = "NO_ENGINE_SUBSTITUTION"

max_allowed_packet              = 256M
max_connections                 = 400
open_files_limit                = 10000
wait_timeout                    = 120
connect_timeout                 = 120
interactive_timeout             = 180
tmpdir                          = /var/lib/mysql/tmp
tmp_table_size                  = 256M
max_heap_table_size             = 256M
max_tmp_tables                  = 300
max_statement_time              = 60
innodb_strict_mode              = OFF
#innodb_file_per_table          = ON

slow_query_log                  = ON
slow_query_log_file             = /var/lib/mysql/mysql_slow_queries.log
long_query_time                 = 10

table_open_cache                = 128
table_open_cache_instances      = 16
query_cache_type                = ON
query_cache_size                = 256M
thread_cache_size               = 40
query_cache_limit               = 300M 
key_buffer_size                 = 3G
join_buffer_size                = 512M
max_heap_table_size             = 16M

innodb_buffer_pool_size         = 64G  #80% of system memory?
innodb_log_file_size            = 2G
#innodb_log_buffer_size         = 1G
innodb_buffer_pool_instances    = 12
innodb_file_format              = Barracuda
#innodb_max_dirty_pages_pct     = 0
#innodb_io_capacity             = 400
#innodb_io_capacity_max         = 600
#innodb_flush_sync              = OFF
innodb_buffer_pool_dump_pct     = 80
innodb_flush_log_at_trx_commit  = 0 #or 2
sync_binlog                     = 0
innodb_flush_method             = O_DIRECT
innodb_thread_concurrency       = 8
innodb_read_ahead_threshold     = 8

concurrent_insert               = 1
local_infile                    = 0
group_concat_max_len            = 102400

Aqui estão todas as leituras relevantes que podem ajudar a entender melhor o ambiente:

• MOSTRAR VARIÁVEIS GLOBAIS: link
• MOSTRE ESTADO GLOBAL: link
• Relatório do sintonizador do MySQL: link
• top -c: link
• htop: link
• ulimit, df -h, iostat -x: link

Answer 1

Sugestões a considerar para sua seção my.cnf [mysqld] Taxa por segundo = RPS

thread_handling=pool-of-threads  # from one-thread-per-connection for scalability
max_heap_table_size=512M  # from 16M to increase RAM capacity
tmp_table_size=512M # from 256K 2 be = max_heap_table_size and reduce created_tmp_disk_tables
innodb_io_capacity=10000  # from 200 limit for SSD possible RPS
read_buffer_size=256K  # from 128K to reduce handler_read_next RPS
read_rnd_buffer_size=192K  # from 256K to reduce handler_read_rnd_nxt RPS
aria_pagecache_division_limit=50  # from 100 for WARM cache
key_cache_division_limit=50  # from 100 for WARM cache
innodb_buffer_pool_instances=8  # from 12 for your current data volume
innodb_buffer_pool_size=24G  # from 64G to support 11G of data with room to grow
innodb_lru_scan_depth=100  # from 1024 to reduce CPU every SECOND see refman
innodb_thread_concurrency=0  # from 8 see dba.stackexchange Question 5666

para sugestões adicionais, visualizar perfil, perfil de rede para informações de contato, incluindo o Skype ID.

Há muito mais oportunidades para melhorar o desempenho do seu sistema.

ulimit -n 48000 seria muito útil. Após o teste, torne-o persistente.

Lembre-se de apenas uma alteração por dia, monitor, próxima alteração em 24 horas ou mais. Se alguma alteração for prejudicial, por favor, deixe-me saber @wilsonhauck depois de removê-lo do seu my.cnf.

Informe-nos sobre seu progresso em alguns dias.