Site Links
Home
Features
Documentation
Pricing & Order
Members Area
Support Options
UBBDev.com
UBBWiki.com
Who's Online Now
1 registered members (Yarpâ„¢), 63 guests, and 192 spiders.
Key: Admin, Global Mod, Mod
Member Spotlight
whk
whk
Portsmouth, RI
Posts: 91
Joined: January 2009
Show All Member Profiles 
Top Posters(30 Days)
Gizmo 17
SteveS 10
isaac 9
Ruben 6
Morgan 5
jorb 4
Latest Photos
Test
Testing to drag photos
Comfortable Cats
Test
BSA photos
Previous Thread
Next Thread
Print Thread
[NOTABUG] Huge Cached Permission Table Overhead #179611
02/27/07 11:35 PM
02/27/07 11:35 PM
2
24hourcampfire  Offline OP
journeyman
Joined: Nov 2006
Posts: 96
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
Express Hosting
Express Hosting "We are the official hosting company of UBB.threads. Ask us about our free migration services to migrate your UBB.threads installation."
Re: Huge Cached Permission Table Overhead [Re: 24hourcampfire] #179628
02/28/07 11:04 AM
02/28/07 11:04 AM
R
Rick  Offline
Former Developer
Joined: Jun 2006
Posts: 10,177
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.

Last edited by Rick; 02/28/07 02:01 PM.
Re: Huge Cached Permission Table Overhead [Re: Rick] #179646
02/28/07 02:47 PM
02/28/07 02:47 PM
2
24hourcampfire  Offline OP
journeyman
Joined: Nov 2006
Posts: 96
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
Re: Huge Cached Permission Table Overhead [Re: 24hourcampfire] #179648
02/28/07 03:37 PM
02/28/07 03:37 PM
AllenAyres  Offline
Pooh-Bah
Joined: Dec 2003
Posts: 2,046
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 smile


- Allen
- ThreadsDev | PraiseCafe
Re: Huge Cached Permission Table Overhead [Re: AllenAyres] #179671
02/28/07 05:23 PM
02/28/07 05:23 PM
Conrad  Offline
addict
Joined: Aug 2004
Posts: 467
What tables do you guys optimize frequently? Is it just the POSTS one? Or do you run a command which optimizes all tables?

Re: Huge Cached Permission Table Overhead [Re: AllenAyres] #179672
02/28/07 05:24 PM
02/28/07 05:24 PM
2
24hourcampfire  Offline OP
journeyman
Joined: Nov 2006
Posts: 96
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
Re: Huge Cached Permission Table Overhead [Re: 24hourcampfire] #179702
02/28/07 10:06 PM
02/28/07 10:06 PM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 17,009
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.


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: Huge Cached Permission Table Overhead [Re: Gizmo] #179793
03/01/07 06:50 PM
03/01/07 06:50 PM
David Dreezer  Offline

Pooh-Bah
Joined: Jul 2006
Posts: 2,199
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. smile



This thread for sale. Click here! [Linked Image]
Re: Huge Cached Permission Table Overhead [Re: David Dreezer] #179797
03/01/07 09:35 PM
03/01/07 09:35 PM
Mark S  Offline
Joined: Jul 2006
Posts: 4,722
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.


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Re: Huge Cached Permission Table Overhead [Re: Mark S] #179808
03/01/07 10:29 PM
03/01/07 10:29 PM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 17,009
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.


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: Huge Cached Permission Table Overhead [Re: Gizmo] #179846
03/02/07 04:16 AM
03/02/07 04:16 AM
Conrad  Offline
addict
Joined: Aug 2004
Posts: 467
Originally Posted by Gizmo
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.


Gizmo, how do you optimize all tables in a database? Is there a special command for this? Please tell me you don't go through all the tables one by one with a separate command for each... smile

Also, why would there be risk of any database corruption if you can lock the board down before optimizing?

Re: Huge Cached Permission Table Overhead [Re: Conrad] #179849
03/02/07 05:52 AM
03/02/07 05:52 AM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 17,009
Portland, OR; USA
It's all done automagially! lol... Or shell scripting, choose your poison...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: Huge Cached Permission Table Overhead [Re: Gizmo] #179854
03/02/07 06:15 AM
03/02/07 06:15 AM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 17,009
Portland, OR; USA
for me it's as easy as:
Code
[root@vnc ~]# ./optimize.sh
Table   Op      Msg_type        Msg_text
ubbage.ubb7_ADDRESS_BOOK        optimize        status  Table is already up to date
ubbage.ubb7_ADMIN_SEARCHES      optimize        status  Table is already up to date
ubbage.ubb7_ANNOUNCEMENTS       optimize        status  Table is already up to date
ubbage.ubb7_BANNED_EMAILS       optimize        status  Table is already up to date
ubbage.ubb7_BANNED_HOSTS        optimize        status  Table is already up to date
ubbage.ubb7_BANNED_USERS        optimize        status  Table is already up to date
ubbage.ubb7_CACHE       optimize        status  Table is already up to date
ubbage.ubb7_CACHED_PERMISSIONS  optimize        status  Table is already up to date
ubbage.ubb7_CALENDAR_EVENTS     optimize        status  Table is already up to date
ubbage.ubb7_CAPTCHA     optimize        status  Table is already up to date
ubbage.ubb7_CATEGORIES  optimize        status  Table is already up to date
ubbage.ubb7_CENSOR_LIST optimize        status  Table is already up to date
ubbage.ubb7_DISPLAY_NAMES       optimize        status  Table is already up to date
ubbage.ubb7_fc_bans     optimize        status  Table is already up to date
ubbage.ubb7_fc_config   optimize        status  Table is already up to date
ubbage.ubb7_fc_config_chats     optimize        status  Table is already up to date
ubbage.ubb7_fc_config_instances optimize        status  Table is already up to date
ubbage.ubb7_fc_config_values    optimize        status  Table is already up to date
ubbage.ubb7_fc_connections      optimize        status  Table is already up to date
ubbage.ubb7_fc_ignors   optimize        status  Table is already up to date
ubbage.ubb7_fc_messages optimize        status  Table is already up to date
ubbage.ubb7_fc_rooms    optimize        status  Table is already up to date
ubbage.ubb7_FILES       optimize        status  Table is already up to date
ubbage.ubb7_FORUMS      optimize        status  Table is already up to date
ubbage.ubb7_FORUM_LAST_VISIT    optimize        status  Table is already up to date
ubbage.ubb7_FORUM_PERMISSIONS   optimize        status  Table is already up to date
ubbage.ubb7_GRAEMLINS   optimize        status  Table is already up to date
ubbage.ubb7_GROUPS      optimize        status  Table is already up to date
ubbage.ubb7_IMPORT_MAP  optimize        status  Table is already up to date
ubbage.ubb7_LANGUAGES   optimize        status  Table is already up to date
ubbage.ubb7_MAILER      optimize        status  Table is already up to date
ubbage.ubb7_MEMBER_SEARCHES     optimize        status  Table is already up to date
ubbage.ubb7_MODERATORS  optimize        status  Table is already up to date
ubbage.ubb7_MODERATOR_NOTIFICATIONS     optimize        status  Table is already up to date
ubbage.ubb7_MODERATOR_PERMISSIONS       optimize        status  Table is already up to date
ubbage.ubb7_ONLINE      optimize        note    The storage engine for the table doesn't support optimize
ubbage.ubb7_POINTER_DELETE      optimize        status  Table is already up to date
ubbage.ubb7_POLL_DATA   optimize        status  Table is already up to date
ubbage.ubb7_POLL_OPTIONS        optimize        status  Table is already up to date
ubbage.ubb7_POLL_VOTES  optimize        status  Table is already up to date
ubbage.ubb7_PORTAL_BOXES        optimize        status  Table is already up to date
ubbage.ubb7_POSTS       optimize        status  Table is already up to date
ubbage.ubb7_PRIVATE_MESSAGE_POSTS       optimize        status  Table is already up to date
ubbage.ubb7_PRIVATE_MESSAGE_TOPICS      optimize        status  Table is already up to date
ubbage.ubb7_PRIVATE_MESSAGE_USERS       optimize        status  Table is already up to date
ubbage.ubb7_RATINGS     optimize        status  Table is already up to date
ubbage.ubb7_REGISTRATION_FIELDS optimize        status  Table is already up to date
ubbage.ubb7_RESERVED_NAMES      optimize        status  Table is already up to date
ubbage.ubb7_RSS_FEEDS   optimize        status  Table is already up to date
ubbage.ubb7_SAVED_QUERIES       optimize        status  Table is already up to date
ubbage.ubb7_SEARCH_AGENTS       optimize        status  Table is already up to date
ubbage.ubb7_SEARCH_RESULTS      optimize        status  Table is already up to date
ubbage.ubb7_SHOUT_BOX   optimize        status  Table is already up to date
ubbage.ubb7_STYLES      optimize        status  Table is already up to date
ubbage.ubb7_TOPICS      optimize        status  Table is already up to date
ubbage.ubb7_TOPIC_VIEWS optimize        note    The storage engine for the table doesn't support optimize
ubbage.ubb7_USERS       optimize        status  Table is already up to date
ubbage.ubb7_USER_DATA   optimize        status  Table is already up to date
ubbage.ubb7_USER_GROUPS optimize        status  Table is already up to date
ubbage.ubb7_USER_NOTES  optimize        status  Table is already up to date
ubbage.ubb7_USER_PROFILE        optimize        status  Table is already up to date
ubbage.ubb7_USER_TITLES optimize        status  Table is already up to date
ubbage.ubb7_VERSION     optimize        status  Table is already up to date
ubbage.ubb7_WATCH_LISTS optimize        status  Table is already up to date


mmmm, yummy homebrew


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: Huge Cached Permission Table Overhead [Re: Gizmo] #179858
03/02/07 06:20 AM
03/02/07 06:20 AM
Mark S  Offline
Joined: Jul 2006
Posts: 4,722
Liverpool : England : UK
phpmyadmin V 2.8.2.4

Go to your Database
Select >> Structure
Scroll to the bottom
Check tables having overhead (Click on this - it does nothing until you change the default.)
Default = : With Selected

Just change the default too "Optermize Table"
And it does it all.

NOTE : I'm not in a position to say if thats the right way
or the wrong way, but ive just cleared a load of overhead.
Using the above method. And as Gizmo points out would be
a good idea before a back up too.

Cheers Gizmo for the expination, makes sense now wink

Last edited by Mark S; 03/02/07 06:25 AM. Reason: added click on

BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Re: Huge Cached Permission Table Overhead [Re: Mark S] #179859
03/02/07 06:22 AM
03/02/07 06:22 AM
Mark S  Offline
Joined: Jul 2006
Posts: 4,722
Liverpool : England : UK
well i have a long way to go before i can write
a script to do that.


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Re: Huge Cached Permission Table Overhead [Re: Mark S] #179861
03/02/07 06:29 AM
03/02/07 06:29 AM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 17,009
Portland, OR; USA
Mark, that's all fine and dandy, however optimizing your databases through a php script is highly not advised if you have large forums; you're going to lock the tables for the optimization and any users trying to do anything are going to be in a fickle pickle...

Not to mention the PHP Maximum Execution time will eventually be hit if it takes long enough... Not to mention, you can't automate it thought phpmyadmin wink...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: Huge Cached Permission Table Overhead [Re: Gizmo] #179862
03/02/07 06:33 AM
03/02/07 06:33 AM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 17,009
Portland, OR; USA
BTW, heres a small chart to show the differance of using a script and using the command line.

Using a script:
1. Your browser, connects to the webserver.
2. The webserver connects to the MySQL server.

The problem here is that any requests to MySQL have to go through the webserver, and the webserver has a "maximum execution time" variable, once you go over this threshold, the server times out your request.

When the server is preforming critical tasks like optimizing or repairing tables, it locks those tables so that no new data can be written to them to avoid loss of data or possible curruption.

Using MySQL via command line:
You access MySQL directly, there is no maximum execution timeframe, as the webserver is not involved. As no webserver is involved, there will be no additional resource usage through the webserver.


Please note though, that I would highly recommend that you optimize your database before backing it up; theres no sense in backing something up when it could be much smaller and "optimized". :nod: lol


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: Huge Cached Permission Table Overhead [Re: Gizmo] #179863
03/02/07 06:44 AM
03/02/07 06:44 AM
Mark S  Offline
Joined: Jul 2006
Posts: 4,722
Liverpool : England : UK
Its easy for me to click on a button smile

I wouldnt know what to type in for a shell command.


/> : Gizmo optermize my tables please

lol


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Re: Huge Cached Permission Table Overhead [Re: Mark S] #179864
03/02/07 06:49 AM
03/02/07 06:49 AM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 17,009
Portland, OR; USA
Mark just loves how i do things :nod: I think he should hire me as his server tech lol


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: Huge Cached Permission Table Overhead [Re: Gizmo] #179865
03/02/07 06:54 AM
03/02/07 06:54 AM
Mark S  Offline
Joined: Jul 2006
Posts: 4,722
Liverpool : England : UK
Name yu price : in corn flakes lol...


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Re: Huge Cached Permission Table Overhead [Re: Mark S] #179866
03/02/07 07:11 AM
03/02/07 07:11 AM
Conrad  Offline
addict
Joined: Aug 2004
Posts: 467
So to do it via the root command line I just need to type: ./optimize.sh

No parameters to set? Or is ".sh" one of them?

How can I make sure that it just optimizes the ubb database if I have others on the server that I don't want to optimize simultaneously?

Re: Huge Cached Permission Table Overhead [Re: Conrad] #179868
03/02/07 07:21 AM
03/02/07 07:21 AM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 17,009
Portland, OR; USA
You'd need a shell script named optimize.sh; I coded mine from scratch; such scripts don't exist as any standard; you'd have to customize the hell out of an input string to MySQL lol.

On mine, i specify what tables i want optimized, and in what databases; then have it execute it at the beginning of my backup batch.


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: Huge Cached Permission Table Overhead [Re: Mark S] #179869
03/02/07 07:21 AM
03/02/07 07:21 AM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 17,009
Portland, OR; USA
Originally Posted by Mark S
Name yu price : in corn flakes lol...
can I have the one that looks like jefferson?


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: Huge Cached Permission Table Overhead [Re: Conrad] #179870
03/02/07 07:22 AM
03/02/07 07:22 AM
Mark S  Offline
Joined: Jul 2006
Posts: 4,722
Liverpool : England : UK
optermize.sh

is more than likely a script file,
with the commands and refrences to optermize.

I would like to know shell commands.
or where to look to find out.

But i dont frown
Hence click on a button, as cave man like it is.


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Re: Huge Cached Permission Table Overhead [Re: Mark S] #179874
03/02/07 08:32 AM
03/02/07 08:32 AM
Conrad  Offline
addict
Joined: Aug 2004
Posts: 467
Is there a regular command that I can use to optimize a database of my choice?

Re: Huge Cached Permission Table Overhead [Re: Conrad] #179892
03/02/07 06:24 PM
03/02/07 06:24 PM
David Dreezer  Offline

Pooh-Bah
Joined: Jul 2006
Posts: 2,199
you can use the mysql shell to optimize a table or tables, not a database, and there is no bash command for it, if "regular command" means a bash shell.


This thread for sale. Click here! [Linked Image]
Re: Huge Cached Permission Table Overhead [Re: David Dreezer] #179895
03/02/07 06:38 PM
03/02/07 06:38 PM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 17,009
Portland, OR; USA
Through custom scripting you could create a script to do it, however this is over the head of 90% of users who run websites.

Generally it's easier to stick to using the MySQL command line for running commands through your server; this would be as easy as:
1. Login to the Server via SSH or Telnet (I very much hope that the later is disabled on your machine for security).
2. Login to the MySQL command line:
Code
mysql -hlocalhost -uusername -p

This should first ask you for your password, and then drop you to the MySQL command line.
3. Issue the command to use the table of your choice:
Code
use my_database;

4. Issue the command of your choice; for this example, optimizing your UBB7 tables:
Code
OPTIMIZE TABLE `ubb7_ADDRESS_BOOK` , `ubb7_ADMIN_SEARCHES` , `ubb7_ANNOUNCEMENTS` , `ubb7_BANNED_EMAILS` , `ubb7_BANNED_HOSTS` , `ubb7_BANNED_USERS` , `ubb7_CACHE` , `ubb7_CACHED_PERMISSIONS` , `ubb7_CALENDAR_EVENTS` , `ubb7_CAPTCHA` , `ubb7_CATEGORIES` , `ubb7_CENSOR_LIST` , `ubb7_DISPLAY_NAMES` , `ubb7_fc_bans` , `ubb7_fc_config` , `ubb7_fc_config_chats` , `ubb7_fc_config_instances` , `ubb7_fc_config_values` , `ubb7_fc_connections` , `ubb7_fc_ignors` , `ubb7_fc_messages` , `ubb7_fc_rooms` , `ubb7_FILES` , `ubb7_FORUMS` , `ubb7_FORUM_LAST_VISIT` , `ubb7_FORUM_PERMISSIONS` , `ubb7_GRAEMLINS` , `ubb7_GROUPS` , `ubb7_IMPORT_MAP` , `ubb7_LANGUAGES` , `ubb7_MAILER` , `ubb7_MEMBER_SEARCHES` , `ubb7_MODERATORS` , `ubb7_MODERATOR_NOTIFICATIONS` , `ubb7_MODERATOR_PERMISSIONS` , `ubb7_ONLINE` , `ubb7_POINTER_DELETE` , `ubb7_POLL_DATA` , `ubb7_POLL_OPTIONS` , `ubb7_POLL_VOTES` , `ubb7_PORTAL_BOXES` , `ubb7_POSTS` , `ubb7_PRIVATE_MESSAGE_POSTS` , `ubb7_PRIVATE_MESSAGE_TOPICS` , `ubb7_PRIVATE_MESSAGE_USERS` , `ubb7_RATINGS` , `ubb7_REGISTRATION_FIELDS` , `ubb7_RESERVED_NAMES` , `ubb7_RSS_FEEDS` , `ubb7_SAVED_QUERIES` , `ubb7_SEARCH_AGENTS` , `ubb7_SEARCH_RESULTS` , `ubb7_SHOUT_BOX` , `ubb7_STYLES` , `ubb7_TOPICS` , `ubb7_TOPIC_VIEWS` , `ubb7_USERS` , `ubb7_USER_DATA` , `ubb7_USER_GROUPS` , `ubb7_USER_NOTES` , `ubb7_USER_PROFILE` , `ubb7_USER_TITLES` , `ubb7_VERSION` , `ubb7_WATCH_LISTS`;

5. When the above query is done, to exit mysql issue:
Code
\q


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: Huge Cached Permission Table Overhead [Re: Gizmo] #179916
03/03/07 08:55 AM
03/03/07 08:55 AM
Mark S  Offline
Joined: Jul 2006
Posts: 4,722
Liverpool : England : UK
I will give that a go later tonight on my test database,
and if it works, i'm not a million miles
from making a script smile

Cheers for the leader Gizmo, i know you like praise.


BOOM !! Version v7.6.1.1
People who inspire me Isaac ME Gizmo
Re: Huge Cached Permission Table Overhead [Re: Mark S] #179937
03/03/07 05:12 PM
03/03/07 05:12 PM
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 17,009
Portland, OR; USA
Praise makes me purr like a kitten.... wink...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Need to Upgrade?
Forums: A Gardeners Forum Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!

Shout Box
Today's Birthdays
No Birthdays
Recent Topics
Private Message, Opt out of conversation
by jorb. 12/04/18 10:11 PM
Disable IP display in Who's Online?
by Baldeagle. 11/29/18 09:05 PM
Permissions problem
by Baldeagle. 11/25/18 09:44 PM
Reddy Kilowatt
by SteveS. 11/20/18 08:30 AM
testar
by Morgan. 11/18/18 02:33 PM
Forum Statistics
Forums36
Topics35,172
Posts191,642
Members12,117
Most Online978
Jun 24th, 2007
Random Image
Powered by UBB.threads™ PHP Forum Software 7.6.2