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.

How to Optimize MySQL

Discussion in 'Workarounds and Optimization' started by joe94500, Feb 5, 2014.

  1. joe94500

    joe94500 Registered

    Joined:
    Feb 5, 2014
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello. This is my first post. I recently upgraded to my first vps. I notice my server is a bit slow.
    my.config is as follows:

    Code:
    [mysqld]
    
    default-storage-engine=MyISAM
    interactive_timeout=300
    key_cache_block_size=4096
    max_heap_table_size=32M
    max_join_size=1000000000
    max_allowed_packet=32M
    
    open_files_limit=4096
    query_cache_size=32M
    thread_cache_size=100
    tmp_table_size=32M
    
    
    wait_timeout=7800
    max_user_connections=5000
    
    myisam_recover_options=FORCE
    innodb_file_per_table=1
    
    innodb_flush_log_at_trx_commit=0
    innodb_purge_threads=1
    innodb_support_xa=0
    
    innodb_thread_concurrency=8
    pid-file=/var/lib/mysql/mysqld.pid
    
    How can it be configured for maximum performance?
    Thank you.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    651
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    run mysqltuner.pl
     
  4. joe94500

    joe94500 Registered

    Joined:
    Feb 5, 2014
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello, thanks cPanelMichael and thinkbot for answering my question and pointing me in the right direction. I
    I have a table with more than a million rows. When trying to go to one of the bottom rows, I get this error:
    Error writing file '/tmp/MYOV5w52' (Errcode: 28).
    Code:
    server summary
    
    Filesystem      Size  Used Avail Use% Mounted on
    /dev/vda1        30G   21G  7.5G  74% /
    tmpfs           939M     0  939M   0% /dev/shm
    /usr/tmpDSK     3.0G  176M  2.6G   7% /tmp
    
    
    my.cnf (This is current mycnf)
    
    [mysqld]
    tmpdir=/tmp
    innodb_file_per_table=1
    open_files_limit=50000
    myisam_use_mmap=1
    
    max_connections = 200
    max_user_connections = 75
    
    join_buffer_size=2M
    sort_buffer_size=2M
    
    table_open_cache = 8000
    table_definition_cache = 6000
    max_allowed_packet = 1G
    
    thread_cache_size = 384
    
    query_cache_type = 1
    query_cache_size = 100M
    query_cache_limit = 1M
    
    max_heap_table_size = 50M
    tmp_table_size = 300M
    
    key_buffer_size = 2500M
    
    innodb_buffer_pool_size = 3G
    innodb_log_buffer_size = 32M
    innodb_old_blocks_time=1000
    innodb_concurrency_tickets=5000
    innodb_autoextend_increment=64M
    innodb_open_files=2000
    innodb_stats_on_metadata=0
    
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    mysqltuner result

    Code:
    - General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.35-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 6G (Tables: 293)
    [--] Data in InnoDB tables: 224K (Tables: 14)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 45
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 37m 24s (909 q [0.405 qps], 127 conn, TX: 608K, RX: 75K)
    [--] Reads / Writes: 68% / 32%
    [--] Total buffers: 5.6G global + 4.6M per thread (200 max threads)
    [!!] Maximum possible memory usage: 6.5G (356% of installed RAM)
    [OK] Slow queries: 4% (39/909)
    [OK] Highest usage of available connections: 6% (13/200)
    [OK] Key buffer size / total MyISAM indexes: 2.4G/2.4G
    [!!] Key buffer hit rate: 85.3% (685 cached / 101 reads)
    [OK] Query cache efficiency: 47.4% (225 cached / 475 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 12 sorts)
    [OK] Temporary tables created on disk: 11% (2 on disk / 17 total)
    [OK] Thread cache hit rate: 89% (13 created / 127 connections)
    [OK] Table cache hit rate: 98% (379 open / 386 opened)
    [OK] Open file limit used: 1% (666/50K)
    [!!] Table locks acquired immediately: 93%
    [OK] InnoDB data size / buffer pool: 224.0K/3.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Optimize queries and/or use InnoDB to reduce lock wait
    What is the best way to proceed to optimize the mysql? NB: I don't understand plenty of this stuff.
     
Loading...

Share This Page