首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >为专用mariadb服务器找到正确的配置-用于大型innodb表

为专用mariadb服务器找到正确的配置-用于大型innodb表
EN

Stack Overflow用户
提问于 2018-03-04 19:31:41
回答 5查看 2.7K关注 0票数 1

我一直在一个单独的服务器上工作来配置mariadb,但查询仍然需要很长时间才能返回,而且大部分时间都没有返回。

Mariadb版本: 10.1.31-1服务器,50 as,2200 Ghz * 10核Centos7作为操作

目标数据库当前有非常大的innodb表,并且没有很好地索引。没有其他的表类型,只有innodb。

目前,我正在进行一些嵌套的select查询,并使用insert into或replace into将结果保存到另一个空的克隆数据库中。

举个例子:

代码语言:javascript
复制
REPLACE INTO db1.table1
SELECT * FROM db0.table1 WHERE ART_ID IN (
    SELECT ART_ID FROM db0.table2 WHERE BRA_ID IN (
        SELECT BRA_ID FROM db0.table3 WHERE BRAND IN (
            SELECT BRAND FROM db0.table4 WHERE ID IN (... bunch of comma seperated ids)
        )
    )
);

唯一的连接来自本地和/或只有一个远程连接。服务器的唯一任务是将海量数据过滤到另一个数据库,而不是web服务器等。

我将/etc/my.cnf文件放在下面。我对这个文件做了很多测试,所以你可能会看到一些无用的附加内容。

问题是: cnf文件中是否有任何遗漏的配置;还有其他配置提示等,以便在合理的时间内运行这些查询。

它工作得更好,但在我更改配置或/和更新服务器后,一切都变得慢了很多。(是的,在更新数据库版本后,我也更新了表)

代码语言:javascript
复制
# The MySQL server
[mysqld]
performance_schema=0
bind-address = 0.0.0.0
max_connections = 32
socket      = /var/lib/mysql/mysql.sock
skip-external-locking
max_allowed_packet = 1024M

connect_timeout=300
wait_timeout=1200
interactive_timeout=300

key_buffer_size = 2G
bulk_insert_buffer_size=256M
sort_buffer_size = 32M
#read_buffer_size = 8M
#read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 32M
join_buffer_size=32M
#max_heap_table_size= 8G
tmp_table_size=8G
concurrent_insert=2
#external-locking=FALSE
#open_files_limit=50000

default-storage-engine=innodb
innodb_file_per_table=1

#lower_case_table_names=1

# Try number of CPU's*2 for thread_concurrency
innodb_write_io_threads=10
innodb_read_io_threads=20
#innodb_buffer_pool_instances=8
#thread_stack = 1M
#thread_cache_size = -1

innodb_buffer_pool_size = 16G

#query_cache_limit = 1073741824
#query_cache_size = 1073741824
#query_cache_type = 1

query_cache_size=0
query_cache_type=0

#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size

innodb_fast_shutdown=0

#innodb_log_file_size = 1G
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 0
innodb_lock_wait_timeout = 1073741823
innodb_thread_concurrency=0
innodb_commit_concurrency=0
innodb_flush_method=O_DSYNC
innodb_log_compressed_pages = 0
#innodb_flush_neighbors = 0
#innodb_adaptive_hash_index_partitions = 8


log-bin=mysql-bin
server-id   = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M

[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M

[mysqlhotcopy]
interactive-timeout

我使用了percona向导并合并了建议。当前my.cnf如下所示。

我昨天晚上(2018-03-05 22:41:30 UTC)运行了php应用程序,但mysql仍然无法返回任何结果。

应用程序只添加了限制10000和偏移量(我不想从头开始,我目前正在跟踪最后一个偏移量,并从该点开始)。

目前我正计划删除并重新安装mariadb :(这将是一种情绪反应)

代码语言:javascript
复制
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208

[mysql]

# CLIENT #
port                           = 9999
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
port                           = 9999
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM # - there is no myisam table
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 30
#thread-cache-size              = 16
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 5G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 42G
innodb-buffer-pool-instances   = 42


thread_cache_size=100
innodb_lru_scan_depth=100
innodb_purge_threads=4
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_thread_concurrency=0
max_seeks_for_key=32
max_write_lock_count=16
thread_concurrency=35

innodb_fast_shutdown=0
innodb_file_per_table=1
default-storage-engine=innodb
concurrent_insert=2
join_buffer_size=32M
tmp_table_size=8G
read_buffer_size = 8M

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log
EN

回答 5

Stack Overflow用户

发布于 2018-03-05 21:43:33

问题中的my.cnf数据与显示全局变量;值不匹配。

请使用此链接

https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/

以帮助获取准确的匹配值以获得帮助。

那么,对于您的my.cnf mysqld部分需要考虑的建议包括:

代码语言:javascript
复制
innodb_buffer_pool_size=8G  # from > 40G (95% of RAM will not work well)
innodb_lru_scan_depth=100  # from 1024  see refman every second digging too deep
log_warnings=2  # from 1 to include connection errors more detail
max_connect_errors=10  # from 1,000,000 no need to waste a million cycles
thread_cache_size=100  # from 16  CAP per V8 MySQ to be prepared for volume
have_symlink=NO  # to protect you server, unless you NEED it
innodb_purge_threads=4  # from 1  to accommodate when needed
innodb_read_io_threads=64  # from 4 see dba.stackexhange.com Q 5666 9/12/11 Rolando
innodb_write_io_threads=64  # from 4  per Rolando to use multi-core
innodb_thrad_concurrency=0  # another part of multi-core enabling
max_seeks_for_key=32  # from huge number, not found in 32, will not be found
max_write_lock_count=16  # give RD opportunity after nn locks
thread_concurrency=35  # to limit concurrent processing load

@Erce,请在正确的active my.cnf中应用。我撤回了重做你的mysqld部分的提议。

票数 1
EN

Stack Overflow用户

发布于 2018-03-07 04:09:03

我使用了percona向导并合并了建议。当前my.cnf如下所示。

我昨天晚上(2018-03-05 22:41:30 UTC)运行了php应用程序,但mysql仍然无法返回任何结果。

应用程序只添加了限制10000和偏移量(我不想从头开始,我目前正在跟踪最后一个偏移量,并从该点开始)。

代码语言:javascript
复制
[mysql]

# CLIENT #
port                           = 9999
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
port                           = 9999
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM # - there is no myisam table
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 30
#thread-cache-size              = 16
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 5G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 42G
innodb-buffer-pool-instances   = 42


thread_cache_size=100
innodb_lru_scan_depth=100
innodb_purge_threads=4
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_thread_concurrency=0
max_seeks_for_key=32
max_write_lock_count=16
thread_concurrency=35

innodb_fast_shutdown=0
innodb_file_per_table=1
default-storage-engine=innodb
concurrent_insert=2
join_buffer_size=32M
tmp_table_size=8G
read_buffer_size = 8M

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log
票数 1
EN

Stack Overflow用户

发布于 2018-03-07 06:15:44

@erce

这三行需要删除(就在记录区之前)

代码语言:javascript
复制
join_buffer_size=32M
tmp_table_size=8G
read_buffer_size = 8M

日志记录

早些时候,max_heap_table_size与tmp_table_size进行了精确匹配。这条线稍后会让你失去平衡,这是很糟糕的。

join_buffer_size和read_buffer_size正在扼杀你的内存空间。请使用mysqlcalculator.com了解原因。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/49094911

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档