 |
 |
 |
 |
Registered: 03/13/07
Posts: 28
|
|
|
 |
 |
 |
 |
|
|
|
|
|
#179646 - 02/28/07 11:47 AM
Re: Huge Cached Permission Table Overhead
[Re: Rick]
|
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
|
|
Top
|
|
|
|
|
|
|
|
|
|
|
|
|
|
#179858 - 03/02/07 03:20 AM
Re: Huge Cached Permission Table Overhead
[Re: Gizmo]
|
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
#179862 - 03/02/07 03:33 AM
Re: Huge Cached Permission Table Overhead
[Re: 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
|
|
Top
|
|
|
|
|
|
|
|
|
|
