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.

Mysql optimizations tips from 4GB to 16GM Ram

Discussion in 'Workarounds and Optimization' started by johnburk, Feb 27, 2012.

  1. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    Mysql optimizations tips from 4GB to 16GB Ram

    I am upgrading my server from a dual-core AMD Opteron 1222 with 4GB Ram to a quad-core Intel Xeon E3-1220 with 16GB Ram.

    Any suggestions how I can improve mysql performance and utilize the 16GB Ram efficiently?

    My current settings are:

    Code:
    [--] Data in MyISAM tables: 1G (Tables: 2044)
    [--] Data in InnoDB tables: 7M (Tables: 357)
    [--] Data in MEMORY tables: 0B (Tables: 1)
    
    
    [mysqld]
    bulk_insert_buffer_size = 8M
    concurrent_insert = 2
    connect_timeout = 100
    default-storage-engine = MyISAM
    innodb_buffer_pool_size=16M
    interactive_timeout = 100
    join_buffer_size = 2M
    key_buffer_size = 192M
    local-infile=0
    log-error=/var/log/mysql/error.log
    log-slow-queries
    log-slow-queries=/var/log/mysql/mysql-slow.log
    long_query_time=1
    
    max_allowed_packet = 32M
    max_connections = 360
    max_heap_table_size = 256M
    max_user_connections= 400
    max_write_lock_count = 8
    myisam_max_sort_file_size = 256M
    myisam_sort_buffer_size = 64M
    open_files_limit=128K
    query_alloc_block_size = 65536
    query_cache_limit = 16M
    query_cache_size = 128M
    query_cache_type = 1
    query_prealloc_size = 262144
    range_alloc_block_size = 4096
    read_buffer_size = 2M
    read_rnd_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 48K
    thread_cache_size = 512
    tmp_table_size = 256M
    transaction_alloc_block_size = 4096
    transaction_prealloc_size = 4096
    wait_timeout = 100
     
    #1 johnburk, Feb 27, 2012
    Last edited: Feb 27, 2012
  2. Quemeros

    Quemeros Member

    Joined:
    Mar 4, 2012
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi Jhon,
    did you try mysqltunner.pl and tuning-primer.sh? (search in google). Both give you advices to get a very good job.
    By the way, you can multiply by 3 (or 4, the amount of new RAM) all variables without change the kind of concurrent_insert, connect_timout, etc that not reffer to Memory or some thing that use it. A simple trick, all 8,16 (binary exclud 2 and 4) multiples are Memory thing's, and M is of Megabyte, can multiply too.
    But i recomend to use first programs, and increse always when they recomend you ;).
    Remember restart MySQL after change settings, and wait almost 2 day's for look again status with that tool's.

    Bye! :)
     
  3. johnburk

    johnburk Well-Known Member

    Joined:
    Jun 23, 2006
    Messages:
    241
    Likes Received:
    0
    Trophy Points:
    16
    Thank you. Yes I have tried both tools. The problem is that both tools are really not made for 16GB of ram and simply multiplying everything will perhaps not be the most efficient way to do this.

    Most optimization tips I find online seem to be heavily outdated and focus on systems with 1GB to 4GB of ram.

    Perhaps there are options to server all tables (this server only has 1G tables) from ram.
     
  4. tank

    tank Well-Known Member

    Joined:
    Apr 12, 2011
    Messages:
    236
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Chicago, IL
    cPanel Access Level:
    Root Administrator
    Can you please post the mysqltuner recommendations. It hard to recommend when not seeing what the puppy has to say. But I am interested in finding a way to optimize 4GB to 16GB of RAM. Currently looking to do something very similar myself.
     
Loading...

Share This Page