Based on few tutorials that I show I created the following MariaDB configuration for my server. Server has 32 GB ram, SSD, 8 cores and is dedicated to run MySQL. The biggest table has about 1,5 million rows, and we are running updates every second. So I am trying to create an optimal configuration for writing. A simple update record sometimes need 2-3 seconds. Should I change something to increase update queries speed?
[mysqld] # MyISAM key_buffer_size = 32M myisam_recover = FORCE,BACKUP # SAFETY innodb = FORCE innodb_strict_mode = 1 max_allowed_packet = 16M max_connect_errors = 1000000 skip_name_resolve # BINARY LOGGING expire_logs_days = 14 sync_binlog = 1 # CACHES AND LIMITS max_connections = 500 max_heap_table_size = 32M open_files_limit = 65535 query_cache_type = 0 query_cache_size = 0 table_definition_cache = 4096 table_open_cache = 10240 thread_cache_size = 50 tmp_table_size = 32M # INNODB innodb_buffer_pool_size = 12G innodb_buffer_pool_instances = 12 innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 innodb_log_files_in_group = 2 innodb_log_file_size = 1G # LOGGING log_error = /var/lib/mysql/srv.defrop.com.err slow_query_log = 1 slow_query_log_file = /var/lib/mysql/mysql-slow.log long_query_time = 2 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links = 0 #custom performance_schema = on
Example log of query
# Time: 190415 10:36:59 # User@Host: defrop_defdb[defrop_defdb] @ localhost  # Thread_id: 2420 Schema: defrop_defrop QC_hit: No # Query_time: 2.404021 Lock_time: 0.000053 Rows_sent: 0 Rows_examined: 2 # Rows_affected: 1 SET timestamp=1555317419; UPDATE `backlinks` SET `backlinks`.`crawler_id` = '10.0.0.28', `backlinks`.`used_time`=NOW() WHERE `backlinks`.`campaign_id`=710 AND `backlinks`.`googlebot_id` IS NULL AND `backlinks`.`used_time` IS NULL LIMIT 1;