Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Server Operation

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 10th April 2006, 15:59
Yabadoo Yabadoo is offline
Member
 
Join Date: Apr 2006
Location: Noord-Brabant, Netherlands
Posts: 39
Thanks: 5
Thanked 0 Times in 0 Posts
Post MySQLd CPU Load

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.
Reply With Quote
Sponsored Links
  #2  
Old 10th April 2006, 17:29
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,740 Times in 2,575 Posts
Default

What kind of script do you run? What does it do?
Anything in the logs when the load goes up?
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #3  
Old 10th April 2006, 18:09
22hosting 22hosting is offline
Junior Member
 
Join Date: Apr 2006
Posts: 26
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Last edited by 22hosting; 22nd August 2011 at 13:04.
Reply With Quote
  #4  
Old 12th April 2006, 20:23
Yabadoo Yabadoo is offline
Member
 
Join Date: Apr 2006
Location: Noord-Brabant, Netherlands
Posts: 39
Thanks: 5
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5  
Old 12th April 2006, 23:01
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,740 Times in 2,575 Posts
Default

Quote:
Originally Posted by Yabadoo
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.
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #6  
Old 12th April 2006, 23:32
Yabadoo Yabadoo is offline
Member
 
Join Date: Apr 2006
Location: Noord-Brabant, Netherlands
Posts: 39
Thanks: 5
Thanked 0 Times in 0 Posts
Default

The problem is there from the beginning, even when the database had a size of 200mb.
Is there something i can optimize ??
Reply With Quote
  #7  
Old 13th April 2006, 14:37
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,740 Times in 2,575 Posts
Default

Did you ask in the NNTPgrab forum? Maybe they know more about this.
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #8  
Old 16th April 2006, 14:01
Yabadoo Yabadoo is offline
Member
 
Join Date: Apr 2006
Location: Noord-Brabant, Netherlands
Posts: 39
Thanks: 5
Thanked 0 Times in 0 Posts
Default

I' will ask on the NNTPGrab forum.. Thanks..
Reply With Quote
  #9  
Old 18th April 2006, 11:29
22hosting 22hosting is offline
Junior Member
 
Join Date: Apr 2006
Posts: 26
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Last edited by 22hosting; 22nd August 2011 at 13:08.
Reply With Quote
  #10  
Old 18th April 2006, 19:10
Yabadoo Yabadoo is offline
Member
 
Join Date: Apr 2006
Location: Noord-Brabant, Netherlands
Posts: 39
Thanks: 5
Thanked 0 Times in 0 Posts
 
Default

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.
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

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 On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
My mysqld broke domino Server Operation 12 12th October 2008 05:56
Howto suggestion suse PhP ver 4 + Ver 5 wwparrish Suggest HOWTO 11 7th August 2006 13:29
Load balancing on Fedora Care 4 luxpops HOWTO-Related Questions 1 4th April 2006 18:14
installing ispconfig Kills Mysqld meso129 Installation/Configuration 1 1st April 2006 20:14
Is There A LinuxOS That Can Handle Load Balancing/WAN Redundancy? bluegrass Installation/Configuration 0 8th March 2006 01:30


All times are GMT +2. The time now is 21:20.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.