MySQL Forums
Forum List  »  Optimizer & Parser

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:

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).

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


set-variable = key_buffer=64M

Luiz Cláudio Pinheiro

Options: ReplyQuote

Written By
Optimal Mysql Configuration (my.cnf)
July 04, 2011 12:53PM

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.