Mysql optmization

Discussion in 'Linux VPS/Dedicated - General' started by stormrider, Aug 16, 2007.

  1. stormrider

    stormrider New Member

    Hello,

    Could someone recommend me a mysql optmization tutorial?

    Thanks.
     
  2. BAlGaInTl

    BAlGaInTl New Member

    I think this will largely depend on a couple of things:

    Which VPS plan / control panel you have, and what your typical usage is.
     
  3. stormrider

    stormrider New Member

    Hello,

    I have VPS XXL + Cpanel and the use is basically to host forums ( phpbb ) and blogs ( wordpress ).

    What do you recommend me to do?

    CPU usage is over 4.50 ( according to WHM )
     
  4. onelove

    onelove New Member

    My my.conf settings

    Hi Stormrider,

    I host mainly Drupal sites and 1 wordpress blog. Drupal sites are database hungry and run many queries to the db, so it is important to enable mysql cache. There are few other important things to look at like table_cache and max connections.

    The default interactive_timeout, wait_timeout and connect_timeout should be adjusted depending on your vps accounts memory. I have around 588M on my account and the settings below made a huge performance difference as compared to the default, which are usually set for servers with at least a gig of ram.

    Code:
    skip-locking
    skip-innodb
    max_connections=200
    key_buffer=32M
    myisam_sort_buffer_size=64M
    join_buffer=2M
    record_buffer=1M
    sort_buffer_size=3M
    read_buffer_size=2M
    table_cache=2048
    thread_cache_size=286
    interactive_timeout=25
    wait_timeout=100
    connect_timeout=10
    query_cache_limit=2M
    query_cache_size=32M
    query_cache_type=1
    max_allowed_packet=16M
    max_connect_errors=999999
    thread_concurrency=4
    tmp_table_size = 32M
    
    I would suggest that you customise the above to your servers requirements. Wordpress runs much less queries than Drupal.

    A very useful tool is
    http://www.day32.com/MySQL/tuning-primer.sh
    this shell script which gives you suggestions on how to optimise your my.conf settings.
     

Share This Page