用于许多更新的MariaDB配置，写

Question

基于我展示的几个教程，我为我的服务器创建了以下MariaDB配置。服务器有32 GB内存，SSD，8个内核，并专门用于运行MySQL。最大的表大约有150万行，我们每秒钟都在运行更新。所以我试图为写作创建一个最优的配置。一个简单的更新记录有时需要2-3秒.我应该改变一些东西来提高更新查询的速度吗？

[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

查询日志示例

# 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;

Answer 1

如果服务器完全由MariaDB使用，那么您可以将16-24GB内存专用于DB服务(现在它仅限于12 GB)。这样就可以产生更大的InnoDB池。如果您的表/索引适合RAM，那么您的服务就不需要昂贵的额外磁盘I/O。

您可以将max_connections变量减少到合理的值，但这需要客户端在使用后立即释放连接。这可以减少缓冲区消耗和总的峰值RAM需求。

此外，还必须将join_buffer_size、sort_buffer_size、read_buffer_size和read_rnd_buffer_size变量设置为适当的值，以确保查询不对中间数据集使用磁盘临时表。但是这些值不应该太大，因为它们是为每个连接定义的，累积内存消耗可以达到(max_connections * all_buffers) + innodb_buffer_pool_size甚至更多。

好的起点是

SET @@global.slow_launch_time = 0.5, @@global.slow_query_log = 1;此命令将开始记录运行超过0.5秒的查询，以进行进一步分析。如果全局变量log_queries_not_using_indexes设置为1，那么没有索引的查询也会被记录在那里。就您的DB不太大而言，更有可能的是，与糟糕的服务器配置相比，不太好的索引会导致经济增长放缓。