1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL performance issues?

Discussion in 'Server Configuration and Hosting' started by Mouth, Oct 14, 2013.

  1. Mouth

    Mouth Well-Known Member

    Hi,

    Trial monitoring service gives the following feedback ...

    Error : Heavy join rate value 43.9675727229 % should be less than 20.0 %
    Warning : Heavy join rate value 43.9675727229 % should be less than 10.0 %
    Error : On-disk temporary table rate value 53.9682539683 % should be less than 20.0 %
    Warning : On-disk temporary table rate value 53.9682539683 % should be less than 10.0 %
    Error : Query cache miss rate value 24.8371431955 % should be less than 20.0 %
    Warning : Query cache miss rate value 24.8371431955 % should be less than 10.0 %
    Error : Innodb buffer pool usage value 96.8673467052 % should be less than 90.0 %
    Warning : Innodb buffer pool usage value 96.8673467052 % should be less than 80.0 %


    Feedback/thoughts and, if necessary, advice on how and where to further identify and resolve these issues. Normally I'd ask someone to do it, but I'd like to start and learn a little more (consider me between entry level and competent) about the configuring and tweaking/tuning of MySQL

    Server: i3 2100 dedicated with 4Gb RAM and single SATA disk
    OS: Debian. Average load during heavy/peak usage is 0.8, with io-wait at 15%
    MySQL: Percona 5.5 (config changes/additions below)
    http: Nginx, with PHP5-FPM
    Xf: 22,000 users, average 250 online and peaking at 550. 2.5Gb DB with 1.93 million posts.

    Code:
    # As per https://tools.percona.com/ with add-ons from me in # JASON # section
    
    [mysqld]
    # GENERAL #
    bind-address            = 127.0.0.1
    default-storage-engine  = InnoDB
    
    # myISAM #
    key_buffer              = 32M
    myisam_recover          = FORCE,BACKUP
    
    # SAFETY #
    max_allowed_packet      = 16M
    max_connect_errors      = 1000000
    sql_mode                = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_EN
    GINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
    sysdate_is_now          = 1
    innodb                  = FORCE
    innodb_strict_mode      = 1
    
    # BINARY LOGGING #
    log_bin                = /var/lib/mysql/mysql-bin
    expire_logs_days        = 14
    sync_binlog            = 1
    
    # CACHES AND LIMITS #
    tmp_table_size          = 64M
    max_heap_table_size    = 64M
    query_cache_type        = 1
    query_cache_size        = 64M
    query_cache_limit      = 40M
    max_connections        = 150
    thread_cache_size      = 50
    open_files_limit        = 65535
    table_definition_cache  = 2048
    table_open_cache        = 2048  # synonyms with table_cache
    
    # INNODB #
    innodb_flush_method            = O_DIRECT
    innodb_log_files_in_group      = 2
    innodb_log_file_size            = 128M
    innodb_flush_log_at_trx_commit  = 2
    innodb_file_per_table          = 1
    innodb_buffer_pool_size        = 2G
    
    # LOGGING #
    log_error                      = /var/log/mysql/mysql-error.log
    slow_query_log                  = 1
    slow_query_log_file            = /var/log/mysql/mysql-slow.log
    long_query_time                = 2
    
    # JASON #
    tmpdir                  = /var/tmp
    low_priority_updates    = 1
    concurrent_insert      = ALWAYS
    back_log                = 2048
    innodb_log_buffer_size  = 8M
    
    
    [mysqld_safe]
    open_files_limit        = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 32M
    
    [mysqlhotcopy]
    interactive-timeout
    

    Thanks.
     
  2. MattW

    MattW Well-Known Member

    Are you on a 64bit version of Debian?

    Code:
    Error : Innodb buffer pool usage value 96.8673467052 % should be less than 90.0 %
    Warning : Innodb buffer pool usage value 96.8673467052 % should be less than 80.0 %
    So your current buffer pool is at 2GB, and you are at 96% of that. Server has 4GB RAM, so I'd look at getting another 4GB of RAM in the box and upping your buffer pool first.
     
  3. Mouth

    Mouth Well-Known Member

    Yes

    Yes, but unfortunately not an option at the current time.
     
  4. MattW

    MattW Well-Known Member

    You'll not be able to get around that warning then with the current amount of RAM available and the size of your DB.

    Do you actually notice any performance issues, or is it just the warnings you are getting from that monitoring service?
     
  5. Mouth

    Mouth Well-Known Member

    No performance issues, and nothing of any significance within slow_queries logging, or queries_without_indexes logging. It was more about trying to learn what these warnings meant, and why.
     

Share This Page