Download your favorite Linux distribution at LQ ISO.
Go Back > Forums > Linux Forums > Linux - Server
User Name
Linux - Server This forum is for the discussion of Linux Software used in a server related context.


  Search this Thread
Old 09-30-2007, 02:29 PM   #1
LQ Newbie
Registered: Sep 2007
Posts: 4

Rep: Reputation: 0
Question mySQL 100% CPU, how to config my.cnf for lots of querys

Hello, this is my first post here, hope everything is ok.

I have a server which I use for a torrent tracker, and mySQL is constant at about 80-90% and the server doesn't always respond.. I spent last couple of days searching different configs for my.cnf, but didn't find the right combination because I think the server can handle the load with the right settings.. maybe someone can help me. Thank you.

CPU : Opteron 155
RAM : 1,5 GB
HDD : Raptor 36 GB 10.000 rpm
OS : FedoraCore 7, php 5.2.2, mySQL 5.0.45

Top in putty image

Server traffic

Query Statistics

More status variables

Here is my my.cnf

# Example MySQL config file for very large systems.
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
#password	= your_password
port		= 3306
socket		= /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server

port		= 3306
socket		= /var/lib/mysql/mysql.sock
key_buffer = 256M
key_buffer_size = 30M
join_buffer_size = 2M
max_allowed_packet = 1M
#table_cache = 512
sort_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 256M
thread_cache_size = 32
query-cache-type = 1
query-cache-size = 12M
query_cache_limit = 8M
query_prealloc_size = 512K
query_alloc_block_size = 128K
wait_timeout = 15
tmp_table_size = 64MB
max_connections = 1500
table_cache = 1024

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!

# Replication Master Server (default)
# binary logging is required for replication

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id	= 1

# Replication Slave (comment out master section to use this)
# To configure this host as a replication slave, you can choose between
# two methods :
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#    Example:
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
# OR
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the file, unless you shutdown
#    the slave server, delete and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
# The replication master for this slave - required
#master-host     =   <hostname>
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
# binary logging - not required for slaves, but recommended

# Point the following paths to different dedicated disks
#tmpdir		= /tmp/		
#log-update 	= /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

max_allowed_packet = 16M

# Remove the next comment character if you are not familiar with SQL

key_buffer = 512M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M


Last edited by sorigen; 09-30-2007 at 02:36 PM.
Old 09-30-2007, 08:49 PM   #2
LQ Guru
Registered: Mar 2004
Distribution: SusE 8.2
Posts: 5,863
Blog Entries: 1

Rep: Reputation: Disabled
Hi -

You have gathered some excellent statistics (kudos!). I don't see anything that would account for the problem, but here are two links that might help:
<= Search for the section "Solving CPU Bottlenecks"
You've already covered much of this ground...
... but the article has some additional suggestions you might find useful
<= Search for "How does Buffer Pool size affects Innodb Performance?"
Old 10-01-2007, 12:58 AM   #3
Registered: Feb 2004
Location: e@rth
Distribution: RHEL-3/4/5,Gloria,opensolaris
Posts: 525

Rep: Reputation: 37
Hi there,

I recently faced with this problem where my mysql service was taking 99.9% cpu for even a single mysql query. I then tried many different my.cnf but lastly the solution I find was due to problem in coding of web application i.e. in Php file. Also, did some indexing too.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL startup process and my.cnf lord-fu *BSD 4 07-11-2007 07:44 AM
mySQL my.cnf (moving default datadir) not working! madfitz Linux - Newbie 4 02-09-2006 07:15 PM
Setting up the my.cnf for MySQL Server wbuik Linux - Software 1 09-24-2005 04:33 PM
how to config the mysql my.cnf? treotan Linux - General 1 09-14-2005 11:05 AM
MySQL server & my.cnf hct224 Linux - Newbie 0 04-02-2004 01:03 PM

All times are GMT -5. The time now is 06:04 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration