MySQL Forums
Forum List  »  Quality Assurance

Memory Usage
Posted by: Zoltan Baranyai
Date: September 28, 2009 01:59PM

I have a 13 GB mysql database and I would like to optimize my.cnf.
The db has 150 tables, 140 MyISAM tables and 10 InnoDB. The server now has 8GB RAM but I plan to increase it to 16 GB.
Mytop says that the key efficiency is 100%:

MySQL on localhost (5.0.22) up 0+04:28:26 [19:47:35]
Queries: 5.4M qps: 351 Slow: 0.0 Se/In/Up/De(%): 38/00/00/00
qps now: 382 Slow qps: 0.0 Threads: 23 ( 2/ 2) 45/00/00/00
Key Efficiency: 100.0% Bps in/out: 0.2/ 58.4 Now in/out: 8.4/ 1.5k

phpMyAdmin shows that I have 56 million rows on that table.

My current my.cnf is this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld]
safe-show-database
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 4608M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 1024M
thread_cache_size = 8
query_cache_size = 512M
ft_min_word_len = 2
tmp_table_size = 512M
thread_concurrency = 16
max_connections = 1000
max_connect_errors = 10000
ssl-ca=/etc/my-ca-cert.pem
ssl-cert=/etc/my-server-cert.pem
ssl-key=/etc/my-server-key.pem

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

Most of the times the Mysql is very fast, but some select queries are slow and slowing down other selects from the same table.

My question is: what should be my optimal my.cnf if I keep the existing 8GB RAM? What if I raise the RAM to 16 GB? Would it be OK to increase memory to 16 GB, this way, I could use the entire database from memory? Am I wrong? The server is only a MySQL server.

Here is my existing processor info:

Motherboard SuperMicro X7DBR-E Intel Xeon QuadCore DualProc SATA [2Proc]
Processor Intel Xeon-Harpertown 5430-Quadcore [2.66GHz] Hardware upgrade
Processor Intel Xeon-Harpertown 5430-Quadcore [2.66GHz] Hardware upgrade

Please advise what should I do to optimize the server. Thank you in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
Memory Usage
3674
September 28, 2009 01:59PM


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.