A solution to keeping database size down?

Anything that doesn't fit anywhere else, and is NON CH related - chat about the weather, or anything else that takes your fancy.

Moderator: Moderators

matthew
GE (Great Erasmus)
Posts: 122
Joined: Wed Jan 12, 2005 11:29 pm
Real Name: Matthew Powell
Location: Toronto, Canada

Post by matthew »

jtaylor wrote:Well, I spotted an issue with the phpbb_sessions table this evening.

It had 55Meg of "overhead".
Tried an overhead, tried a repair - mysqladmin wouldn't let me do it, "The handler for the table doesn't support optimize" and same message for repair.

Copied the table, renamed old one, and overhead has gone.

Any techies out there know the implications of this??

J
It's nothing to do with Saddam Hussein. I know that much.

My experience of phpBB is limited, to say the least. But I have Google, a little guesswork, and a deceptive air of authority. This may not be the right answer, but it might give you some ideas.

You're probably using an in-memory table for sessions rather than one stored on disk. That improves performance, but session data is lost when MySQL is restarted. Depending on how your hosting provider does accounting, it may also prevent session data from counting against your storage allowance.

Session data is information about who's logged in at any one time. If it's lost, it just means that people have to log in again. Since people can log in and out many times a day, it changes a lot more often than the rest of the database, but doesn't need to be preserved long-term.

As you've discovered, you can't optimize a table that's stored in memory. I expect the idea is you don't need to. It's possible you've found a bug in MySQL.

Restart the MySQL server, drop and recreate the table, or upgrade MySQL.

It might be the reported memory use is wrong, though, and this 'overhead' is a phantom. See, for example, bug #18610. If that's the problem, you needn't do anything, unless you care about accurate statistics.
User avatar
Mrs C.
Button Grecian
Posts: 2300
Joined: Wed Feb 16, 2005 2:22 pm
Real Name: Janet Chandler
Location: C.H.

Post by Mrs C. »

See - I was right! ( I think!)
The best way to forget your troubles is to wear tight shoes.
User avatar
J.R.
Forum Moderator
Posts: 15835
Joined: Wed Mar 09, 2005 4:53 pm
Real Name: John Rutley
Location: Dorking, Surrey

Post by J.R. »

I'm even more lost now !

Incidently, Dave, I suggest you leave Amy's thrust out of this.

DIVERT ALERT !
John Rutley. Prep B & Coleridge B. 1958-1963.
Scone Lover
Grecian
Posts: 897
Joined: Sun Oct 15, 2006 2:45 pm

Post by Scone Lover »

Have you posted on the phpBB forum for site owners Julian? They sometimes come up with clever answers to knotty questions
User avatar
blondie95
Button Grecian
Posts: 2590
Joined: Tue Jun 06, 2006 4:41 pm
Real Name: Amy Leadbeater
Location: Kent
Contact:

Post by blondie95 »

well im going to try and thrust in the right sense now! That post will have made sense to all computer understanderers! :)
User avatar
DavebytheSea
Forum Moderator
Posts: 2034
Joined: Tue Nov 23, 2004 10:33 am
Real Name: David Eastburn
Location: Nr Falmouth, Cornwall

Post by DavebytheSea »

blondie95 wrote:well im going to try and thrust in the right sense now! That post will have made sense to all computer understanderers! :)
I thought I was a computer understanderer until I read that bit about the Iraqi.
David Eastburn (Prep B and Mid A 1947-55)
User avatar
jtaylor
Forum Administrator
Posts: 1880
Joined: Sat Jun 05, 2004 12:32 am
Real Name: Julian Taylor
Location: Wantage, OXON
Contact:

Post by jtaylor »

Scone Lover wrote:Have you posted on the phpBB forum for site owners Julian? They sometimes come up with clever answers to knotty questions
Haven't posted on there, but do use it quite a bit for answers/fixes.

The more I've read, the more it's clear that our little forum is tiny compared to some, both in storage space and in number of users.
Hence, most of the suggested speed improvements just aren't relevent for such a small database.
I'm more thinking that the shared hosting is the issue, and that our SQL server is probably getting hit hard on some other sites, which is affecting ours.

One more thing I'm going to try this evening is to move the database over to the second SQL database we have with the hosting - this may be on a different server, which isn't getting hit so hard.

This SHOULD be quite quick (famous last words!)

Hence, forum will be disabled for a while this evening.

J
Julian Taylor-Gadd
Leigh Hunt 1985-1992
Image
Founder of The Unofficial CH Forum
https://www.grovegeeks.co.uk - IT Support and website design for home, small businesses and charities.
User avatar
J.R.
Forum Moderator
Posts: 15835
Joined: Wed Mar 09, 2005 4:53 pm
Real Name: John Rutley
Location: Dorking, Surrey

Post by J.R. »

It must have worked, then !
John Rutley. Prep B & Coleridge B. 1958-1963.
Post Reply