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

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.

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

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

What tables do you guys optimize frequently? Is it just the POSTS one? Or do you run a command which optimizes all tables?

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

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.

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.

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.

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.