Looking for some MySQL improvements

  • Filter
  • Time
  • Show
Clear All
new posts

  • Looking for some MySQL improvements

    I'm with heavy use in MySQL MyIsam and want to make some improvements. I'm kindly asking your advice and suggestions.

    Current situation:

    Since las 11 days, 6 million pages served (taking into account also crawlers) with 2 billion mysql queries at a rate of 350 queries per page and 2.3 k queries per second. I've run the slow queries log and I've take a tour creating few missing indexes. Some things cannot be improved easily by design. So in the meanwhile I'll to attack concurrently the cnf file.

    Red values are:

    Handler_read_rnd 904 M
    Handler_read_rnd_next 30 G
    Qcache_lowmem_prunes 132 M
    Created_tmp_disk_tables 13 M
    Key_reads 46 M
    Key_writes 27 M
    Select_full_join 45 k
    Select_range_check 46
    Sort_merge_passes 21 k
    Opened_tables 2,597 k
    Table_locks_waited 1,069 k

    I've some extra memory to use so I'm think to changes these settings:

    join_buffer_size = 131072 -> 64M
    key_bufer_size = 32M -> 256M
    query_cache_size = 64M -> 512M
    read_buffer_size = 2M -> 64M (deleting "read_buffer" line)
    sort_buffer_size = 2M -> 256M
    table_cache = 256 -> table_open_cache = 1024
    query_cache_type = 1 (today not present)
    myisam_sort_buffer_size = 64M -> 256M

    I'll appreciate your suggestions.
    Thanks and Regards

  • #2
    Suggestions: change only one thing at a time, then benchmark. Do not change sort_buffer_size or read_buffer_size. Read the server documentation on each variable you are thinking about changing.