Hi, following the installation of MyBB 1.4 Beta onto a live production board (I know it is strongly advised against), I appear to be experiencing high server load averages.
anyway, does this quiery mean anything to you as it's killing the mysql server which in turn is killing the VPS :s
"SELECT t.tid, t.dateline, p.edittime, t.subject, f.allowhtml, f.allowmycode, f.allowsmilies, f.allow"
I'm sure that this quiery can't last for over 7 hours..., and I can't find what uses this table. MySQL tells me that it is "Copying to tmp".
I have consulted advice for MySQL experts as I'm sure that the software is not to blame at this point.
==============================================================================
EDIT: Found the problem to be mysql and copying to tmp
the syndication query? There is definately something wrong with your MySQL server. I can run that query fine on a 3 MILLION post setup (live-production board).
Can I remove the syndication.php temporarily?
I doubt it's going to change anything. Your server will continue to crash until the issue gets fixed. (And this is an issue in general with MySQL queries on your server timing out, so if you remove syndication.php then it'll be forumdisplay.php, then showthread.php, then index.php assuming this is indeed the issue....)
If you tell me how many posts / threads / users you have on your site then I can recommend some changes
Can you attach your my.cnf here, or PM me it's contents?
[mysqld]
skip-bdb
safe-show-database
#old_passwords
back_log = 50
skip-innodb
max_connections = 350
key_buffer_size = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 2500
thread_cache_size = 128
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 8
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 3M
query_cache_size = 48M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 16
[mysqld_safe]
nice = -10
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
Thanks
Wow, there's a lot there for an installation like yours. Can you post the full query? That was only 1 part of it. Check the slow query logs
The server is refusing to make slow query logs for some reason :?
There's a bug in mysql code:
http://bugs.mysql.com/bug.php?id=14070
I'll keep flushing the tables - however, you may want to add more indexes to the tables just so that this doesn't happen again - on someone else's board.
We have plenty of indexes on the tables. Again, like I said, it runs fine on a 3 million post board. It's something to do with your MySQL version / MySQL setup. Upgrade to the latest stable version of MySQL and see if that changes anything. By the looks of it your running a pretty old version of MySQL.