[2.0.x] Tweaks for large forums

The 2.0.x discussion forum has been locked; this will remain read-only. The 3.0.x discussion forum has been renamed phpBB Discussion.
Locked
ms2scale
Registered User
Posts: 2
Joined: Tue Apr 26, 2005 6:31 am

Post by ms2scale »

My relatively large board starts do crawl during some queries.
The processlist shows, that the slow queries hang in
"Copying to tmp tables".
I'm using mysql 3.23.49 utilizing MyIsam tables.
I already checked lanzers mysql config but start getting confused with
the mysql server variables, especially the ones regarding tmp tables.
Any clues or example setups?

Cheers,

Michael
User avatar
steve1
Registered User
Posts: 50
Joined: Mon Dec 15, 2003 3:29 am

Re: backing up your database

Post by steve1 »

lanzer wrote: We do nightly backup with mysqldump for most of our databases. A script was made to list all the tables within the database, then each table will be dumped into a backup file by the same name. (you can also just tell mysqldump to dump all tables into one big file) This happens on all our database servers. The files are then compressed into tar files and multiple copies are saved in a backup directory by another script. Every week they're copied to an off-site location. This is an inexpensive way of back up that will work for almost all sites out there.

Exception being the forum database which is simply too big to be backed up. The forum database is at 100G and the archive database is just as big. They rely on their slave servers as the backup, but we're still prone to human errors. If someone commit a delete on the forum both the master and the slave will lose all their data! 8O

We're in the middle of installing a storage area network this week which will give us plenty of space and the ability to actually take snapshots for backups.
DmcMan wrote:How do you guys with these huge databases back them up? For security, aren't you supposed to back them up daily and obviously, you can't download a 1+ gig database everyday. Do you do daily tape backups or something of the sort?

Just curious.


Hi Lanzer, as usual, very much enjoy reading your posts!
Question: I find mysqldump to be very slow for large databases/tables. I start getting "Linux is dangerously overloaded" type warnings. Also tried to do tar on the data tables, but then since the tables are live, start getting tar errors.

How do you handle this?

Thanks,
steve
ezClassifieds.com Set up a Classifieds Section in 10 minutes
Auto Forums Windows Forums Mac Forums
User avatar
Friends4U
Registered User
Posts: 256
Joined: Fri Mar 22, 2002 5:09 pm
Location: Dordrecht, The Netherlands
Contact:

Post by Friends4U »

Are this tweaks still usable for phpbb 2.0.14?
Forums4U - Http://www.Forums4U.nl - Dutch
User avatar
EverettB
Registered User
Posts: 326
Joined: Fri Aug 01, 2003 7:11 pm
Location: North America

Post by EverettB »

Friends4U wrote: Are this tweaks still usable for phpbb 2.0.14?

Yes
lanzer
Registered User
Posts: 152
Joined: Wed Oct 10, 2001 10:00 am
Contact:

Re: backing up your database

Post by lanzer »

steve1 wrote: Question: I find mysqldump to be very slow for large databases/tables. I start getting "Linux is dangerously overloaded" type warnings. Also tried to do tar on the data tables, but then since the tables are live, start getting tar errors.

How do you handle this?


Hello Steve, when it comes to making back-ups, it is true that using mysqldump does require servers with CPU power to spare. I used to run it at 3am and it'll be done in an hour, and that was on a dual Opteron machine with a RAID 0 array. Within months the process eventually took more than 2 hours to complete, and I eventually had to stop dumping forum posts table and private messages table.

At that point, I had slave database servers to offload the database and act as a backup against hardware defects, but everything was still prone to human errors. If I accidentally deleted a table then there won't be a back up.

From that point on, to truly back up our InnoDB database means to shutdown mysql on the slave server and just making a copy of the database to another hard drive. It's quite a pain to manage, and at this point we know that soon we'll need some automated software which is going to be costly.

Personally I think that the most practical way to backup is to install a big hard drive on your database server and just straight up copy your database files into that hard drive for backup. From that point on, it's just a question of whether you have the CPU power or not. Run mysqldump if there's the CPU power to handle it, or your alternative is to shutdown the board while you run mysqldump, or shutdown mysql altogether and copy your database files over.

* When using mysqldump, be sure to use the "--opt" option.

Don't have much practical solutions at hand, I'll edit this post if I remember anything.
User avatar
xkevinx
Registered User
Posts: 132
Joined: Tue Nov 05, 2002 8:45 pm
Location: California
Contact:

Post by xkevinx »

Lanzer how about something like rsync. If you running a windows based system I would recomend SecondCopy. My mom's work uses it at work and it works for there db. Or if you were looking for like a tape backup solution theres http://amanda.sourceforge.net/. That's what SF themselfs use last time I looked.

Kevin
Do you Believe?
Monkiji
Registered User
Posts: 2
Joined: Thu May 12, 2005 5:43 pm
Contact:

Post by Monkiji »

lanzer, those forums are great. kudos! I am very jealous and impressed.
Success is I, for I am Luck

Visit and register please! They're new!
www.monkisforums.tk
y0y0
Registered User
Posts: 85
Joined: Sun Apr 04, 2004 12:03 pm

Post by y0y0 »

@lanzer, do you recommend my forum any tweak modifications? It's loading very slow
In total there are 88 users online :: 9 Registered, 3 Hidden and 76 Guests


If I get more visitors then 40-50 it starts to lagg and it generates in 7 - 30 seconds. You can see my forum here

I have the Catogeries Hiarchy installed. I don't want to buy a server, so maybe a good phpBB-tweak modification could help me?
>>>>>No sig at the time<<<<<<
niekas
Registered User
Posts: 562
Joined: Sun Sep 23, 2001 7:34 am

Post by niekas »

y0y0 wrote: @lanzer, do you recommend my forum any tweak modifications? It's loading very slow
In total there are 88 users online :: 9 Registered, 3 Hidden and 76 Guests


If I get more visitors then 40-50 it starts to lagg and it generates in 7 - 30 seconds. You can see my forum here

I have the Catogeries Hiarchy installed. I don't want to buy a server, so maybe a good phpBB-tweak modification could help me?


You have some inefficient mods installed that double your SQL queries.

Queries: 23

Reg catH usually has ~10 queries for unregistered user. You should look into optimizing these extra mods and their queries.
y0y0
Registered User
Posts: 85
Joined: Sun Apr 04, 2004 12:03 pm

Post by y0y0 »

and how I can do that? help me please

and remember: I have catogeries hiarchy !
>>>>>No sig at the time<<<<<<
User avatar
EverettB
Registered User
Posts: 326
Joined: Fri Aug 01, 2003 7:11 pm
Location: North America

Post by EverettB »

Here is a help thread I read recently that helped me optimize my server a bit more:

Notes:
#2 - may not apply
#3 - MMCache is out of date
I recommend reading this install on eAccelerator instead:
(You will have to substitute in the current version, which is 0.9.3 instead of the rc2 mentioned in that thread if they don't update the original post soon)

For the php.ini settings, I preferred to use a setting of
eaccelerator.shm_size="0"
instead of
eaccelerator.shm_size="16"
0 = use system default
User avatar
EverettB
Registered User
Posts: 326
Joined: Fri Aug 01, 2003 7:11 pm
Location: North America

Post by EverettB »

y0y0 wrote: and how I can do that? help me please
and remember: I have catogeries hiarchy !

You can turn on the slow query log in mySQL and see what queries are hurting your server.

Click on the link above or do some Google searches to see how to use it.
Lady Serena
Registered User
Posts: 76
Joined: Thu May 26, 2005 12:31 am
Location: Smithsburg, MD
Contact:

Post by Lady Serena »

Hmm. I have a few ideas for a large forum of my own, and I'm definitely going to need some of these performance tweaks.

Hey, Lanzer, would you mind terribly if I implemented a few of your performance tweaks on my own site? If so, would I get the same pagination problems Gaia is facing right now?

P.S. Lanzer, the past 3 donation items were big hits, including the one released today. :3nod:

Edit: *wonders if Lanzer will actually read this*

Another edit: Is Apache disabled or not installed on sasha? All I ever get is "Connection Refused" with sasha.gaiaonline.com or when the load balancer assigns sasha to my page requests.
Last edited by Lady Serena on Sat May 28, 2005 10:35 pm, edited 1 time in total.
Varus Online :: Discover Imagination
http://www.varusonline.com/
arod-1
Registered User
Posts: 1327
Joined: Mon Sep 20, 2004 1:33 pm

Re: backing up your database

Post by arod-1 »

lanzer wrote: .........
Hello Steve, when it comes to making back-ups, it is true that using mysqldump does require servers with CPU power to spare. I used to run it at 3am and it'll be done in an hour, and that was on a dual Opteron machine with a RAID 0 array. Within months the process eventually took more than 2 hours to complete, and I eventually had to stop dumping forum posts table and private messages table.
just wonder: do you run mysqldump under "nice"? seems like the right thing to do...
lanzer
Registered User
Posts: 152
Joined: Wed Oct 10, 2001 10:00 am
Contact:

Re: backing up your database

Post by lanzer »

Hi arod-1, when I run mysqldump I don't change the nice value, where as I have the mysql daemon running as -20. If any single database takes more than an hour to back up, that just means it's probably time to seek a solution different than just running mysqldump.
arod-1 wrote: just wonder: do you run mysqldump under "nice"? seems like the right thing to do...
Locked

Return to “2.0.x Discussion”