3 x mirror smart load balanced web servers
1 x MySQL db server 4G 64-bit Opterons
2000 simultaneous boards users pretty much caps it.
What can I do?
skip-locking
key_buffer = 1536M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 8M
read_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 128M
max_connections = 1500
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
"4G 64-bit Opterons"
???? Is that 4GB RAM or 4 64-bit Opterons?
How much RAM do you have?
Do you have any stats from running mysqladmin extended-status?
Do you have other applications using MySQL?
4G of RAM. 2 proc Opterons. We are probably going to take the db server to 12G RAM if that will help, but I haven't started hitting swap yet with the current MySQL configuration. I actually saw a load of 70 last night, but the machine recovered fine when the user number went down.
Remember that MySQL is only one component. You should have a look at the webserver configuration also.
I think that using a second db-server and some kind of replication and load balacing will be better than just increasing the RAM. Getting a load ~ 70 without swapping lets me suppose that the RAM isn't the current bottleneck.
All the servers in the web cluster perform fine. They run at less than .50 load. What should I look at in the web config?
Is Threads replication aware? I was reading through the MySQL replication documentation and it says that the app has to know to read from the slaves and write to the master.
Anyone know what some of the biggest Threads boards are (simultaneous users)? It would be cool to know what they run.
I can't give you any specific advises without knowing details about your infrastructure.
ubb.Threads doesn't support MySQL replication builtin, but it's possible. I developed that for a client some time ago. If you're interested in that, just drop me am PM or email.
2000 simultaneous users is quite much, how many posts do you have in your database? I know some boards with about 2mio posts and up to 1000 users online.
Your keybuffer is very high, you should lower it. No table in ubb.threads has such large keys. Better define join_buffer and read_round buffer instead.