mysql 优化 服务器 12核 32g 这样配置还有什么优化的吗
0 / 15 / 创建于 2年前
lijizheng 的个人博客
[client]
#password = your_password
default-storage-engine=INNODB
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 100G
table_open_cache = 1024
sort_buffer_size = 4M
net_buffer_length = 4K
read_buffer_size = 4M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 64M
thread_cache_size = 128
query_cache_size = 128M
tmp_table_size = 128M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1
autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 8192M
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 8
innodb_write_io_threads = 8
[mysqldump]
quick
max_allowed_packet = 500M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
本作品采用《CC 协议》，转载必须注明作者和本文链接
关于 LearnKu
粤公网安备 44030502004330号
[client]
#password = your_password default-storage-engine=INNODB port = 3306 socket = /tmp/mysql.sock
[mysqld] port = 3306 socket = /tmp/mysql.sock datadir = /www/server/data default_storage_engine = InnoDB performance_schema_max_table_instances = 400 table_definition_cache = 500000 skip-external-locking key_buffer_size = 256M max_allowed_packet = 100G table_open_cache = 500000 sort_buffer_size = 4M net_buffer_length = 4K read_buffer_size = 4M read_rnd_buffer_size = 256K myisam_sort_buffer_size = 64M thread_cache_size = 128 query_cache_size = 128M tmp_table_size = 128M max_heap_table_size=128M sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp = true
#skip-name-resolve max_connections = 50000 max_connect_errors = 50000 open_files_limit = 500000
log-bin=mysql-bin binlog_format=mixed server-id = 1 expire_logs_days = 10 slow_query_log=1 slow-query-log-file=/www/server/data/mysql-slow.log long_query_time=1
#log_queries_not_using_indexes=on
innodb_data_home_dir = /www/server/data innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /www/server/data innodb_buffer_pool_size = 8192M innodb_file_per_table=1 innodb_log_file_size = 512M innodb_log_files_in_group=2 innodb_log_buffer_size = 128M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 innodb_max_dirty_pages_pct = 90 innodb_read_io_threads = 8 innodb_write_io_threads = 8
[mysqldump] quick max_allowed_packet = 500M
[mysql] no-auto-rehash
[myisamchk] key_buffer_size = 256M sort_buffer_size = 4M read_buffer = 2M write_buffer = 2M
[mysqlhotcopy] interactive-timeout
没法说具体业务/问题具体分析
100多人用的管理系统，这也太凡尔赛了吧。这么好的配置。
首先不能进行盲目的、臆想的去做一些所谓的优化配置。你的CPU使用过高，应该去分析为什么过高？
通过你的描述，100多人的系统这么高的配置。不会出现CPU使用过高的情况。可能存在索引失效、锁相关的问题。
暴殄天物
2核8G，60几块的路过。
优化参数不如让他们改代码质量 比如select(*) 然后大数组 死循环 N表join
有没有可能是临时表爆掉了？
…要不就优化直接禁用join 一条条插数据然后把数据扔到php里面处理，要不就引es 全扔一个宽表里面 随他们怎么造