1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL tunning

Discussion in 'Server Configuration and Hosting' started by Deriel, Sep 18, 2013.

  1. Deriel

    Deriel Well-Known Member

    Hi all!

    I'm (yet) in the process of migrating my 3M posts vB4.2.1 to XenForo and something that's really bothering my is the "Rebuild Users" time. With 50k users it's taking some 12 hours!

    Could someone please my configs and tell me if I'm doing something stupid? :)

    Code:
    Linux ZM1AU39241.alnitech.com 2.6.32-358.14.1.el6.x86_64 #1 SMP Tue Jul 16 23:51:20 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
    Intel(R) Xeon(R) CPU E31230 @ 3.20GHz
    16GB RAM
    3M posts with 50k users (about 300 online at once)
    
    my.cnf:

    Code:
    [mysqld]                                                                       
    datadir=/var/lib/mysql                                                         
    socket=/var/lib/mysql/mysql.sock                                               
    user=mysql                                                                     
    symbolic-links=0                                                               
    key_buffer = 1024M                                                             
    
    sort_buffer = 2M                                                               
    join_buffer = 8M                                                               
    max_allowed_packet = 16M                                                       
    
    max_heap_table_size = 256M                                                     
    table_cache = 4096                                                             
    sort_buffer_size = 16M                                                         
    
    read_buffer_size = 2M                                                         
    read_rnd_buffer_size = 2M                                                     
    myisam_sort_buffer_size = 96M                                                 
    thread_cache_size = 1024                                                       
    
    query_cache_type = 0                                                           
    query_cache_limit = 8M   
    ##query_cache_size = 16M
    query_cache_size = 0
    max_connections = 500                                                         
    
    tmp_table_size = 256M                                                         
    thread_concurrency = 8                                                         
    low_priority_updates = 1                                                       
    max_write_lock_count = 1                                                       
    
    [isamchk]                                                                     
    key_buffer = 1024M
    sort_buffer = 128M                                                             
    read_buffer = 32M                                                             
    write_buffer = 32M                                                             
    
    [myisamchk]                                                                   
    key_buffer = 1024M
    sort_buffer = 128M                                                             
    read_buffer = 32M                                                             
    write_buffer = 32M                                                             
    
    #INNODB
    innodb_read_ahead = none
    innodb_flush_method  = O_DIRECT                                               
    innodb_log_files_in_group  = 2                                                 
    innodb_log_file_size  = 256M                                                   
    innodb_flush_log_at_trx_commit = 2                                             
    innodb_file_per_table  = 1                                                     
    ##innodb_buffer_pool_size  = 8G                                                 
    innodb_buffer_pool_size  = 10G
    innodb_additional_mem_pool_size=64M     
    ##innodb_thread_concurrency=0 
    innodb_thread_concurrency = 4
    innodb_lock_wait_timeout=50
    innodb_log_buffer_size=8M
    innodb_io_capacity = 2000
    innodb_read_io_threads = 4
    innodb_write_io_threads = 2                                   
    
    [mysqld_safe]                                                                 
    log-error=/var/log/mysqld.log                                                 
    pid-file=/var/run/mysqld/mysqld.pid
    
     

Share This Page