mysql 5.6 64bit版本的性能调优 [英] Performance tuneing of mysql 5.6 64bit version
问题描述
当前，我正在使用具有8GB RAM的mySQL 5.6 64位版本，当前数据库大小为appx 4GB.我正在为mySQL使用以下参数，并使用INNODB存储引擎.目前工作正常.有没有提高数据库性能的选项.
[客户]
端口= 3307
[mysql]
default-character-set = utf8
[mysqld]
端口= 3307
datadir ="D:/mysql-5.6.36-winx64/Data"
字符集服务器= utf8
default-storage-engine = INNODB
sql-mode ="STRICT_TRANS_TABLES，NO_AUTO_CREATE_USER，NO_ENGINE_SUBSTITUTION"
log-output = FILE
general-log = 0
general_log_file ="XYZ-INHOUSE.log"
slow-query-log = 1
slow_query_log_file ="XYZ-INHOUSE-slow.log"
long_query_time = 10
innodb_table_locks = 0
innodb_autoinc_lock_mode = 2
transaction-isolation =已读
log-error ="XYZ-INHOUSE.err"
server-id = 2
max_connections = 999
query_cache_size = 512M
table_open_cache = 2000
tmp_table_size = 137M
thread_cache_size = 10
myisam_max_sort_file_size = 100G
myisam_sort_buffer_size = 264M
key_buffer_size = 256M
thread_stack = 256K
read_buffer_size = 64K
read_rnd_buffer_size = 256K
sort_buffer_size = 256K
expire_logs_days = 4
innodb_additional_mem_pool_size = 19M
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 256M
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1280M
innodb_thread_concurrency = 8
innodb_autoextend_increment = 64
innodb_buffer_pool_instances = 8
innodb_concurrency_tickets = 5000
innodb_old_blocks_time = 1000
innodb_open_files = 300
innodb_stats_on_metadata = 0
innodb_file_per_table = 1
innodb_checksum_algorithm = 0
back_log = 80
flush_time = 0
join_buffer_size = 256K
max_allowed_packet = 64M
max_connect_errors = 100
open_files_limit = 65535
query_cache_type = 1
sort_buffer_size = 256M
table_definition_cache = 4096
binlog_row_event_max_size = 8K
sync_master_info = 10000
sync_relay_log = 10000
sync_relay_log_info = 10000
group_concat_max_len = 1000000
table_open_cache = 8192
max_heap_table_size = 256M
query_cache_limit = 4M
我尝试过的事情:
有什么选择可以提高数据库的性能.
Currently I am using mySQL 5.6 64bit version with 8GB RAM and current database size is appx 4GB. I am using following parameter for mySQL and using INNODB storage engine. Currently it is working fine. Is there any option to increase the performance of database.
[client]
port=3307
[mysql]
default-character-set=utf8
[mysqld]
port=3307
datadir="D:/mysql-5.6.36-winx64/Data"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="XYZ-INHOUSE.log"
slow-query-log=1
slow_query_log_file="XYZ-INHOUSE-slow.log"
long_query_time=10
innodb_table_locks=0
innodb_autoinc_lock_mode=2
transaction-isolation=READ-COMMITTED
log-error="XYZ-INHOUSE.err"
server-id=2
max_connections=999
query_cache_size=512M
table_open_cache=2000
tmp_table_size=137M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=264M
key_buffer_size=256M
thread_stack = 256K
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
expire_logs_days= 4
innodb_additional_mem_pool_size=19M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=256M
innodb_buffer_pool_size=4G
innodb_log_file_size=1280M
innodb_thread_concurrency=8
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=64M
max_connect_errors=100
open_files_limit=65535
query_cache_type=1
sort_buffer_size=256M
table_definition_cache=4096
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
group_concat_max_len = 1000000
table_open_cache = 8192
max_heap_table_size = 256M
query_cache_limit = 4M
What I have tried:
Is there any option to increase the performance of database.
推荐答案
性能调优取决于环境和工作量，没有人会在不了解更多信息的情况下告诉您配置您的系统并收集指标.
Performance tuning is subject to the environment and work load, no one can tell you a configuration without knowing more about your system and gathering metrics.
这篇关于mysql 5.6 64bit版本的性能调优的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
