If you are running a true high volume production server like I am, creating large temp tables is suicide. It's like hitting swap on the server. You have exhausted MySQL's memory/cache settings, and so it's resorting to temp tables to get the job done.
Before we go any further ... what kind of table type are you using ... MyISAM or InnoDB?
MyISAM most likely - yes it is
(2008-07-14, 10:00 PM)Tikitiki Wrote: [ -> ]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.
we are using 5.0.51, the latest STABLE.
My settings... I'm too busy ATM to go through and compare one by one .... but this should be a good starting place.
#-------------------------------------------------------
#>>>>> Memory Optimizations >>>>>>>>>>>>>>>>>>>>>>>>>>>>
#-------------------------------------------------------
max_heap_table_size = 100M
query-cache-type=1
query_cache_limit=2M
query_cache_size=128M
max_allowed_packet = 1M
max_connections = 600
max_connect_errors=10
interactive_timeout=100
wait_timeout=1800
connect_timeout=10
table_cache = 2000
thread_cache_size = 256
tmp_table_size=32M
key_buffer = 512M
key_buffer=16M
join_buffer=16M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size=6M
myisam_sort_buffer_size = 6M
tmp_table_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
Nope, that didn't work
Good lord don't blindly copy my config in there ... that's tuned for my RAM and load characteristics. It's meant as a basis for comparison!
Turn on slow query query logging and report back with slow queries.
(2008-07-14, 10:09 PM)ct2k7 Wrote: [ -> ] (2008-07-14, 10:00 PM)Tikitiki Wrote: [ -> ]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.
we are using 5.0.51, the latest STABLE.
Then how come you linked to a bug report that was filed for 5.0.16 and the 4.x series?
(2008-07-14, 11:24 PM)ct2k7 Wrote: [ -> ]let's try logging again.
Ok - where the hell are my threads gone.....
http://movies.thinkteen.co.uk - the threads are gone....
What did you change? Revert to your old config... Don't blindly copy someone's config to yours
Sounds to me like somebody's server hardware is the problem
Corrupt RAM? Failing RAID? Failing drive?
# mysqlcheck -r -o -u (yourusername) -p (your mybb database name)
I had to make the mysql-slow-queries.log file manually and then chmod it writable...it's probably a permissions issues with mysql not having access to the folder.
If you want to throw me phpmyadmin access I can examine in details what's going on. There are some good tools in phpmyadmin now to help view the problems.
Also how much ram does your VPS provide? I know some offer as little as 128MB which imho ain't enough to run a decent mysql setup let alone apache too. Who is your VPS provider? Are they reputable?
Hi, let me PM you the root details of the server. my server has 512 RAM, and is run by Virpus Network, reputable
.