[NOTABUG] Huge Cached Permission Table Overhead

You are not logged in. [Log In] UBB.threads Community » Forums » UBB.threads 7 Discussion » Bug Reports
» Non Bugs » [NOTABUG] Huge Cached Permission Table Overhead

Site Links

Home
Features
Documentation
Pricing & Order
Members Area
Support Options

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

Page 2 of 3

Topic Options

Rate This Topic

#179846 - 03/02/07 01:16 AM Re: Huge Cached Permission Table Overhead [Re: Gizmo]
Conrad enthusiast Registered: 08/04/04 Posts: 361	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... $\:\)$ Also, why would there be risk of any database corruption if you can lock the board down before optimizing?
Top

#179849 - 03/02/07 02:52 AM Re: Huge Cached Permission Table Overhead [Re: Conrad]
Gizmo Registered: 06/04/06 Posts: 12006 Loc: Portland, OR; USA	It's all done automagially! lol... Or shell scripting, choose your poison... _________________________ UGN Security, Elite Web Gamers & VNC Web Design Owner Longtime UBB Supporter, UBB7 Beta Tester & Resident Post-A-Holic
Top

#179854 - 03/02/07 03:15 AM

Re: Huge Cached Permission Table Overhead [Re: Gizmo]

Gizmo

Registered: 06/04/06
Posts: 12006
Loc: 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

_________________________
UGN Security, Elite Web Gamers & VNC Web Design Owner
Longtime UBB Supporter, UBB7 Beta Tester & Resident Post-A-Holic

Top

#179858 - 03/02/07 03:20 AM Re: Huge Cached Permission Table Overhead [Re: Gizmo]
Mark S Carpal Tunnel Registered: 07/04/06 Posts: 4044 Loc: 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 $;\)$ Edited by Mark S (03/02/07 03:25 AM) Edit Reason: added click on _________________________ Version v7.2.2 < Threads satisfaction status People who inspire me Rick Gizmo Ian David jgeoff ntdoc To answer the question you must first give a question.
Top

#179859 - 03/02/07 03:22 AM Re: Huge Cached Permission Table Overhead [Re: Mark S]
Mark S Carpal Tunnel Registered: 07/04/06 Posts: 4044 Loc: Liverpool : England : UK	well i have a long way to go before i can write a script to do that. _________________________ Version v7.2.2 < Threads satisfaction status People who inspire me Rick Gizmo Ian David jgeoff ntdoc To answer the question you must first give a question.
Top

#179861 - 03/02/07 03:29 AM Re: Huge Cached Permission Table Overhead [Re: Mark S]
Gizmo Registered: 06/04/06 Posts: 12006 Loc: 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 ;)... _________________________ UGN Security, Elite Web Gamers & VNC Web Design Owner Longtime UBB Supporter, UBB7 Beta Tester & Resident Post-A-Holic
Top

#179862 - 03/02/07 03:33 AM Re: Huge Cached Permission Table Overhead [Re: Gizmo]
Gizmo Registered: 06/04/06 Posts: 12006 Loc: 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 _________________________ UGN Security, Elite Web Gamers & VNC Web Design Owner Longtime UBB Supporter, UBB7 Beta Tester & Resident Post-A-Holic
Top

#179863 - 03/02/07 03:44 AM Re: Huge Cached Permission Table Overhead [Re: Gizmo]
Mark S Carpal Tunnel Registered: 07/04/06 Posts: 4044 Loc: Liverpool : England : UK	Its easy for me to click on a button $\:\)$ I wouldnt know what to type in for a shell command. /> : Gizmo optermize my tables please lol _________________________ Version v7.2.2 < Threads satisfaction status People who inspire me Rick Gizmo Ian David jgeoff ntdoc To answer the question you must first give a question.
Top

#179864 - 03/02/07 03:49 AM Re: Huge Cached Permission Table Overhead [Re: Mark S]
Gizmo Registered: 06/04/06 Posts: 12006 Loc: Portland, OR; USA	Mark just loves how i do things :nod: I think he should hire me as his server tech lol _________________________ UGN Security, Elite Web Gamers & VNC Web Design Owner Longtime UBB Supporter, UBB7 Beta Tester & Resident Post-A-Holic
Top

#179865 - 03/02/07 03:54 AM Re: Huge Cached Permission Table Overhead [Re: Gizmo]
Mark S Carpal Tunnel Registered: 07/04/06 Posts: 4044 Loc: Liverpool : England : UK	Name yu price : in corn flakes lol... _________________________ Version v7.2.2 < Threads satisfaction status People who inspire me Rick Gizmo Ian David jgeoff ntdoc To answer the question you must first give a question.
Top

Page 2 of 3

Previous Topic

Index

Next Topic

Hop to:

Moderator: AllenAyres, Gizmo, Harold, Ian, Ian Spence, Ron M, Sirdude, usrbingeek

Shout Box

December
Su	M	Tu	W	Th	F	Sa
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

Today's Birthdays

brushie, cass, Monte G., twebman, White Gold Wielder