开发者

InnoDB Optimization Tips Needed - mysql

I recently got a new dedicated MySQL machine. Now it's running fine, but sometimes it gets slowed down a lot by queries that state: Copying to tmp table. It seems to happen randomly.

The machi开发者_如何学Cne has 12GB of DDR3 ram, and runs in a RAID10 setup with (4x 15k RPM SAS drives).

This machine hosts 5 databases, all between 1 and 8gb in size each. Reads / Writes: 66% / 34%

Below is my my.cnf file. If anyone has performance optimization tips, I would love to hear them.

[mysqld]
skip-name-resolve
datadir=/var/lib/mysql
#socket=/tmp/mysql.sock
log-error=/var/log/mysqld.log
old_passwords=0
max_connections = 1500
table_cache = 1024
max_allowed_packet = 16M
sort_buffer_size = 2M
thread_cache = 8
thread_concurrency = 32
query_cache_size = 0M
query_cache_type = 0
default-storage-engine = innodb
transaction_isolation = REPEATABLE-READ
tmp_table_size = 256M
long_query_time = 3
log_slow_queries = 1
innodb_additional_mem_pool_size=48M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=32M
innodb_buffer_pool_size=6G
innodb_autoinc_lock_mode=2
innodb_io_capacity=500
innodb_read_io_threads=16
innodb_write_io_threads=8
innodb_buffer_pool_size = 5000M
innodb_lock_wait_timeout = 300
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency =32


It seems I have found the solution myself. max_heap_table_size wasnt set. this limited tmp_table_size. i have now set both values on 512m.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜