LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
Search this Thread
Old 09-30-2007, 02:29 PM   #1
sorigen
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.

Config:
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
http://www.uploadgeek.com/uploads456/1/top.gif

Server traffic
http://www.uploadgeek.com/uploads456...er_traffic.gif

Query Statistics
http://www.uploadgeek.com/uploads456...statistics.gif

More status variables
http://www.uploadgeek.com/uploads456..._variables.gif

Here is my my.cnf

Code:
# 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
[client]
#password	= your_password
port		= 3306
socket		= /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
log-slow-queries=/var/log/mysql-slow.log

port		= 3306
socket		= /var/lib/mysql/mysql.sock
skip-locking
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
#new
table_cache = 1024
long_query_time=5
connect_timeout=15


# 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!
# 
skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# 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:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    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:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    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 master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info 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
#log-bin=mysql-bin

# 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

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 512M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

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

[mysqlhotcopy]
interactive-timeout

Last edited by sorigen; 09-30-2007 at 02:36 PM.
 
Old 09-30-2007, 08:49 PM   #2
paulsm4
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:

http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
<= 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

http://www.mysqlperformanceblog.com/category/innodb/
<= Search for "How does Buffer Pool size affects Innodb Performance?"
 
Old 10-01-2007, 12:58 AM   #3
tanveer
Member
 
Registered: Feb 2004
Location: e@rth
Distribution: RHEL-3/4/5,Gloria,opensolaris
Posts: 489

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.
 
  


Reply


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 04:30 AM.

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