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

MySQL tunning

Deriel

Well-known member
#1
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