MySQLd CPU Load

[Yabadoo]

Every 20th minute i'am running a script to update a mysql database.
When i run the program top i noticed that mysqld sometimes uses 98% cpu,
is there a way to limit this value, or what can you do to lower this.

[falko]

What kind of script do you run? What does it do?
Anything in the logs when the load goes up?

[22hosting]

Possibly your MySQL needs good optimisation (check out my.cnf). Also what is your SQL Query? Written badly this will slow down query times exponentially.
________
Ivonna live (http://camslivesexy.com/cam/Ivonna)

[Yabadoo]

The server i'am running is a usenetindexer. The database is 14GB big after running for 23 days now. I mentioned that i'am running a script but actually it's a programm written in c++ that you compile on any operating system.
(weblink = http://www.nntpgrab.nl).

In which log file can i take a look to see the errors?

The hardware this server is running on, is a Intel Dual Core 2,66 GHz, 4GB Internal Memory, and 3x 200GB SATA HD, Mainboard is ASUS INTEL S775 P5WD2 Premium, i'am not running RAID, because the version of ubuntu i'am running doesn't recognise this onboard controller

Below the output of my.cnf

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
skip-innodb

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 384M
#max_allowed_packet = 16M
max_allowed_packet = 1M
thread_stack = 512K
table_cache = 128
sort_buffer_size = 2M
read_buffer_size = 2M
record_buffer = 32M
myisam_sort_buffer_size = 64M
thread_cache = 8
#128
wait_timeout = 30
thread_concurrency = 4
join_buffer_size = 4096K
max_heap_table_size = 128M


#
# * Query Cache Configuration
#
query_cache_limit = 1048576
query_cache_size = 16777216
query_cache_type = 1
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log = /var/log/mysql.log
#log = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
#log-slow-queries = /var/log/mysql/mysql-slow.log
#
# The following can be used as easy to replay backup logs or for replication.
#server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
# See /etc/mysql/debian-log-rotate.conf for the number of files kept.
max_binlog_size = 104857600
#binlog-do-db = include_database_name
#binlog-ignore-db = include_database_name
#
# * BerkeleyDB
#
# The use of BerkeleyDB is now discouraged and support for it will probably
# cease in the next versions.
skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Feature
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# If you want to enable SSL support (recommended) read the manual or my
# HOWTO in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt.gz
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

[falko]

The server i'am running is a usenetindexer. The database is 14GB big after running for 23 days now.I guess this is the problem - your database has become too big for your hardware.

[Yabadoo]

The problem is there from the beginning, even when the database had a size of 200mb.
Is there something i can optimize ??

[falko]

Did you ask in the NNTPgrab forum? Maybe they know more about this.

[Yabadoo]

I' will ask on the NNTPGrab forum.. Thanks..

[22hosting]

This is the recommended my.cnf for large systems, however it will probably need tinkering with. I suspect that the C++ program you are running reindexes your tables or something equivalently tasking. It WILL consume CPU time and there's probably not a lot you can do about it. You could probably set the thread-concurrency to 2 instead of 4 and see if that makes a difference (as it might use less threads).

# Example mysql config file for large systems.
#
# This is for large system with memory = 512M 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.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --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]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=16
log-bin
server-id = 1

# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=64M
#set-variable = 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: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
#set-variable = innodb_buffer_pool_size=256M
#set-variable = innodb_additional_mem_pool_size=20M
# Set .._log_file_size to 25 % of buffer pool size
#set-variable = innodb_log_file_size=64M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#set-variable = innodb_lock_wait_timeout=50

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

[mysqldump]
quick
set-variable = max_allowed_packet=16M

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

[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout
quick
set-variable = max_allowed_packet=16M
________
Bacardia (http://www.girlcamfriend.com/cam/Bacardia/)

[Yabadoo]

I think you made a good point here, it is indeed a c++ program wich indexes the tables, so there won't be much do to about that.
I will check my.cnf for some big differences. :)

[Ovidiu]

also read and use this for mysql tuning: http://hackmysql.com/mysqlreport