SQL database

[toontastic]

My site currently uses phpbb and we're still putting the finishing touches to the simply machines board before we release it to the masses. We have lately been having problems however in that the site goes down and I'm getting SQL errors. A quick word with the guys who run our server and they say it's down the load on the SQL server caused by phpbb and how it runs things. So how does the load on the mysql server compare to phpbb when a high number of users are logged in?

They believe we've simply got to many visitors to the site and they think they are going to have to upgrade the CPU and more hardware on the server but I'm hoping our change of board might help a little.

Thanks,
Phil

[[Unknown]]

I would like to think SMF would be more efficient, but no real benchmarks have been made that I can say are reliable.

How large is your forum in terms of:
  - number of posts per day.
  - total number of posts.
  - maximum people on line each day.

And what sort of server/plan are you running on (dedicated, shared, CPU, ram?)

When using InnoDB, things are pretty good.  This forum here is on a dedicated Celeron 1700Mhz with 512 megs of ram, but it also hosts yabbse.org.  It does pretty well.

-[Unknown]

[toontastic]

How large is your forum in terms of:
  - number of posts per day.
  - total number of posts.
  - maximum people on line each day.

And what sort of server/plan are you running on (dedicated, shared, CPU, ram?)

We get aprox 1000 posts a day and have a total of 147738 posts most people on a one time at the minute is around the 250 mark and we have 1311 users so i wouldn't say we really have a massive number. However we are hoping that is going to continue to increase and the site does get extra busy on a match day (it's a football website) I'm not sure what the current spec is of the server I'll have to go and find that one out and post back again.

Thanks for the reply,

Cheers,
Phil

[toontastic]

When using InnoDB, things are pretty good.
-[Unknown]

Just noticed this, what is it ?

Cheers,
Phil

[[Unknown]]

1000 posts a day is more than we get here, but on a dedicated server you should be more than fine with that and the number of users you will have online, even if things pick up.

As for the server, I'm most interested in the amount of ram and whether it is dedicated, VPS, etc.  The speed and CPU type are good to know, as well.

You will want to be using MySQL 4.0.x or 4.1.x, with PHP 4.3.x or 5.0.x.  I strongly recommend having a PHP accelerator, like eAccelerator, installed.

InnoDB is a transactional storage engine available in MySQL.  It's enabled by default in 4.0.x and higher.  Basically, this type of table reads slower, but writes faster.  This is oversimplifying it, but it's a good example.

I've posted a list of which tables in the database should be InnoDB (we use a mix here too) for theoretical best performance.  I invite anyone to give me hard numbers proving otherwise, though.

-[Unknown]

[toontastic]

Got the details from my host the server is a dedicated server with the following:

Our CPU : AMD Duron 1GHz
RAM: 512 megs

however it is hosted as a shared server along with about a dozen websites. I'll have to find out a bit more about eAccelerator and the versions of MySQL and PHP.

Thanks again,

Phil

[[Unknown]]

We're on an Intel Celeron 1700Mhz here.  You'll really want that eAccelerator, but you should be fine with that CPU.  Still, I'd watch the load averages - with phpBB or SMF both.

If the other sites aren't highly trafficked, or if they don't use PHP/MySQL, it shouldn't be a problem at all.

-[Unknown]

[toontastic]

I'm still waiting for them to get back about whether they have eAccelator installed and what versions on MySQL and PHP. I had to ask though after the problems we've had with phpbb so hopefully we won't have these problems with this board. I'm going to install the fuse box mod just in case though and I think they are going to upgrade the server a little.