0

Recently I took all MySQL servers that we had over a few servers and put in a single VPS dedicated to MySQL. As a source of information, I used Aurimas Mikalauskas ebook "Speedemy - MySQL Configuration Tuning Handbook" to configure the server. It all looked great until I realize that it was eating away the swap (and probably going slow). Then I went to the config file and reduced the suggested memory to store innodb_buffer_pool_size to value even lower than the suggested 80%.

Today I went there to check how the server was doing, and notice it had eaten a bunch of my swap again, and since I don't trust myself, I decided to ask here what I may be doing wrong.

The server is a VPS with 16GB of RAM and 6 cores.

This is my free output:

              total        used        free      shared  buff/cache   available
Mem:       16393988    13286624     2009964         876     1097400     2798976
Swap:        975868      610360      365508

This is my configuration file (ignore the Portuguese comments):

#   Arquivo de configuração do mysql
#   Criado por Guilherme Richter, 19/05/2020

[client]
#   Usado apenas para casos especificos
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysql]
#   Configurações do cliente
auto-rehash                     # Auto completar        (DEFAULT: FALSE)

[mysqld]
#   Configuração do servidor
pid_file            = /var/run/mysqld/mysqld.pid
socket              = /var/run/mysqld/mysqld.sock
datadir             = /var/lib/mysql
log_error           = /var/log/mysql/error.log
user                = mysql
bind_address        = 0.0.0.0       # Ouve todos os endereços

#   Configurações genericas do servidor
max_allowed_packet          = 32M       # Tamanho maximo do pacote. (DEFAULT: 64M)
max_connections             = 2000      # Maximo de coneções        (DEFAULT: 151)
open_files_limit            = 10000     # Maximo de arquivos abertos    (DEFAULT: 10000)
tmp_table_size              = 64M       # Limite tamanho tabela em mem  (DEFAULT: 16M)
max_heap_table_size         = 64M       # Limite tamanho tabela em mem  (DEFAULT: 16M)
tmpdir                      = /tmp      # Diretorio /tmp/       (DEFAULT: /tmp/)
default_storage_engine      = InnoDB    # Engine default        (DEFAULT: InnoDB)
skip_name_resolve                       # Desabilita resolução DNS  (DEFAULT: FALSE)

#   Configurações de log binario
log_bin                         # Log binario ativo     (DEFAULT: TRUE)
server_id                   = 1     # Identifica servidor para log  (DEFAULT: 1)
max_binlog_size             = 256M      # Tamanho maximo log binario    (DEFAULT: 1G)
binlog_expire_logs_seconds  = 604800    # Max tempo log binario     (DEFAULT: 2592000)
sync_binlog                 = 0     # Somente pra replicação    (DEFAULT: TRUE)

#   Configurações especificas do InnoDB
innodb_buffer_pool_size         = 10000M    # Tamanho do buffer (<80% mem)  (DEFAULT: 128M)
#http://www.speedemy.com/mysql/17-key-mysql-config-file-settings/innodb_log_file_size/ (Verificar a melhor configuração quando o servidor estiver no ar.
innodb_log_file_size                = 256M      # Tamanho do log de recovery    (DEFAULT: 48M)
innodb_log_buffer_size              = 16M       # Tamanho do buffer     (DEFAULT: 16M)
innodb_flush_log_at_trx_commit      = 0     # Commita o banco a cada evento (DEFAULT: 1)
innodb_flush_method                 = O_DIRECT  # Evita double buffer       (DEFAULT: fsync)
innodb_buffer_pool_instances        = 8     # Regioes que dividem o buffer  (DEFAULT: 8)
innodb_thread_concurrency           = 12        # Limita o numero de threads    (DEFAULT: 0)
innodb_io_capacity                  = 800       # Quantas escritas por segundo  (DEFAULT: 200)
innodb_io_capacity_max              = 3000      # Quantas escritas/s stress (DEFAULT: 2000)
innodb_stats_on_metadata            = 0     # Estatisticas redundantes  (DEFAULT: 0)
innodb_buffer_pool_dump_at_shutdown = 1     # Restart mais rapido       (DEFAULT: 1)
innodb_buffer_pool_load_at_startup  = 1     # Restart mais rápdio       (DEFAULT: 1)
innodb_buffer_pool_dump_pct         = 75        # Restart mais rápido       (DEFAULT: 25)
innodb_adaptive_hash_index_parts    = 16        # Partições de Index        (DEFAULT: 8)
innodb_checksum_algorithm           = crc32         # Aceleração de hardware    (DEFAULT: crc32)
innodb_read_io_threads              = 16        # Threads de leitura        (DEFAULT: 4)
innodb_write_io_threads             = 8     # Threads de escrita        (DEFAULT: 4)
innodb_file_per_table                       # Arquivo por tabelas       (DEFAULT: True)

#   Slow query log 
slow_query_log              = 1     # Guarda queries lentas     (DEFAULT: False)
long_query_time             = 1.0       # Tempo lite da query       (DEFAULT: 10s)

#   Função
log_bin_trust_function_creators     = 1;

I have a bunch of metrics from zabbix too, but I don't understand how to interpreat it.

Any ideas of what I am doing wrong, and how I could fix it?

  • "it was eating away the swap" -- does it actually affect performance, or are you generally concerned about your swap partition being used for what it's supposed to be used? – mustaccio Jun 25 at 0:55
  • @mustaccio I am more concerned about my lack of knowledge on database configuration. It's very difficult for us to know if our systems are running smoothly because we pretty much don't have user feedback. And at the moment I can't afford to stop my other projects and focus on the fine art of database administration. – IamRichter Jun 25 at 13:35
0

The default for swappiness is 60; change it to 1: http://mysql.rjweb.org/doc.php/memory#swappiness

| improve this answer | |
0

Swapping is normal, it doesn't necessarily indicate a problem.

You can, however, make sure the buffer pool never gets swapped out. Allocate 10GB of huge pages at boot time, and instruct MySQL to allocate it's innodb_buffer_pool from there (large-pages=1 in my.cnf). Huge pages are unswappable, so this will ensure your buffer pool never gets swapped out.

| improve this answer | |
  • is there a performance issue in use the entire buffer pool in a single file? – IamRichter Jun 24 at 18:25
  • What do you mean by "single file"? Buffer pool is in RAM. – Gordan Bobic Jun 24 at 19:52
  • Ohh yeah, right. Sorry, my mistake. I will try it. – IamRichter Jun 25 at 12:00

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.