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.

my.conf tuning

Discussion in 'Workarounds and Optimization' started by newmanyak, Feb 21, 2013.

  1. newmanyak

    newmanyak Member

    Joined:
    Feb 21, 2013
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi all! My system VDS 2GB ram 1 Core CPU

    my.conf

    Code:
    [mysqld]
    tmpdir=/tmp
    safe-show-database
    max_connections=300
    max_user_connections=500
    key_buffer_size=768M
    myisam_sort_buffer_size=64M
    join_buffer_size=1M
    read_buffer_size=2M
    sort_buffer_size=2M
    table_cache=5000
    thread_cache_size=64
    wait_timeout=30
    connect_timeout=45
    tmp_table_size=950M
    max_heap_table_size=64M
    max_allowed_packet=256M
    thread_concurrency=32
    table_lock_wait_timeout=1
    query_cache_limit=4M
    query_cache_size=128M
    query_cache_type=1
    set-variable=local-infile=0
    local-infile=0
    innodb_file_per_table=1
    [isamchk]
    key_buffer=256M
    sort_buffer_size=256M
    read_buffer=2M
    write_buffer=2M
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    

    ./mysqltuner.pl

    mysqlcheck -o --all-databases and after 5 minutes, again fragmented tables

    Code:
      >>  MySQLTuner 1.2.0 - 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.1.61-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 46M (Tables: 135)
    [--] Data in InnoDB tables: 208K (Tables: 13)
    [!!] Total fragmented tables: 1
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 11d 8h 33m 21s (12M q [12.695 qps], 548K conn, TX: 232B, RX: 2B)
    [--] Reads / Writes: 73% / 27%
    [--] Total buffers: 906.0M global + 5.5M per thread (300 max threads)
    [!!] Maximum possible memory usage: 2.5G (127% of installed RAM)
    [OK] Slow queries: 0% (2/12M)
    [OK] Highest usage of available connections: 4% (12/300)
    [OK] Key buffer size / total MyISAM indexes: 768.0M/21.8M
    [OK] Key buffer hit rate: 99.9% (84M cached / 64K reads)
    [OK] Query cache efficiency: 60.6% (5M cached / 9M selects)
    [!!] Query cache prunes per day: 3169
    [OK] Sorts requiring temporary tables: 0% (8K temp sorts / 959K sorts)
    [OK] Temporary tables created on disk: 3% (8K on disk / 296K total)
    [OK] Thread cache hit rate: 99% (12 created / 548K connections)
    [OK] Table cache hit rate: 26% (172 open / 649 opened)
    [OK] Open file limit used: 3% (317/10K)
    [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
    [OK] InnoDB data size / buffer pool: 208.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
    
    and some times it seems top -c
    Code:
    /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server1.com.err --pid-file=
    pico server1.com.err last last lines...

    130210 12:47:50 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.

    130210 12:47:50 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.

    Code:
    130210 12:45:52 [Note] Event Scheduler: Purging the queue. 0 events
    130210 12:45:53  InnoDB: Starting shutdown...
    130210 12:45:58  InnoDB: Shutdown completed; log sequence number 0 119571
    130210 12:45:58 [Note] /usr/sbin/mysqld: Shutdown complete
    
    130210 12:45:58 mysqld_safe mysqld from pid file /var/lib/mysql/server1.com.pid ended
    130210 12:47:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    130210 12:47:50 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
    130210 12:47:50 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
    130210 12:47:50 [Note] Plugin 'FEDERATED' is disabled.
    130210 12:47:50  InnoDB: Initializing buffer pool, size = 8.0M
    130210 12:47:50  InnoDB: Completed initialization of buffer pool
    130210 12:47:50  InnoDB: Started; log sequence number 0 119571
    130210 12:47:51 [Note] Event Scheduler: Loaded 0 events
    130210 12:47:51 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.1.61-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
    
    thanks all.
     
    #1 newmanyak, Feb 21, 2013
    Last edited: Feb 21, 2013
  2. newmanyak

    newmanyak Member

    Joined:
    Feb 21, 2013
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    why nobody does not reply
     
    #2 newmanyak, Feb 25, 2013
    Last edited: Feb 25, 2013
  3. newmanyak

    newmanyak Member

    Joined:
    Feb 21, 2013
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Waiting for suggestions... 2GB + 2GB Swap ram vds

    ./mysqltuner.pl latest info mysql working time > 48h

    How can i fix this..

    Run OPTIMIZE TABLE to defragment tables for better performance?
    mysqlcheck -o --all-databases and after 5 minutes, again fragmented tables

    Reduce your overall MySQL memory footprint for system stability?
    Enable the slow query log to troubleshoot bad queries?

    Code:
     >>  MySQLTuner 1.2.0 - 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.1.61-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 46M (Tables: 135)
    [--] Data in InnoDB tables: 208K (Tables: 13)
    [!!] Total fragmented tables: 4
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 20h 50m 28s (1M q [11.394 qps], 82K conn, TX: 34B, RX: 353M)
    [--] Reads / Writes: 72% / 28%
    [--] Total buffers: 906.0M global + 5.5M per thread (300 max threads)
    [!!] Maximum possible memory usage: 2.5G (127% of installed RAM)
    [OK] Slow queries: 0% (3/1M)
    [OK] Highest usage of available connections: 4% (12/300)
    [OK] Key buffer size / total MyISAM indexes: 768.0M/21.5M
    [OK] Key buffer hit rate: 99.5% (12M cached / 64K reads)
    [OK] Query cache efficiency: 60.8% (800K cached / 1M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 1% (1K temp sorts / 136K sorts)
    [OK] Temporary tables created on disk: 3% (1K on disk / 42K total)
    [OK] Thread cache hit rate: 99% (12 created / 82K connections)
    [OK] Table cache hit rate: 21% (174 open / 813 opened)
    [OK] Open file limit used: 7% (319/4K)
    [OK] Table locks acquired immediately: 99% (889K immediate / 889K locks)
    [OK] InnoDB data size / buffer pool: 208.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
    
    latest pico /etc/my.cnf

    Code:
    [mysqld]
    tmpdir=/tmp
    skip-name-resolve
    safe-show-database
    max_connections=300
    max_user_connections=500
    key_buffer_size=768M
    myisam_sort_buffer_size=64M
    join_buffer_size=1M
    read_buffer_size=2M
    sort_buffer_size=2M
    table_cache=2048
    thread_cache_size=64
    wait_timeout=30
    connect_timeout=45
    tmp_table_size=950M
    max_heap_table_size=64M
    max_allowed_packet=256M
    thread_concurrency=32
    table_lock_wait_timeout=1
    query_cache_limit=4M
    query_cache_size=64M
    query_cache_type=1
    local-infile=0
    innodb_file_per_table=1
    [isamchk]
    key_buffer=128M
    sort_buffer_size=128M
    read_buffer=2M
    write_buffer=2M
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    pico /var/lib/mysql/server1.com.err

    some warning alerts

    Code:
    130226 18:53:59 mysqld_safe mysqld from pid file /var/lib/mysql/server1.com.pid ended
    130226 18:56:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    130226 18:56:05 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
    130226 18:56:05 [Note] Plugin 'FEDERATED' is disabled.
    130226 18:56:05  InnoDB: Initializing buffer pool, size = 8.0M
    130226 18:56:05  InnoDB: Completed initialization of buffer pool
    130226 18:56:05  InnoDB: Started; log sequence number 0 514228
    130226 18:56:06 [Warning] 'user' entry 'root@server1.com' ignored in --skip-name-resolve mode.
    130226 18:56:06 [Warning] 'user' entry 'user@server1.com' ignored in --skip-name-resolve mode.
    130226 18:56:06 [Warning] 'db' entry 'user\_forumdb dbname_root@server1.com' ignored in --skip-
    130226 18:56:06 [Note] Event Scheduler: Loaded 0 events
    130226 18:56:06 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.1.61-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
    
     
  4. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    For slow query logging, add the below to your my.cnf

    Code:
    log-slow-queries=/var/lib/mysql/slow.log
    Then run the below
    touch /var/lib/mysql/slow.log
    chmod 660 /var/lib/mysql/slow.log
    chown mysql:mysql /var/lib/mysql/slow.log
    Restart MySQL
     
  5. newmanyak

    newmanyak Member

    Joined:
    Feb 21, 2013
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    thanks you for reply. mysql is running from 17 hours but the log is empty

    pico /var/lib/mysql/slow.log

    Code:
    /usr/sbin/mysqld, Version: 5.1.61-cll (MySQL Community Server (GPL)). started with:
    Tcp port: 0  Unix socket: (null)
    Time                 Id Command    Argument
     
    #5 newmanyak, Mar 3, 2013
    Last edited: Mar 3, 2013
  6. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Try changing log-slow-queries to slow-query-log in my.cnf and restarting.
     
  7. newmanyak

    newmanyak Member

    Joined:
    Feb 21, 2013
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    ./mysqltuner.pl I see again

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Enable the slow query log to troubleshoot bad queries

    Anyway I gave up from logs thanks
     
  8. anton_latvia

    anton_latvia Well-Known Member
    PartnerNOC

    Joined:
    May 11, 2004
    Messages:
    348
    Likes Received:
    3
    Trophy Points:
    18
    Location:
    Latvia
    cPanel Access Level:
    Root Administrator
    Hm.. What is the exact problem? Looking at your original my.cnf.. I would lower these values:

    Code:
    key_buffer_size=256M
    myisam_sort_buffer_size=64M
    tmp_table_size=512M
    max_heap_table_size=32M
    max_allowed_packet=32M
    if not even lower. Having those values set to big values does not always mean "speed and memory caching".
     
  9. newmanyak

    newmanyak Member

    Joined:
    Feb 21, 2013
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    ok thanks anton, why mysqlcheck -o --all-databases and after 5 minutes, again fragmented tables

    Code:
     >>  MySQLTuner 1.2.0 - 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.1.61-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 48M (Tables: 135)
    [--] Data in InnoDB tables: 208K (Tables: 13)
    [!!] Total fragmented tables: 25
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 8h 52m 44s (1M q [14.072 qps], 73K conn, TX: 31B, RX: 269M)
    [--] Reads / Writes: 73% / 27%
    [--] Total buffers: 362.0M global + 5.5M per thread (300 max threads)
    [!!] Maximum possible memory usage: 2.0G (100% of installed RAM)
    [OK] Slow queries: 0% (0/1M)
    [OK] Highest usage of available connections: 5% (17/300)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/24.0M
    [OK] Key buffer hit rate: 99.7% (10M cached / 32K reads)
    [OK] Query cache efficiency: 61.4% (738K cached / 1M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 1% (1K temp sorts / 119K sorts)
    [OK] Temporary tables created on disk: 4% (1K on disk / 35K total)
    [OK] Thread cache hit rate: 99% (17 created / 73K connections)
    [OK] Table cache hit rate: 45% (188 open / 414 opened)
    [OK] Open file limit used: 7% (333/4K)
    [OK] Table locks acquired immediately: 99% (794K immediate / 794K locks)
    [OK] InnoDB data size / buffer pool: 208.0K/8.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
    
     
Loading...

Share This Page