MySQL Forums :: Optimizer & Parser :: Optimal Mysql Configuration (my.cnf)


Advanced Search

Optimal Mysql Configuration (my.cnf)
Posted by: Luiz Cláudio Pinheiro ()
Date: July 04, 2011 12:53PM

Well, I have a system running many queries in a table which is constantly modified. This table has an average of 400 records and these records are always deleted and new ones added, all the time.

Make some changes in my configuration file, but I do not think I got the best result. The CPU usage of my mysql is between 300 ~ 700% range every time.

Here you'll be displaying my config file so you can tell me what should be changed.

Remember that sometimes, this time not so often, mysql locks up, causing my entire server to fall completely. It is also worth remarking that it is possible to reduce the number of queries. All optimizations have been made in consultations, leaving me just changing the configuration of mysql.

Below is the configuration:
------------------------------------------------------------------------------
[mysqld]
#bind-address=127.0.0.1
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

set-variable = key_buffer=312M

set-variable = max_allowed_packet=1M

set-variable = table_cache=15000
set-variable = open_files_limit=30000
set-variable = table_definition_cache=1024
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=128M
set-variable = thread_cache=32
set-variable = thread_concurrency=16

set-variable = max_connections=5000

set-variable = net_write_timeout=30
set-variable = connect_timeout=2
set-variable = wait_timeout=30

query_cache_limit = 1M
query_cache_size = 16M
thread_stack = 128k
read_buffer_size = 128k


# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

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

[isamchk]
set-variable = key_buffer=64M
------------------------------------------------------------------------------

Att,
Luiz Cláudio Pinheiro

Options: ReplyQuote


Subject Views Written By Posted
Optimal Mysql Configuration (my.cnf) 11466 Luiz Cláudio Pinheiro 07/04/2011 12:53PM
Re: Optimal Mysql Configuration (my.cnf) 2033 Rick James 07/07/2011 10:06PM
Re: Optimal Mysql Configuration (my.cnf) 2650 Luiz Cláudio Pinheiro 07/08/2011 10:28AM
Re: Optimal Mysql Configuration (my.cnf) 2404 Rick James 07/08/2011 08:33PM


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.