Hey Steve,

You may have already tried these things, and since I don't have any specific logs yet (I just saw your post on vBulletin, I'll try taking a gander at that zip file later).

Have you tried giving the search SELECT a low-priority attribute? Maybe this will keep the UPDATE's from piling up behind a few search queries.

Any thought of migrating to InnoDB tables? Does vBulletin support InnoDB? Is it stable enough yet in .47?

Have you check OS-level file descriptor limits? (ulimit -n) ... are they at 8192? That should be put up there no matter what...

Did you try getting rid of (if used) low-priority updates and testing some max_write_lock_count values like 20 or so? (don't quite know your traffic/access numbers) Did you try dropping the maximum number of db-connections down to prevent the box from hanging and throw maximum connect messages instead?

Have you tried tuning up MySQL a bit more? Your KeyBuffer looked pretty good, 1000:1 ratio, though I thought it was strange that for such a large db that a key-buffer of 16M was sufficient...... Something scaled more toward large DB's, like (I've only included values that are different then in your post):

set-variable = max_allowed_packet=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
set-variable = table_cache=256 (you only showed 140 or so opened tables at a given time in your post, 1024 is overkill at that point)

[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M

[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M

Any thought on trying another bbs package? Somethign that is more large-table compatible, like UBBThreads? I know that would probably be a last resort as retooling is always a pain.

I also strongly agree with the others recomendations of dropping the number of maxclients in your apache config down. Having that many children with so few parents is not the best way to run it. If you're running out of connections with 10*512 clients, you'll want to expand the number of servers, not clients....10*512 would give you 5120 concurrent connections to the site, I'd say on average that will cover 300,000 or so daily hit average pretty easy. Besides that if you hit 10*1024 that you'll suck up 384mb ram for webservers (given an average apache instance of 32mb)
... You have keep-alives turned on and tuned I'm assuming (to keep concurrent connections down and improve end-user experience).

When I was administrating Tommy Hilfiger (www.tommy.com), even with 900,000 hits a day, we never ran out of httpd processes when I was running 25*512 across 2 servers. (max of 17,920*2 concurrent connections using two servers, with 900k hits, we would peak at 10,000 concurrent during big promos.) That almost meant that we were using at max, about 600mb of ram for webservers (it was three tier, so ram wasn't an issue on the boxes, combine that with the SSL servers and we were tuned to max 2GB E250's pretty well, with horizontal scaling being an easy option)..

Daniel

 

Hey Steve,

Was this any help?

Daniel

 

Daniel,

It was, thank you very much. I have been tweaking the MySQL configurations for quite some time, but they have had little to no effect on the table locking problems...which is at the heart of the problem. Some of your suggestions look familiar.

To answer a couple of your questions directly:
The biggest issue with that right now is support...the developers of vB have not yet publicly committed to supporting InnoDB tables and several dozen queries would have to be rewritten within the code to support them. Another alternative is NuSphere's MySQL Advantage product, which uses Gemini tables. Both would theoretically fix the locking problem (and I would gladly purchase a "commercial" product if I had 100% confidence it would fix the problem for good), but not without changes to the underlying forum software code or endorsement from the vendor. Fortunately (I think) more and more vB customers are experiencing this issue so I can only keep my fingers crossed that a future version will support alternative table types.

Right now it's a painful waiting game...
I don't think using UBBThreads will help out much. I have been in contact with at least a half-dozen site operators that are using the latest/greatest version and they are having even more MySQL problems than we are.

 

Cool.

When you were talking to UBBboard owners, were you talking to people that run UBB or UBBThreads (InfoPop has two products) UBBThreads used to be WWWThreads and is "supposed" to handle large databases better, but I can't speak from experience for it. I run it, but it is on a low-volume site.

I have a big Oracle friend I'm going to tap to see if he can give any pointers on the issue... I have a copy of vBulletin board, I'll see if I can get some time to poke around in the code and see if there is anything that blares out at me...

I would definately do the same on not wanting to buy the commercial product until it is know if it would actually fix the problem. I had seen some users with vBulletin experimenting with InnoDB a little ways back, but I didn't know if it every improved since I had stopped following vBulletin as much when I switched to wwwThreads/UBBThreads.

BTW, congrats on breaking the gigabyte-barrier. Its always impressive on a non-huge-commerical-conglomerate site.

Daniel

 

Definitely spoke with UBBThreads users (some new since Infopop purchased it, some WWWThreads users), not UBB users. Infopop actually has three products, including their hosted solution, OpenTopic. We used UBB here for almost three years, from the 3.x through 5.47, until we outgrew it and knew v6 was actually a step backwards. (perl + file-based + 300,000 messages = disaster) Thus I went with vB.

 

Yeah, was definately a wise choice there. I can't imagine the choke hold regular UBB would have had with the perl setup. Gah.

Alright, I'll keep my eyes/ears open and if I get some time, poke around the the vBulletin SQL...Let you know if I find anything else.

Daniel