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

Discussion in 'General Discussion' started by trparky, Apr 27, 2005.

  1. trparky

    trparky Well-Known Member

    Joined:
    Apr 23, 2003
    Messages:
    184
    Likes Received:
    1
    Trophy Points:
    0
    We have a server that has 4 GB of RAM and a MySQL 4.1.10-standard server with a rather large InnoDB table, somewhere around 1.5 GB.

    We have set the following in /etc/my.cnf, but queries that include JOINs with the 1.5 GB InnoDB table take forever to process.

    [mysqld]
    set-variable = max_connections=1000
    set-variable = query_cache_size=128M
    set-variable = thread_cache_size=100
    set-variable = innodb_buffer_pool_size=125829120
    set-variable = table_cache=128
    set-variable = key_buffer_size=120M
    set-variable = tmp_table_size=120M
    safe-show-database

    Any more suggestions on some more optimizations?
     
  2. trparky

    trparky Well-Known Member

    Joined:
    Apr 23, 2003
    Messages:
    184
    Likes Received:
    1
    Trophy Points:
    0
    Anybody know anything about optimizing a MySQL server for very large (huge) databases? I need this soon.
     
  3. Ramsy

    Ramsy Guest

  4. jester.ro

    jester.ro Well-Known Member
    PartnerNOC

    Joined:
    Feb 6, 2004
    Messages:
    304
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Bucharest, Romania
    cPanel Access Level:
    DataCenter Provider
    theese are my optimizations for s server with 2 GB of ram and alot of mysql processes(big databases, but not as big as yours - 100-200 MB, but ALOT of them)


    query_cache_limit=4M
    query_cache_size=64M
    query_cache_type=1
    max_connections=570
    interactive_timeout=10
    wait_timeout=10
    connect_timeout=10
    thread_cache_size=1024
    thread_cache=256
    table_cache=1024
    key_buffer=64M
    join_buffer=64k
    max_allowed_packet=128M
    table_cache=1024
    record_buffer=4M
    sort_buffer_size=8M
    read_buffer_size=8M
    max_connect_errors=10
    thread_concurrency=2
    myisam_sort_buffer_size=64M
    tmp_table_size=256M
    max_heap_table_size=256M
    max_tmp_tables=256

    after months of fighting with it, this seems to be version that suits me :)

    i only have one processor, so i keep thread_concurency at 2. If you have 2 processors, try 4. if you have dual xeons HT, try 8 (but also test it with 4).
     
Loading...

Share This Page