Discussion in 'Server Operation' started by Yabadoo, Apr 10, 2006.

  1. Yabadoo

    Yabadoo Member

    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.
  2. falko

    falko Super Moderator Howtoforge Staff

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

    22hosting New Member

    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: Aug 22, 2011
  4. Yabadoo

    Yabadoo Member

    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 =

    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

    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.
    socket = /var/run/mysqld/mysqld.sock
    nice = 0

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

    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address =
    # * 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
    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.
    # * 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

    max_allowed_packet = 16M

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

    key_buffer = 16M
  5. falko

    falko Super Moderator Howtoforge Staff

    I guess this is the problem - your database has become too big for your hardware.
  6. Yabadoo

    Yabadoo Member

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

    falko Super Moderator Howtoforge Staff

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

    Yabadoo Member

    I' will ask on the NNTPGrab forum.. Thanks..
  9. 22hosting

    22hosting New Member

    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
    #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
    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
    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
    #set-variable = innodb_lock_wait_timeout=50

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

    set-variable = max_allowed_packet=16M

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

    set-variable = key_buffer=128M
    set-variable = sort_buffer=128M
    set-variable = read_buffer=2M
    set-variable = write_buffer=2M

    set-variable = key_buffer=128M
    set-variable = sort_buffer=128M
    set-variable = read_buffer=2M
    set-variable = write_buffer=2M

    set-variable = max_allowed_packet=16M
    Last edited: Aug 22, 2011
  10. Yabadoo

    Yabadoo Member

    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. :)
  11. Ovidiu

    Ovidiu Active Member

Share This Page