The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Mysql RES size

Discussion in 'Database Discussions' started by Sannin, Feb 5, 2013.

  1. Sannin

    Sannin Active Member

    Joined:
    May 19, 2011
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    Hello

    The database size on my shared server is growing quite large. Right now its size is 3649 Mbs after 19 days uptime. However maximum possible memory usage is at 1,8 Gbs according to mysql tuner. I guess that something in my config causes this. Can you please take a look?

    Code:
    # /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    
     >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.28-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 2G (Tables: 13464)
    [--] Data in InnoDB tables: 7M (Tables: 243)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 58)
    [!!] Total fragmented tables: 248
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 18d 23h 26m 29s (651M q [397.507 qps], 3M conn, TX: 11801B, RX: 1419B)
    [--] Reads / Writes: 94% / 6%
    [--] Total buffers: 1.3G global + 2.9M per thread (200 max threads)
    [OK] Maximum possible memory usage: 1.8G (11% of installed RAM)
    [OK] Slow queries: 0% (48K/651M)
    [OK] Highest usage of available connections: 26% (52/200)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/480.0M
    [OK] Key buffer hit rate: 100.0% (45B cached / 458K reads)
    [OK] Query cache efficiency: 72.3% (448M cached / 620M selects)
    [!!] Query cache prunes per day: 3347568
    [OK] Sorts requiring temporary tables: 0% (47K temp sorts / 12M sorts)                                                            
    [!!] Joins performed without indexes: 1184561                                                                                     
    [!!] Temporary tables created on disk: 26% (4M on disk / 15M total)                                                               
    [OK] Thread cache hit rate: 99% (55 created / 3M connections)                                                                     
    [!!] Table cache hit rate: 0% (26K open / 71M opened)                                                                             
    [OK] Open file limit used: 15% (39K/262K)                                                                                         
    [OK] Table locks acquired immediately: 99% (258M immediate / 260M locks)
    [OK] InnoDB data size / buffer pool: 7.6M/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 128M)
        join_buffer_size (> 256.0K, or always use indexes with joins)
        tmp_table_size (> 128M)
        max_heap_table_size (> 128M)
        table_cache (> 65536)
    Code:
    [mysql]
    
    # CLIENT #
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    
    [mysqld]
    
    # GENERAL #
    user                           = mysql
    default_storage_engine         = MyISAM
    socket                         = /var/lib/mysql/mysql.sock
    pid_file                       = /var/lib/mysql/mysql.pid
    
    # MyISAM #
    key_buffer_size                = 1G
    myisam_recover                 = FORCE,BACKUP
    
    # SAFETY #
    max_allowed_packet             = 16M
    max_connect_errors             = 1000000
    local-infile                   = 0
    
    # DATA STORAGE #
    datadir                        = /var/lib/mysql/
    
    # BINARY LOGGING #
    log_bin                        = /var/lib/mysql/mysql-bin
    expire_logs_days               = 1
    sync_binlog                    = 1
    
    # CACHES AND LIMITS #
    tmp_table_size                 = 128M
    max_heap_table_size            = 128M
    query_cache_type               = 1
    query_cache_size               = 128M
    query_cache_limit              = 4M
    query_cache_min_res_unit       = 2K
    max_connections                = 200
    thread_cache_size              = 40
    open_files_limit               = 262144
    table_definition_cache         = 65536
    table_open_cache               = 65536
    
    join_buffer_size               = 256K
    
    # INNODB #
    innodb_flush_method            = O_DIRECT
    innodb_log_files_in_group      = 2
    innodb_log_file_size           = 32M
    innodb_flush_log_at_trx_commit = 1
    innodb_file_per_table          = 1
    innodb_buffer_pool_size        = 8M
    
    # LOGGING #
    log_error                      = /var/lib/mysql/mysql-error.log
    #log_queries_not_using_indexes  = 1
    slow_query_log                 = 1
    slow_query_log_file            = /var/lib/mysql/mysql-slow.log
    long_query_time                = 1
    Code:
    mysql     666532 15.0 23.0 7134136 3737104 ?     Sl   Jan18 4101:38  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql/ --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysql-error.log --open-files-limit=262144 --pid-file=/var/lib/mysql//hostname.pid --socket=/var/lib/mysql/mysql.sock
     
    #1 Sannin, Feb 5, 2013
    Last edited: Feb 5, 2013
Loading...

Share This Page