Delayed SQL processes - New vB 3.7 import

Wild Onion

Wild Onion

Member
I'm still working out the kinks in my VB import. My board has about 100k threads and about 2.2 million posts. Database is 3859 MB.

Site is fast, then every minute or two, I get a bunch of delayed or locked SQL processes. I've disabled all add-ons. Below are my settings. Any glaring problems?

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=8M
query_cache_size=128M
query_cache_type=1
max_connections=400
max_user_connections=60
interactive_timeout=60
wait_timeout=20
connect_timeout=30
thread_cache_size=128
#key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=100
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
#log-bin
server-id=1

open_files_limit=4520
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
---
Click to expand...
 
I also am sometimes getting this error

An exception occurred: User theio3_halo already has more than 'max_user_connections' active connections in /home/theio3/public_html/library/Zend/Db/Adapter/Mysqli.php on line 333

  1. Zend_Db_Adapter_Mysqli->_connect() in Zend/Db/Adapter/Abstract.php at line 315
  2. Zend_Db_Adapter_Abstract->getConnection() in XenForo/Application.php at line 684
  3. XenForo_Application->loadDb()
  4. call_user_func_array() in XenForo/Application.php at line 921
  5. XenForo_Application->lazyLoad() in XenForo/Application.php at line 952
  6. XenForo_Application::get() in XenForo/Application.php at line 1336
  7. XenForo_Application::getDb() in XenForo/Error.php at line 52
  8. XenForo_Error::unexpectedException() in XenForo/Application.php at line 363
  9. XenForo_Application::handleException()
Click to expand...
 
You should really enable InnoDB and convert the relevant tables in the database, as XenForo was designed to work with InnoDB.
 
It depends on just how busy your site is. Increase it to 100, and see if you get it again. If you do, keep going.....
 
Enabled InnoDB and increased max connections to 100. I'm not getting the max connections error, but I'm still getting the bad delays in executing SQL processes.
 
You guys rock. Thanks for all the help.

After changing the tables over to InnoDB, normal response times are much better. However, it still hangs every few minutes. It's not as bad as before. We seem to notice it the most on posting replies.
 
You must log in or register to reply here.
Back
Top Bottom