As more people join my site, the more it slows down. I am leasing a VPS linux centOS with 1 gig of memory. Server information: Forum software - VBulletin PHP version 5.2.8 PHP Max Post Size 32.00 MB PHP Maximum Upload Size 32.00 MB PHP Memory Limit 256.00 MB MySQL Version 5.0.67-community MySQL Packet Size 16.00 MB Web Server Apache v1.3.41 Currently the forum has: Threads: 44,634 Posts: 539,447 Members: 13,441 Active Members: 3,964 and gets around 2.9 million page views a month. March 2nd 2009 was my busiest days with around 10,555 people visiting the site. my.cnf [mysqld] skip-bdb skip-innodb set-variable = max_connections=1024 set-variable = max_user_connections=900 set-variable = interactive_timeout=40 set-variable = wait_timeout=40 set-variable = connect_timeout=20 safe-show-database key_buffer = 384M max_allowed_packet = 32M table_cache = 1024 sort_buffer_size = 64M key_buffer_size = 128M long_query_time = 5 read_buffer_size = 16M myisam_sort_buffer_size = 64M thread_cache = 128 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 16 safe-show-database tmp_table_size = 32M max_heap_table_size = 32M old-passwords = 1 Code (markup): I have gone through the Server Configuration on vbulletin.com, submitted support tickets to the hosting provider and still can not get the site performance to stabilize. If anymore then 400 people get on the site, its slows to around 20 second response time - and this is mostly when viewing threads. Going to the home page, the load time is 2 - 3 seconds. Why does the home page load fast, but the threads do not? What can I do to start trouble shooting the issue? Where do I even start at?
do you regularly run database repairs and optimize the tables? what is your VPS's dedicated bandwidth? 100mbit?
I might run the database repair in the vb control panel maybe once a month, maybe once every 2 months. On the dedicated bandwidth, I am not sure about that.
usually if there is more then 1 site being hosted on the VPS some of them could be taking up the CPU and ram and the bandwidth also. leaving your forum with less. if that isnt the case, you could havelow bandwidth say only 50mbit then when theres 2k users loading your page at once the site will get a bit laggy. i suggest to run database checks and optimize them form the admin CP once a week. just to much sure you never get on the database error and threads slowing down. then every now and then not often i would rebuild threads and update the counters. maybe consult your host about increasing your bandwidth if it isnt a server resource side of things.
Out of interest, did you just pluck random numbers when coming up with that my.cnf file as there is no way any sound logic went into producing it. I'd suggest scrapping it completely and starting again, basing it off the example files bundled with MySQL (my-large.cnf would probably be a good starting point, locate my-large.cnf). Then do some research before wildly altering variables. sort_buffer_size and read_buffer_size are per connection variable, with 1GB ram, these shouldn't need to be anywhere over 1MB, in fact with 4GB, I wouldn't have them set over 2MB, let alone the crazy values you have. If you don't know what you are doing, use a tool such as the mysql tuning primer <http://www.day32.com/MySQL/> which will at least give you some hints as what to do.
Here's the important settings, in my opinion: set-variable = open_files_limit=16392 set-variable = key_buffer_size=256M set-variable = query_cache_size=128M set-variable = query_cache_limit=1M set-variable = sort_buffer_size=16M But this inevitably comes down to trial-and-error for your hardware and server load. The VPS may be overloaded, or, you may not be really changing the my.cnf settings. Some VPS's are set up so users see a 'local' setting, but the 'global' settings override everything.. So your changes are meaningless. It may be well worth while to get yourself on your own server. I wouldn't recomend VPS.
Some of the numbers were taken from the vbulletin support forums at vbulletin.com, some of them were increased at random. The site has a totally revised my.cnf, closer to what you suggested and is preforming a lot better. But response times are still in the 2 - 8 second range. 2 - 3 seconds is ok, but when I start getting close to 8, 9 or 10 seconds - that is too long. Keep in mind, I am running 8 sites off of this one server. That my.cnf is a global configuration for all 8 sites. On that one server I am running: 3 - vbulletin forums 1 - smf forum 5 - wordpress sites I have decided to lease another VPS. The other 3 forums will be off loaded to that server. After looking at the traffic graphs, I saw that the traffic has doubled in the past 6 months. In October 2008, my forum had around 2,800 visitors daily. Currently, the site gets over 7,000 visitors daily. The same thing with total page views - its doubled in the past 6 months. By going with a second server, its going to be saving about $70 a month, as compared to getting an entry level dedicated. I checked into the prices over the past couple of days. The growth of the forum has been steady for the past 18 months, by doubling every 6 months. I have to make plans for what the growth might be like in 6 more months. And that is why I am going to go with a second server and off load some of my smaller forums onto it. Even though "right now" the server may be responding well, I have to stay ahead of the growth to ensure proper response times.