GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Increased load on server

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Increased load on server

    Hello all,

    I am having a strange problem where my database server load average increase as the number of updates and selects on 3 of my larges tables go up. The biggest ones (160GB) are MyISAM tables and the third is an innodb table (2GB).

    The server is my master database so it gets a lot of updates but because the large tables are so big, I have to do selects on them from the master because if I did my selects on the slave it will increase their replication lag. I am considering moving to innodb but I want to make sure I have tried all options before I make that move because the tables are soo large. Has anyone had this problem before and maybe have any ideas how to solve it?

    I also noticed that the more the number of active connections I have, the cpu system waits go up when there is a lot of activity on these tables.

    An help would be welcomed. my mysql config is show below.
    [mysqld]
    log-bin
    replicate-ignore-db=mysql

    log-slave-updates

    server-id = 52001001
    set-variable = max_connections=2500
    set-variable = back_log=20
    set-variable = key_buffer=8000M
    set-variable = table_cache=4000
    set-variable = max_allowed_packet=4M
    set-variable = sort_buffer=2M
    set-variable = record_buffer=4M
    set-variable = myisam_sort_buffer_size=64M
    set-variable = thread_cache=200
    set-variable = long_query_time=10
    set-variable = query_cache_size=256M
    set_variable = wait_timeout=60

    transaction-isolation = READ-COMMITTED
    skip-slave-start

    innodb_additional_mem_pool_size=20M

    innodb_flush_method=O_DIRECT

    innodb_flush_log_at_trx_commit=2

    innodb_log_buffer_size=256M

    innodb_buffer_pool_size=2G

    # Have eight threads running concurrently
    innodb_thread_concurrency=8

    # let's have a file per table
    innodb_file_per_table

    ################################################## ########## ############

    log-bin = /var/lib/mysql/b2db01-new-bin

    relay-log = /var/lib/mysql/b2db09-relay-bin

    log-slow-queries = /var/lib/mysql/slow.log
    skip-name-resolve
    tmpdir = /var/lib/mysql/tmp

    #core-file

    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1
    #user=root

    [mysql.server]
    user=mysql
    #core_file_size = unlimited


    [mysqld_safe]
    log-error=/var/log/mysqld.log
    log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    [client]
    socket=/var/lib/mysql/mysql.sock
    #user = root

  • #2
    Why is it strange that your load increases if you ask your server to do more for you?
    If a query takes a long time on a MyISAM table, then all update/insert queries have to wait. Check http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html

    Comment

    Working...
    X