Site Links
Home
Features
Documentation
Pricing & Order
Members Area
Support Options
Who's Online
4 registered (blaaskaak, Djuma, Gizmo, 1 invisible), 22 Guests and 20 Spiders online.
Key: Admin, Global Mod, Mod
Featured Member
Registered: 07/07/05
Posts: 62
Top Posters (30 Days)
Ruben Rocha 179
Gizmo 112
Rick 109
Thelockman 80
driv 43
AllenAyres 35
ntdoc 28
Ian 24
Sirdude 23
ScriptKeeper 20
Latest Photos
My Home System
test photo gallery
Bernese Mountain Dogs
My Daimler
Dorado and shark
Page 1 of 3 1 2 3 >
Topic Options
Rate This Topic
#179611 - 02/27/07 08:35 PM [NOTABUG] Huge Cached Permission Table Overhead
24hourcampfire Offline
journeyman

Registered: 11/02/06
Posts: 56
Don't know if this is a bug or not, but since 7.1, my Cached Permission table has huge overhead every day. After 24 hours, it had 725.8 KiB of overhead. The table itself is less than 4.1 MiB.

Is this normal? None of my other tables generate nearly as much overhead.

Rick
_________________________
Rick Bin
24hourcampfire.com

http://www.24hourcampfire.com/ubbthreads/ubbthreads.php

Top
#179628 - 02/28/07 08:04 AM Re: Huge Cached Permission Table Overhead [Re: 24hourcampfire]
Rick Administrator Offline

*****

Registered: 06/04/06
Posts: 7898
Loc: Aberdeen, WA
Editing my post. I thought we converted this to a memory table, but I was wrong. It will be a bit large depending on how many users you have online at one time, as it holds their permission set to each forum in this table.


Edited by Rick (02/28/07 11:01 AM)
_________________________
UBB.threads™ Developer
My Personal Website · StogieSmokers.com

Top
#179646 - 02/28/07 11:47 AM Re: Huge Cached Permission Table Overhead [Re: Rick]
24hourcampfire Offline
journeyman

Registered: 11/02/06
Posts: 56
Rick:

It is orders of magnitude bigger. Here's this morning's info:



Table Action Records Type Collation Size Overhead
ubbt_ADDRESS_BOOK 4,603 MyISAM latin1_swedish_ci 87.5 KiB -
ubbt_ADMIN_SEARCHES 2 MyISAM latin1_swedish_ci 1.3 KiB 68 B
ubbt_ANNOUNCEMENTS 28 MyISAM latin1_swedish_ci 2.5 KiB -
ubbt_BANNED_EMAILS 29 MyISAM latin1_swedish_ci 1.7 KiB -
ubbt_BANNED_HOSTS 89 MyISAM latin1_swedish_ci 2.7 KiB -
ubbt_BANNED_USERS 92 MyISAM latin1_swedish_ci 3.8 KiB -
ubbt_CACHE 2 MyISAM latin1_swedish_ci 1.1 KiB -
ubbt_CACHED_PERMISSIONS 602 MyISAM latin1_swedish_ci 3.4 MiB 844.0 KiB
ubbt_CALENDAR_EVENTS 321 MyISAM latin1_swedish_ci 44.4 KiB -
ubbt_CAPTCHA 0 MyISAM latin1_swedish_ci 1.0 KiB -
ubbt_CATEGORIES 9 MyISAM latin1_swedish_ci 4.4 KiB -
ubbt_CENSOR_LIST 39 MyISAM latin1_swedish_ci 1.8 KiB -
ubbt_DISPLAY_NAMES 0 MyISAM latin1_swedish_ci 2.0 KiB 24 B
ubbt_FILES 7,103 MyISAM latin1_swedish_ci 586.2 KiB -
ubbt_FORUMS 64 MyISAM latin1_swedish_ci 16.1 KiB 40 B
ubbt_FORUM_LAST_VISIT 28,981 MyISAM latin1_swedish_ci 774.9 KiB -
ubbt_FORUM_PERMISSIONS 442 MyISAM latin1_swedish_ci 16.6 KiB -
ubbt_GRAEMLINS 18 MyISAM latin1_swedish_ci 3.7 KiB -
ubbt_GROUPS 7 MyISAM latin1_swedish_ci 2.1 KiB -
ubbt_IMPORT_MAP 1,230,151 MyISAM latin1_swedish_ci 64.8 MiB -
ubbt_LANGUAGES 1 MyISAM latin1_swedish_ci 2.0 KiB -
ubbt_MAILER 0 MyISAM latin1_swedish_ci 1.0 KiB -
ubbt_MEMBER_SEARCHES 1 MyISAM latin1_swedish_ci 2.1 KiB -
ubbt_MODERATORS 190 MyISAM latin1_swedish_ci 8.7 KiB -
ubbt_MODERATOR_NOTIFICATIONS 13 MyISAM latin1_swedish_ci 2.1 KiB -
ubbt_MODERATOR_PERMISSIONS 3 MyISAM latin1_swedish_ci 2.0 KiB -
ubbt_ONLINE 509 HEAP latin1_swedish_ci 6.3 MiB 184.8 KiB
ubbt_POINTER_DELETE 0 MyISAM latin1_swedish_ci 1.0 KiB -
ubbt_POLL_DATA 26 MyISAM latin1_swedish_ci 6.9 KiB -
ubbt_POLL_OPTIONS 171 MyISAM latin1_swedish_ci 11.6 KiB -
ubbt_POLL_VOTES 2,874 MyISAM latin1_swedish_ci 188.8 KiB -
ubbt_PORTAL_BOXES 24 MyISAM latin1_swedish_ci 2.9 KiB 160 B
ubbt_POSTS 1,279,422 MyISAM latin1_swedish_ci 1.5 GiB -
ubbt_PRIVATE_MESSAGE_POSTS 75,062 MyISAM latin1_swedish_ci 61.4 MiB -
ubbt_PRIVATE_MESSAGE_TOPICS 63,539 MyISAM latin1_swedish_ci 3.5 MiB 352 B
ubbt_PRIVATE_MESSAGE_USERS 92,981 MyISAM latin1_swedish_ci 3.3 MiB 1.2 KiB
ubbt_RATINGS 0 MyISAM latin1_swedish_ci 1.0 KiB -
ubbt_REGISTRATION_FIELDS 26 MyISAM latin1_swedish_ci 3.6 KiB -
ubbt_RESERVED_NAMES 1 MyISAM latin1_swedish_ci 1.0 KiB -
ubbt_RSS_FEEDS 62 MyISAM latin1_swedish_ci 6.9 KiB -
ubbt_SAVED_QUERIES 0 MyISAM latin1_swedish_ci 1.0 KiB -
ubbt_SEARCH_AGENTS 1 MyISAM latin1_swedish_ci 1.2 KiB -
ubbt_SEARCH_RESULTS 293 MyISAM latin1_swedish_ci 821.6 KiB 33.0 KiB
ubbt_SHOUT_BOX 30 MyISAM latin1_swedish_ci 5.0 KiB -
ubbt_STYLES 3 MyISAM latin1_swedish_ci 29.7 KiB -
ubbt_TOPICS 115,072 MyISAM latin1_swedish_ci 18.7 MiB -
ubbt_TOPIC_VIEWS 3 HEAP latin1_swedish_ci 6.4 MiB -
ubbt_USERS 14,008 MyISAM latin1_swedish_ci 3.2 MiB -
ubbt_USER_DATA 14,016 MyISAM latin1_swedish_ci 605.8 KiB 20 B
ubbt_USER_GROUPS 14,062 MyISAM latin1_swedish_ci 294.6 KiB -
ubbt_USER_NOTES 8 MyISAM latin1_swedish_ci 2.2 KiB -
ubbt_USER_PROFILE 14,016 MyISAM latin1_swedish_ci 3.2 MiB -
ubbt_USER_TITLES 7 MyISAM latin1_swedish_ci 2.2 KiB -
ubbt_VERSION 1 MyISAM latin1_swedish_ci 1.0 KiB -
ubbt_WATCH_LISTS 1,182 MyISAM latin1_swedish_ci 55.1 KiB -
55 table(s) Sum 2,960,189 MyISAM latin1_swedish_ci 1.7 GiB 1.0 MiB


If this is normal, I'll be optimizing overhead daily, I think. I just want to make sure my numbers jive with what you expect to see.

We max out at about 600-800 users online at one time, with 5000 or so uniques a day.

As always, thanks for the help.

Rick Bin
_________________________
Rick Bin
24hourcampfire.com

http://www.24hourcampfire.com/ubbthreads/ubbthreads.php

Top
#179648 - 02/28/07 12:37 PM Re: Huge Cached Permission Table Overhead [Re: 24hourcampfire]
AllenAyres Moderator Offline

****

Registered: 12/29/03
Posts: 1639
Loc: Texas
Those aren't unusually large numbers, your total overhead for your tables is 1 megabyte while your tables are 1.7 gigabytes - about 0.006%.

Cached permissions are 3.4 megabytes with 844.0 kilobytes as overhead, larger than average but still not huge.

I optimize every couple weeks right before I make a backup \:\)
_________________________
- Allen
- ThreadsDev | PraiseCafe

Top
#179671 - 02/28/07 02:23 PM Re: Huge Cached Permission Table Overhead [Re: AllenAyres]
Conrad Offline
enthusiast

Registered: 08/04/04
Posts: 361
What tables do you guys optimize frequently? Is it just the POSTS one? Or do you run a command which optimizes all tables?

Top
#179672 - 02/28/07 02:24 PM Re: Huge Cached Permission Table Overhead [Re: AllenAyres]
24hourcampfire Offline
journeyman

Registered: 11/02/06
Posts: 56
Allen:

Roger that.

Just to make sure I have communicated clearly:

The 725.8 KiB of overhead I reported in my first post, at 8:35 pm yesterday, was optimized immediately at that time.

This morning, I had a brand new 844 KiB that generated overnight.

Two weeks??? Holy smokes!

I will continue to optimize daily, unless there is a good reason not to.

Thanks for the advice!!

Rick
_________________________
Rick Bin
24hourcampfire.com

http://www.24hourcampfire.com/ubbthreads/ubbthreads.php

Top
#179702 - 02/28/07 07:06 PM Re: Huge Cached Permission Table Overhead [Re: 24hourcampfire]
Gizmo Moderator Online   sleepy

***

Registered: 06/04/06
Posts: 12007
Loc: Portland, OR; USA
I optimize monthly, just before my critical backups; additionally, I optimize all tables in the database.

There is however a reason to not do any tasks daily, as it locks the tables until it has been completed; which could affect users utilizing your forum at that time and corruption could start occouring should a lot of users be trying to post in that timeframe.
_________________________
UGN Security, Elite Web Gamers & VNC Web Design Owner
Longtime UBB Supporter, UBB7 Beta Tester & Resident Post-A-Holic

Top
#179793 - 03/01/07 03:50 PM Re: Huge Cached Permission Table Overhead [Re: Gizmo]
David Dreezer Offline
Pooh-Bah
*****

Registered: 07/21/06
Posts: 1792
844 kilobytes of overhead in 2 weeks? No big deal. That means 844 kilobytes of data was deleted out of that table in 2 weeks. That ain't a whole hell of a lot. And your MySQL sure as heck isn't running slower for it. I think you're really going overboard with worry on this.

People are going to be deleting private messages or deleting or editing posts all day every day. It's going to make for an amount of overhead/dead space. Don't let yourself get into a position where you're freaking out over a little overhead every time someone deletes a PM. Your board doesn't even notice the difference.

Spend the time enjoying your board instead. \:\)
_________________________
What do you mean "You're the bomb, run away?"

Top
#179797 - 03/01/07 06:35 PM Re: Huge Cached Permission Table Overhead [Re: David Dreezer]
Mark S Offline
Carpal Tunnel
***

Registered: 07/04/06
Posts: 4044
Loc: Liverpool : England : UK
In English, what is overhead?

I rememeber i tried to look up what it actually was
but ended up on double geeky sites.

I see it when i use phpmyadmin, so i know
its to do mysql but beyond that i havnt a clue.

Thanks in advance Mark.
_________________________
Version v7.2.2 smile smile < Threads satisfaction status
People who inspire me Rick Gizmo Ian David jgeoff ntdoc
To answer the question you must first give a question.

Top
#179808 - 03/01/07 07:29 PM Re: Huge Cached Permission Table Overhead [Re: Mark S]
Gizmo Moderator Online   sleepy

***

Registered: 06/04/06
Posts: 12007
Loc: Portland, OR; USA
For speed, MySQL doesn't rebuild the tables every time that a minor change (editing posts, deleting posts, pm's, etc); on large boards it would grind to a hault as an optimize would lock the table for the maintenance option to complete.

The "overhead" is simply an indication of how much "dead space" is in the database; an optimization once a month (or whatever your backup schedule is) should be well sufficient to rebuild the tables to clear out the overhead.
_________________________
UGN Security, Elite Web Gamers & VNC Web Design Owner
Longtime UBB Supporter, UBB7 Beta Tester & Resident Post-A-Holic

Top
Page 1 of 3 1 2 3 >


Shout Box

Today's Birthdays
brushie, cass, Monte G., twebman, White Gold Wielder
Recent Topics
Shrinking graemlins
by Djuma
Today at 05:48 PM
2 problems I'm seeing now.
by DougMM
Today at 04:39 PM
Which CSS Style Controls the bottom lines
by David DelMonte
Today at 01:51 PM
UBBThreads vs. vBulletin -- Defections & Why So Tough to Compare?
by WebMagic
Today at 12:28 PM
Font and column shifts occuring
by Bill B
Yesterday at 06:21 PM
Forum Stats
4261 Members
33 Forums
30532 Topics
154962 Posts

Max Online: 978 @ 06/24/07 08:19 PM