Allocating more memory to MySQL
Posted by: James Marcus
Date: July 18, 2012 08:43AM

Hi,
I haven't really done much MySQL beyond creating users and making some changes in my.cnf as a system administrator. That being said, we have a 100GB MySQL server and we are having trouble doing INSERTS and UPDATES, they are taking up to 90 seconds when looking at show processlist.

This machine has a 192GB of Memory, MySQL has 80G of Virtual Memory and 79GB of Residential Memory, according to top.

mysqld Ver 5.1.52-log for unknown-linux-gnu on x86_64 (MySQL Community Server (GPL))

Are there memory allocation that I can make to improve performance?



Here is my.cnf

[client]
port = 3306
socket = /mount/data/mysql/mysql.sock

[mysqld]
max_connections = 300
port = 3306
socket = /mount/data/mysql/mysql.sock
skip-locking
key_buffer = 512M
max_allowed_packet = 1G
table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size = 64M
query_cache_min_res_unit = 8
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 10
default-storage-engine=InnoDB
tmp_table_size= 4095M
max_heap_table_size = 20G
max_tmp_tables= 256
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 70G
innodb_flush_method=O_DIRECT
innodb_log_file_size = 1800M
innodb_log_buffer_size = 20M
innodb_data_home_dir =
innodb_data_file_path = /mount/data/mysql/ibdata1:10M:autoextend
innodb_thread_concurrency = 0
datadir=/mount/data/mysql/
tmpdir=/mount/mysql_tmpdir
innodb_file_per_table

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout

Thanks,
James

Options: ReplyQuote


Subject
Written By
Posted
Allocating more memory to MySQL
July 18, 2012 08:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.