Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: Very laggy server - Assistance appreciated!

    Hello,
    I am in need of assistance of sorting out an overburdened database server. It appears to be doing excessive amounts of table scans.

    mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1
    load average: 1.61, 2.18, 2.26

    Situation: The software running is HLStatsX, which is a community stats system for many online based games. A daemon receives game logs from multiple servers and parses and saves the data into the daemon.
    As i am unexperienced with MySQL and did not write this, I am unsure on how to troubleshoot it. Even the slightest bit of assistance is appreciated!

    The website is Surf-Infamous.com - Contents for viewing the web front end.

    Server spec:
    Ubuntu Server
    Pentium D 3ghz Dual Core
    4gb RAM
    2x Hitachi 7200RPM in RAID1

    All tables are MyISAM. Here is the status page of phpmyadmin
    Code:
    http://stats.surf-infamous.com/statuspage.html
    I logged queries without indexes for a minute or two. This isn't logging slow queries, just the ones without indexes. I can log slow queries if this would help.
    Here is the log
    Code:
    http://stats.surf-infamous.com/slowquery.log
    Contents of my.cnf, apologies for silly mistakes, several people have attempted throwing more resources at the problem but its clearly not working.
    Code:
    [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
    #
    
    #
    # * IMPORTANT
    #   If you make changes to these settings and your system uses apparmor, you may
    #   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
    #
    
    user            = mysql
    socket          = /var/run/mysqld/mysqld.sock
    port            = 3306
    basedir         = /usr
    datadir         = /var/lib/mysql
    tmpdir          = /var/tmpfs
    slave_load_tmpdir       = /tmp
    skip-external-locking
    #skip-grant-tables
    skip-name-resolve
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address            = 96.43.143.2
    #
    # * Fine Tuning
    #
    key_buffer              = 256M
    max_allowed_packet      = 32M
    thread_stack            = 192K
    thread_cache_size       = 384
    max_connections         = 500
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    #max_connections        = 100
    table_cache            = 20480
    #thread_concurrency     = 10
    connect_timeout         = 10
    interactive_timeout     = 120
    join_buffer_size        = 4M
    sort_buffer_size        = 6M
    read_buffer_size        = 6M
    read_rnd_buffer_size    = 8M
    innodb_buffer_pool_size = 176M
    #
    # * Query Cache Configuration
    #
    query_cache_limit       = 8M
    query_cache_size        = 256M
    
    max_heap_table_size     = 64M
    tmp_table_size          = 256M
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file        = /var/log/mysql/mysql.log
    #general_log             = 1
    
    log_error                = /var/log/mysql/error.log
    
    # Here you can see queries with especially long duration
    #log_slow_queries       = /var/tmpfs/slowquery.log
    #long_query_time = 20000000
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id              = 1
    #log_bin                        = /var/log/mysql/mysql-bin.log
    expire_logs_days        = 10
    max_binlog_size         = 100M
    #binlog_do_db           = include_database_name
    #binlog_ignore_db       = include_database_name
    #
    # * 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 Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    #
    # 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
    
    #
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    !includedir /etc/mysql/conf.d/
    Any help is significantly appreciated!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    There are several SQL statements which are taking excessively long to complete for the number of rows that are being returned. I have filtered the query times from the slow log and this is what I see:

    # Query_time: 4.091963 Lock_time: 0.000097 Rows_sent: 0 Rows_examined: 786335
    # Query_time: 10.316334 Lock_time: 0.000123 Rows_sent: 1 Rows_examined: 1458489
    # Query_time: 10.327622 Lock_time: 0.000134 Rows_sent: 1 Rows_examined: 1458491
    # Query_time: 10.686265 Lock_time: 0.000112 Rows_sent: 1 Rows_examined: 1458489
    # Query_time: 10.722302 Lock_time: 0.000116 Rows_sent: 1 Rows_examined: 1458489
    # Query_time: 11.319295 Lock_time: 0.000143 Rows_sent: 1 Rows_examined: 1458491
    # Query_time: 11.341616 Lock_time: 0.000133 Rows_sent: 1 Rows_examined: 1458491
    # Query_time: 12.061641 Lock_time: 0.000104 Rows_sent: 1 Rows_examined: 1458491
    # Query_time: 12.220498 Lock_time: 0.000105 Rows_sent: 1 Rows_examined: 1458489
    # Query_time: 12.396043 Lock_time: 0.000137 Rows_sent: 1 Rows_examined: 1458491

    The rows sent indicates the number of rows sent back to the client. I would start with these queries and try to resolve these one first. They appear to be doing huge amounts of work but returning minimal numbers of rows.

    Also your table cache does not appear to be high enough and is still causing problems. Increasing this will speed up access to some of the tables (files).
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Oct 2011
    Posts
    2
    Is this relevant at all?
    table_cache negative scalability - MySQL Performance Blog

    I will look at those queries, but i'm unsure how to optimize/fix them as i don't know how this software works exactly.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Do you know whether performance has been gradually getting worse over time? Are there lots of inserts and deletes in each of the tables? Perhaps coalescing the table data and rebuilding the indexes might help with this?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •