PDA

View Full Version : Good old Open Source



DimPrawn
16th November 2009, 13:27
Database error
The database has encountered a problem.

--------------------------------------------------------------------------------

Please try the following:
•Load the page again by clicking the Refresh button in your web browser.
•Open the forums.contractoruk.com home page, then try to open another page.
•Click the Back button to try another link.

The forums.contractoruk.com forum technical staff have been notified of the error, though you may contact them if the problem persists.

We apologise for any inconvenience.

administrator
16th November 2009, 14:05
Yeah, you are not notified what the problem was but I am. Too many DB connections, the DB server is struggling with TPD. Night is drawing in on the thread I am afraid. The executioner is donning his hood. The congregation will be asked to deliver the last rites...

DimPrawn
16th November 2009, 14:06
Yeah, you are not notified what the problem was but I am. Too many DB connections, the DB server is struggling with TPD. Night is drawing in on the thread I am afraid. The executioner is donning his hood. The congregation will be asked to deliver the last rites...

You realise AtW is going to come in his pants on this news?

minestrone
16th November 2009, 14:09
It must be a world record for the largest thread on a forum.

realityhack
16th November 2009, 14:14
There must be some other way. We're a bunch of IT 'experts' here for goodness sake - can't we think of a way to keep TPD and free the forum up a bit?

Come on PHP gurus, don your thinking caps.

administrator
16th November 2009, 14:16
A whip round for a few more GB of RAM might be enough to keep it going for a few hundred more posts... Will see how much this machine has in it and see what the cost would be for some more. If anyone has some optimisation tips then don't be shy.

DimPrawn
16th November 2009, 14:17
There must be some other way. We're a bunch of IT 'experts' here for goodness sake - can't we think of a way to keep TPD and free the forum up a bit?

Come on PHP gurus, don your thinking caps.

I can think of a way

DELETE FROM threads WHERE thread_title = 'test please delete'

:D

ctdctd
16th November 2009, 14:17
Yeah, you are not notified what the problem was but I am. Too many DB connections, the DB server is struggling with TPD. Night is drawing in on the thread I am afraid. The executioner is donning his hood. The congregation will be asked to deliver the last rites...

Oh no - please don't.

It's a bit like Terry Wogan, the Archers, or even John Peel (R.I.P.). You never listen or partake but are comforted by the fact that they will always be there.

TheFaQQer
16th November 2009, 14:18
There must be some other way. We're a bunch of IT 'experts' here for goodness sake - can't we think of a way to keep TPD and free the forum up a bit?

Come on PHP gurus, don your thinking caps.

Over to you, Nick!

Andy2
16th November 2009, 14:19
I can think of a way

DELETE FROM threads WHERE thread_title = 'test please delete'

:D

better still
Drop database CUK ;

TheFaQQer
16th November 2009, 14:20
I can think of a way

DELETE FROM threads WHERE LOWER(thread_title) LIKE '%my lunch%'

:D

That should free up a fair bit of room.

There are far bigger threads than TPD out there running vBulletin with no problems.

DimPrawn
16th November 2009, 14:20
better still
Drop database CUK ;

Format C:\

Even better.

TheFaQQer
16th November 2009, 14:21
A whip round for a few more GB of RAM might be enough to keep it going for a few hundred more posts... Will see how much this machine has in it and see what the cost would be for some more. If anyone has some optimisation tips then don't be shy.

I'd best get cracking - another 61 posts to get to 10k

realityhack
16th November 2009, 14:24
What about splitting TPD off into a sub-forum, TPD having a separate process?

Or deleting all threads in Light Relief except TPD, 1-a-day, the jokes thread and a few other choice ones?

Or, archiving all TPD posts older than 6 months into a separate thread off the bulletin board? A TPD Net-split?

NickFitz
16th November 2009, 14:27
There have been 70-odd posts on TPD since 8:30 this morning. There have been a lot more than that over that time period in General. I assume there have also been posts in B/C, A/L and Technical, and probably to other LR threads.

So why should TPD be singled out?

If it's because of the length of the thread, that would imply that the database itself needs to be optimised. It really shouldn't be breaking over something like that. Certainly that shouldn't be causing too many database connections: at present the number of people viewing each forum are:

General 185
B/C 60
A/L 108
Technical 15
Light Relief 24

and as each of those viewers requires at least one DB connection per page view, it's quite obvious that it can't be TPD consuming too many connections: it must be General that is using the greatest number of connections.

DimPrawn
16th November 2009, 14:30
There have been 70-odd posts on TPD since 8:30 this morning. There have been a lot more than that over that time period in General. I assume there have also been posts in B/C, A/L and Technical, and probably to other LR threads.

So why should TPD be singled out?

If it's because of the length of the thread, that would imply that the database itself needs to be optimised. It really shouldn't be breaking over something like that. Certainly that shouldn't be causing too many database connections: at present the number of people viewing each forum are:

General 185
B/C 60
A/L 108
Technical 15
Light Relief 24

and as each of those viewers requires at least one DB connection per page view, it's quite obvious that it can't be TPD consuming too many connections: it must be General that is using the greatest number of connections.

Delete everything else and leave TPD. Delete all your paying sponsors adverts.

Think like a techie for god sake!

DiscoStu
16th November 2009, 14:30
There must be some other way. We're a bunch of IT 'experts' here for goodness sake - can't we think of a way to keep TPD and free the forum up a bit?

Come on PHP gurus, don your thinking caps.

Where? :D

TheFaQQer
16th November 2009, 14:32
We tried to take TPD into Freelancer UK, but they kept kicking us out!

(BTW, I just tried to quote RH's post, and it failed to quote)

DimPrawn
16th November 2009, 14:32
Delete this thread it is using up electrons.

DELETE FROM posts WHERE user_id IN (SELECT user_id FROM morons)


Should do it.

NickFitz
16th November 2009, 14:33
"As I've mentioned in the beginning of my blog post – what's really cool in vBulletin 4.0 is not necessarily apparent to the untrained eye. Previously, vBulletin utilized the fulltext index on both “thread” and “post” tables making optimization and server problem resolution very expensive and complicated. vBulletin 4.0 no longer needs the fulltext index to be present in either of the “thread” or “post” tables. This means that vBulletin 4.0 database can utilize the InnoDB table engine and prevent a lot of common database issues associated with locked database queries. This is great news for small to medium board owners who will be able to resolve some performance overhead problems without having unnecessary expenditures for expensive hosting or complicated third-party search engines."

- From vBulletin.com (http://www.vbulletin.com/forum/entry.php?2376-Part-1-vB4mance-Helping-communities-grow-performance-data-model-changes-in-vB-4-0), their emphasis.

Any plans to upgrade? IIRC they pulled some stunt like forcing you to upgrade if you want to receive updates anyway... lots of people were pretty annoyed about that.

Anyway I'll see if I can come up with anything that could help, although I don't have the vBulletin code to work with: I may have to buy a copy to see where it's failing :nerd

minestrone
16th November 2009, 14:33
Would the connection not be created and killed in the request anyway? The query to get the posts for TPD will be done in a range so I cannot see how a TPD page view/post would be any worse than any other topic.

Also the number of views compared to posts is quite low.

God knows why I care though.

realityhack
16th November 2009, 14:35
"As I've mentioned in the beginning of my blog post – what's really cool in vBulletin 4.0 is not necessarily apparent to the untrained eye. Previously, vBulletin utilized the fulltext index on both “thread” and “post” tables making optimization and server problem resolution very expensive and complicated. vBulletin 4.0 no longer needs the fulltext index to be present in either of the “thread” or “post” tables. This means that vBulletin 4.0 database can utilize the InnoDB table engine and prevent a lot of common database issues associated with locked database queries. This is great news for small to medium board owners who will be able to resolve some performance overhead problems without having unnecessary expenditures for expensive hosting or complicated third-party search engines."- From vBulletin.com (http://www.vbulletin.com/forum/entry.php?2376-Part-1-vB4mance-Helping-communities-grow-performance-data-model-changes-in-vB-4-0), their emphasis.

Any plans to upgrade? IIRC they pulled some stunt like forcing you to upgrade if you want to receive updates anyway... lots of people were pretty annoyed about that.

Anyway I'll see if I can come up with anything that could help, although I don't have the vBulletin code to work with: I may have to buy a copy to see where it's failing :nerd

Admin - can't you let NF take a look under the hood?

Weren't you thinking of upgrading vBulletin a while ago anyway?

Andy2
16th November 2009, 14:35
Get the names of top ten posters and drop them

TheFaQQer
16th November 2009, 14:36
So why should TPD be singled out?

Because it always is, because of the TPD clique.

As a warning to those contemplating the TPD deletion - such action could cause tay to return.

DimPrawn
16th November 2009, 14:37
You could upgrade to a proper database and a proper operating system.

I'm sure you can afford a copy of Windows if you save up.

:laugh

NickFitz
16th November 2009, 14:40
Because it always is, because of the TPD clique.

As a warning to those contemplating the TPD deletion - such action could cause tay to return.

:laugh

realityhack
16th November 2009, 14:41
Tips optimising servers for large forums (>1m posts) (http://www.vbulletin.com/forum/showthread.php?111239-The-Large-vBulletin-Forums-1-million-posts-Discussion-Thread)

Andy2
16th November 2009, 14:41
Why not charge users to post on TPD
say 50p a post.
it will be a self supporting thread

EternalOptimist
16th November 2009, 14:42
Will cooperate and free up a connection in 20 mins when I go to the pub


DROP CONNECTION ID ='EO' GOTO PUB


hic

:rolleyes:

administrator
16th November 2009, 14:44
That should free up a fair bit of room.

There are far bigger threads than TPD out there running vBulletin with no problems.

Examples please. I have struggled to find any.


There have been 70-odd posts on TPD since 8:30 this morning. There have been a lot more than that over that time period in General. I assume there have also been posts in B/C, A/L and Technical, and probably to other LR threads.

So why should TPD be singled out?



Because TPD is a single thread containing over 200,000 posts. A lot computation to work out last post in thread, last unread post etc. I am sure you can understand this. It is about the amount of posts in the thread not the amount of threads in the forum.



If it's because of the length of the thread, that would imply that the database itself needs to be optimised. It really shouldn't be breaking over something like that. Certainly that shouldn't be causing too many database connections: at present the number of people viewing each forum are:

General 185
B/C 60
A/L 108
Technical 15
Light Relief 24

and as each of those viewers requires at least one DB connection per page view, it's quite obvious that it can't be TPD consuming too many connections: it must be General that is using the greatest number of connections.

Yes but when the DB is taking a long time to sort the TPD thread to display the info the end user has requested it holds up other users' requests and these time out. This has been a minor problem for the last few weeks but is becoming a daily occurence now when the forum is busy.

Yes, the DB could be optimized but there is no way of guaranteeing that a non-standard DB will upgrade correctly for future versions of VB that we may want to implement. With resource specific problems like this I have often found that upgrading hardware is quicker and easier than trying to weed out small gains through code optimisation.

RichardCranium
16th November 2009, 14:44
Any plans to upgrade?Feel free to copy 'n' paste the following in an email...


Dear vBulletin support,

I'm the sysadmin for the UK's foremost techie site for IT consultants, CUK. Your wobbly vbulletin v3 software is flaky and the geeks are getting restless. It's not looking good for you.

To save your reputation over here in Europe, I suggest you bung us a free upgrade to v4 sharpish before these nerds go ballistic and start chucking out your systems & bitching about you to their clients.

Yours in anticipation,

CUK admin

RichardCranium
16th November 2009, 14:47
Tips optimising servers for large forums (>1m posts) (http://www.vbulletin.com/forum/showthread.php?111239-The-Large-vBulletin-Forums-1-million-posts-Discussion-Thread)Blimey, 8 million posts and that was nearly eight years ago.

DiscoStu
16th November 2009, 14:48
I have a highly technical solution to this issue that will take approximately 2 minutes and completely resolve the problem:

Lock the old TPD and have sasguru start a new one.

<<Stu in "Thinking outside the box" mode>>

FiveTimes
16th November 2009, 14:50
I have a highly technical solution to this issue that will take approximately 2 minutes and completely resolve the problem:

Lock the old TPD and have sasguru start a new one.

<<Stu in "Thinking outside the box" mode>>

Loving your work Stu :D

realityhack
16th November 2009, 14:51
Loving your work Stu :D
This is, indeed, a quality suggestion.

scotspine
16th November 2009, 14:52
it *does* sound like either a poor db, poor db configuration or bad code or a bit of some/all.

NickFitz
16th November 2009, 14:53
Yes but when the DB is taking a long time to sort the TPD thread to display the info the end user has requested it holds up other users' requests and these time out. This has been a minor problem for the last few weeks but is becoming a daily occurence now when the forum is busy.

Yes, the DB could be optimized but there is no way of guaranteeing that a non-standard DB will upgrade correctly for future versions of VB that we may want to implement. With resource specific problems like this I have often found that upgrading hardware is quicker and easier than trying to weed out small gains through code optimisation.

Dunno if this might be any help: How fast can you sort data with MySQL ? (http://www.mysqlperformanceblog.com/2007/08/18/how-fast-can-you-sort-data-with-mysql/)

I'm not suggesting changing the DB from its existing structure necessarily; there may well be optimisations that can be made in terms of the server configuration. That comment on the thread RH linked to about using myISAM rather than InnoDB was possibly relevant.

I'll start digging through that copy of High Performance mySQL that's been sitting on the shelf... :nerd

administrator
16th November 2009, 14:55
Sorry Nick, missed your other post about VB4. Definitely looking to upgrade at some point but would rather the VB community bug checked it for a few months before we try it :wink

Just checking some other bits and pieces as well but this machine only having 2GB RAM means that a quick and simple solution could be more memory in the short-term. Happy to let Nick have a look at the site in more detail - if you are up for it Nick.

No great rush with all of this, we are not looking to delete it right here and now. And if we were to do anything with it would be more likely that we simply close that thread and you can start a part II - should not be the end of the world - and that would be only if we cannot do anything to keep it going.

Reports of use of INNODB over MyISAM - good to stop table locking, which may be the problem here, but users often end up going back to MyISAM due to performance issues from threads that I have read in the past.

administrator
16th November 2009, 14:57
I have a highly technical solution to this issue that will take approximately 2 minutes and completely resolve the problem:

Lock the old TPD and have sasguru start a new one.

<<Stu in "Thinking outside the box" mode>>

Was typing my reply when you wrote that - honest :D

realityhack
16th November 2009, 14:57
Would it not be possible to restrict what the BB calls from the DB when users request TPD?

For example, to avoid calling all 200k+ posts, and maybe just the last few pages?

I'm not a DB developer, and know nothing about vBulletin set-ups, but surely that's an option?

RichardCranium
16th November 2009, 14:59
If it turns out that the post count of the poster is used in some mentalist way as part of the indexing of the posts in a thread, and that has resulted in poor indexing through post counts in L-R not incrementing, I'll laugh so much I'll piss my kidneys out.

DiscoStu
16th November 2009, 15:01
Was typing my reply when you wrote that - honest :D

Invoice is in the post :D

TheFaQQer
16th November 2009, 15:10
This is, indeed, a quality suggestion.

The main downside would be that AtW will be chasing the important milestones now.

TheFaQQer
16th November 2009, 15:13
If it turns out that the post count of the poster is used in some mentalist way as part of the indexing of the posts in a thread, and that has resulted in poor indexing through post counts in L-R not incrementing, I'll laugh so much I'll piss my kidneys out.

That's a very good point.

Maybe as a "test", moderators should allow LR posts to count again and see how that helps :D

DiscoStu
16th November 2009, 15:15
That's a very good point.

Maybe as a "test", moderators should allow LR posts to count again and see how that helps :D

I concur with your suggestion. As I'll then have >5000 posts, I'd like my title to become Andyw-like :D

AtW
16th November 2009, 15:19
Yeah, you are not notified what the problem was but I am. Too many DB connections, the DB server is struggling with TPD. Night is drawing in on the thread I am afraid. The executioner is donning his hood. The congregation will be asked to deliver the last rites...

:yay:

DimPrawn
16th November 2009, 15:22
:yay:

There goes his pants.

:sick

DiscoStu
16th November 2009, 15:25
There goes his pants.

:sick

:sick

Clippy
16th November 2009, 15:42
As a suggestion, why not lock TPD for a few days to see how that affects performance.

Failing that, just ban atw!

cojak
16th November 2009, 15:45
I have a highly technical solution to this issue that will take approximately 2 minutes and completely resolve the problem:

Lock the old TPD and have sasguru start a new one.

<<Stu in "Thinking outside the box" mode>>

I'm with you on this.

With DS23 posting away I can never bother reading previous pages anyway... :D

NickFitz
16th November 2009, 16:13
As a suggestion, why not lock TPD for a few days to see how that affects performance.


:spel General

TheFaQQer
16th November 2009, 16:30
I concur with your suggestion. As I'll then have >5000 posts, I'd like my title to become Andyw-like :D

Join us in the Word Association Thread (it's not in LR, so posts count :D)

NickFitz
16th November 2009, 16:31
Join us in the Word Association Thread (it's not in LR, so posts count :D)

Just think how annoyed AtW would be if it was moved to LR :rolleyes:

TheFaQQer
16th November 2009, 16:32
I concur with your suggestion. As I'll then have >5000 posts, I'd like my title to become Andyw-like :D

In terms of custom titles, I'd like to offer up this offering from Tony in 2005 (69th post ever on CUK):


Couldn't we just mark ATW as 'single, no mates and sitting in his bedroom'. A status which will never change! :)

DS23
16th November 2009, 17:22
I'm with you on this.

With DS23 posting away I can never bother reading previous pages anyway... :D

don't blame you.

AtW
16th November 2009, 17:49
Failing that, just ban atw!

I've got plenty of other sockies :smokin

sasguru

NickFitz
17th November 2009, 02:18
OK, I've bought myself a copy of vBulletin. The idea is to set up an Apache/PHP server and a mySQL server, hopefully configured similarly to the existing forums.CUK setup, then set up one or more additional servers to replay traffic that causes the fail. This means that I can test various options for tuning the database server, hopefully finding some tweaks to the configuration that will address the issues.

If that doesn't work, I may need to figure something else out that the good people of CUK can deploy without having to worry about breakage from future upgrades to the vBulletin software.

It would be handy to have some examples of the kind of traffic that has been causing the problems: we have a very clear timeframe today, so info from the HTTP logs would be useful, as long as it's cleaned of IP addresses and anything else that could be exploited to identify users.

Of course it'll all be more complicated than that, and I can't promise a rapid (or any) solution, but maybe something can be done - well, something other than just shutting down General, which is clearly the root of the problem ;)

Luckily I have a (possibly incomplete) copy of TPD that I can start with :)

Board Game Geek
17th November 2009, 08:09
Wow, this is all so exciting !

We've got the collective geniuses of the crème de la crème IT contracting in the UK, working bravely to save a thread from extinction, before it crashes and burns and takes out the rest of the board with it.

It's like open-heart surgery, on a patient without anaesthetic.

The stakes are high, the die is cast, now the work begins.

Can they pull it off ?

Will the patient survive ?

This is better than X-Factor.

In fact, using that parallel, Admin could be Simon Cowell, and the contestants are the forum users.

Who's suggestion is going to make the grade this week, and who get's booted off ?

The tension is nearly unbearable. The crowd are going wild.

There's everything to play for, and some are going home empty-handed.

Gripping stuff indeed !

OwlHoot
17th November 2009, 08:16
As TPD doesn't seem to have caused any resource problems until now, I suggest:

1. Lock TPD (for read only)

2. Start a new thread called TPD2

3. Sorted

ctdctd
17th November 2009, 08:26
Will the patient survive ?

This is better than X-Factor.

So:-

NickFitz = Jedward
Churchill + Cailin = Stavros :D
EO = Susan Boyle

(OK, not all in the X-Factor but near enough!)

Any other suggestions?

RichardCranium
17th November 2009, 09:05
OK, I've bought myself a copy of vBulletin.:rolleyes:

:hug:

realityhack
17th November 2009, 09:38
NF, you're a star.

Admin, if/when this all gets sorted, can we move to design and IA next? :)

TheFaQQer
17th November 2009, 10:02
I think Nick should be a moderator, as a reward for his help.

Then we can watch General get deleted in an afternoon....

RichardCranium
17th November 2009, 10:42
I think Nick should be a moderator, as a reward for his help.Having been a moderator for Usenet newsgroups and mailing lists, I can assure you it is no reward.

It is not out of fear that I do not abuse the mods, it is out of pity.

realityhack
17th November 2009, 12:02
Having been a moderator for Usenet newsgroups and mailing lists, I can assure you it is no reward.

WHS with bells on. Not a job to be envied at times. And if someone really wants to be a mod, they probably shouldn't be allowed to be one, if you catch my drift.

TheFaQQer
17th November 2009, 13:07
I wonder whether the newer version of vBSEO would make any difference. Not knowing what runs on the site, I have no idea - just something else to throw into the mix.

xchaotic
17th November 2009, 13:13
it's test, please delete, sho why don't you delete it after all?

DiscoStu
17th November 2009, 13:14
it's test, please delete, sho why don't you delete it after all?

Because it must remain until the end of the universe.

HTH

TheFaQQer
17th November 2009, 13:21
Because it must remain until the end of the universe.

HTH

Sometime around 2050 when the oil runs out, then?

Sysman
17th November 2009, 14:42
Reports of use of INNODB over MyISAM - good to stop table locking, which may be the problem here, but users often end up going back to MyISAM due to performance issues from threads that I have read in the past.

I've been looking at implementing InnoDB on and off for a while now, (though have held off because I'm using a shared hosting solution and can't knobble the MySQL parameters).

Various folks have done it with Drupal, usually needing to hack it a bit to do certain things in a more InnoDB friendly way, and the current development version (V7 - the next major release) uses InnoDB as the default.

Yes, what suits Drupal won't necessarily fit VBulletin requirements, but there's some good experience out there which is probably highly relevant.

Converting your Drupal MySQL database from MyISAM to InnoDB (http://xdeb.org/node/1268) - do follow the links here

In particular, search tables are probably best left as MyISAM, and the default buffer pool sizes for InnoDB are pitifully low.

Referring back to:

Tips optimising servers for large forums (>1m posts) (http://www.vbulletin.com/forum/showthread.php?111239-The-Large-vBulletin-Forums-1-million-posts-Discussion-Thread)


I've just changed all my InnoDB tables back to MyISAM because:
a) InnoDB seemed to use more memory
b) InnoDB causes load to go up - changing back to MyISAM dropped my loads down by 90%!!!
c) InnoDB used more hard disk space
d) InnoDB is slower than MyISAM


Yes it can use more memory, but that also gives faster performance
In one case I read about, this was because it was actually doing more useful work - i.e. the bottleneck had shifted
dumping the MyISAM database by primary key apparently solves this
Way too general a statement - it depends on what it's being asked to do


MySQL Engines: MyISAM vs. InnoDB (http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB) again follow the links
MySQL InnoDB: performance gains as well as some pitfalls (http://2bits.com/articles/mysql-innodb-performance-gains-as-well-as-some-pitfalls.html) - take note of the stuff about locking, autoincrement fields and SELECT (*) COUNT (SELECT (*) MAX is better if that fits the app).
InnoDB Performance Tuning (http://tag1consulting.com/InnoDB_Performance_Tuning)

I got this little lot from Googling "drupal 7 innodb" - there were plenty more hits.