Previous Thread
Next Thread
Print Thread
Hop To
#168534 11/18/2006 7:01 AM
Joined: Aug 2004
Posts: 460
Addict
Addict
Joined: Aug 2004
Posts: 460
To be honest I've never tried this before, even though my forums have been running for a few years now.

- How often should the database be optimized?

- What is the best way to do this (command line perhaps?)?

- Is there any risk involved, could the database become corrupted as a result of this operation?

Joined: Jun 2006
Posts: 684
H
Addict
Addict
H Offline
Joined: Jun 2006
Posts: 684
I've done this just a couple of times through the boards control panel after closing the board. Haven't had any problems to note when doing so.

Joined: Aug 2004
Posts: 460
Addict
Addict
Joined: Aug 2004
Posts: 460
How exactly do you optimize the database through the board's CP?

Also, how long did it take?

Joined: Jun 2006
Posts: 684
H
Addict
Addict
H Offline
Joined: Jun 2006
Posts: 684
Crud. I wish I still had a 6.5 version running to see it. You close the board and the optimize function is in there somewhere. At least it used to be.

Joined: Jun 2006
Posts: 3,837
I
Ian Offline
Carpal Tunnel
Carpal Tunnel
I Offline
Joined: Jun 2006
Posts: 3,837
I think it got taken out. Might be wrong though.

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
Control Panel -> Tools -> SQL Command Tab, save the following queries if you want them:

Analyze Database:
Code
ANALYZE TABLE `forums_ADDRESS_BOOK` , `forums_ADMIN_SEARCHES` , `forums_ANNOUNCEMENTS` , `forums_BANNED_EMAILS` , `forums_BANNED_HOSTS` , `forums_CACHE` , `forums_CACHED_PERMISSIONS` , `forums_CALENDAR_EVENTS` , `forums_CATEGORIES` , `forums_CENSOR_LIST` , `forums_DISPLAY_NAMES` , `forums_FILES` , `forums_FORUMS` , `forums_FORUM_LAST_VISIT` , `forums_FORUM_PERMISSIONS` , `forums_GRAEMLINS` , `forums_GROUPS` , `forums_IMPORT_MAP` , `forums_LANGUAGES` , `forums_MAILER` , `forums_MEMBER_SEARCHES` , `forums_MODERATORS` , `forums_MODERATOR_NOTIFICATIONS` , `forums_MODERATOR_PERMISSIONS` , `forums_ONLINE` , `forums_POLL_DATA` , `forums_POLL_OPTIONS` , `forums_POLL_VOTES` , `forums_PORTAL_BOXES` , `forums_POSTS` , `forums_PRIVATE_MESSAGE_POSTS` , `forums_PRIVATE_MESSAGE_TOPICS` , `forums_PRIVATE_MESSAGE_USERS` , `forums_RATINGS` , `forums_REGISTRATION_FIELDS` , `forums_RESERVED_NAMES` , `forums_RSS_FEEDS` , `forums_SAVED_QUERIES` , `forums_SEARCH_RESULTS` , `forums_SHOUT_BOX` , `forums_STYLES` , `forums_TOPICS` , `forums_TOPIC_VIEWS` , `forums_USERS` , `forums_USER_DATA` , `forums_USER_GROUPS` , `forums_USER_NOTES` , `forums_USER_PROFILE` , `forums_USER_TITLES` , `forums_VERSION` , `forums_WATCH_LISTS`

Optimize Database:
Code
OPTIMIZE TABLE `forums_ADDRESS_BOOK` , `forums_ADMIN_SEARCHES` , `forums_ANNOUNCEMENTS` , `forums_BANNED_EMAILS` , `forums_BANNED_HOSTS` , `forums_CACHE` , `forums_CACHED_PERMISSIONS` , `forums_CALENDAR_EVENTS` , `forums_CATEGORIES` , `forums_CENSOR_LIST` , `forums_DISPLAY_NAMES` , `forums_FILES` , `forums_FORUMS` , `forums_FORUM_LAST_VISIT` , `forums_FORUM_PERMISSIONS` , `forums_GRAEMLINS` , `forums_GROUPS` , `forums_IMPORT_MAP` , `forums_LANGUAGES` , `forums_MAILER` , `forums_MEMBER_SEARCHES` , `forums_MODERATORS` , `forums_MODERATOR_NOTIFICATIONS` , `forums_MODERATOR_PERMISSIONS` , `forums_ONLINE` , `forums_POLL_DATA` , `forums_POLL_OPTIONS` , `forums_POLL_VOTES` , `forums_PORTAL_BOXES` , `forums_POSTS` , `forums_PRIVATE_MESSAGE_POSTS` , `forums_PRIVATE_MESSAGE_TOPICS` , `forums_PRIVATE_MESSAGE_USERS` , `forums_RATINGS` , `forums_REGISTRATION_FIELDS` , `forums_RESERVED_NAMES` , `forums_RSS_FEEDS` , `forums_SAVED_QUERIES` , `forums_SEARCH_RESULTS` , `forums_SHOUT_BOX` , `forums_STYLES` , `forums_TOPICS` , `forums_TOPIC_VIEWS` , `forums_USERS` , `forums_USER_DATA` , `forums_USER_GROUPS` , `forums_USER_NOTES` , `forums_USER_PROFILE` , `forums_USER_TITLES` , `forums_VERSION` , `forums_WATCH_LISTS`

Repair Database:
Code
REPAIR TABLE `forums_ADDRESS_BOOK` , `forums_ADMIN_SEARCHES` , `forums_ANNOUNCEMENTS` , `forums_BANNED_EMAILS` , `forums_BANNED_HOSTS` , `forums_CACHE` , `forums_CACHED_PERMISSIONS` , `forums_CALENDAR_EVENTS` , `forums_CATEGORIES` , `forums_CENSOR_LIST` , `forums_DISPLAY_NAMES` , `forums_FILES` , `forums_FORUMS` , `forums_FORUM_LAST_VISIT` , `forums_FORUM_PERMISSIONS` , `forums_GRAEMLINS` , `forums_GROUPS` , `forums_IMPORT_MAP` , `forums_LANGUAGES` , `forums_MAILER` , `forums_MEMBER_SEARCHES` , `forums_MODERATORS` , `forums_MODERATOR_NOTIFICATIONS` , `forums_MODERATOR_PERMISSIONS` , `forums_ONLINE` , `forums_POLL_DATA` , `forums_POLL_OPTIONS` , `forums_POLL_VOTES` , `forums_PORTAL_BOXES` , `forums_POSTS` , `forums_PRIVATE_MESSAGE_POSTS` , `forums_PRIVATE_MESSAGE_TOPICS` , `forums_PRIVATE_MESSAGE_USERS` , `forums_RATINGS` , `forums_REGISTRATION_FIELDS` , `forums_RESERVED_NAMES` , `forums_RSS_FEEDS` , `forums_SAVED_QUERIES` , `forums_SEARCH_RESULTS` , `forums_SHOUT_BOX` , `forums_STYLES` , `forums_TOPICS` , `forums_TOPIC_VIEWS` , `forums_USERS` , `forums_USER_DATA` , `forums_USER_GROUPS` , `forums_USER_NOTES` , `forums_USER_PROFILE` , `forums_USER_TITLES` , `forums_VERSION` , `forums_WATCH_LISTS`

Be sure to replace forums_ with whatever your database prefix is.


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.
Do you need Forum Install or Upgrade Services?
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!
Joined: Aug 2004
Posts: 460
Addict
Addict
Joined: Aug 2004
Posts: 460
Before I start messing with my database, what does analyzing do? Will that tell me if it's worth optimizing a table? Does analyzing the table alone make any changes to the database/table?

Joined: Jul 2006
Posts: 2,143
Pooh-Bah
Pooh-Bah
Joined: Jul 2006
Posts: 2,143
Ok, I'm going to wayyyyyyyyyyy oversimplify here.

[oversimplify]Optimize is like a defrag, it retrieves all the emtpy space that can form in a table or tables that have a lot of deletes.[/oversimplify]


[even bigger oversimplify]Analyze, on the other hand decides the order in which tables should be joined[/oversimplify]

Really you should read the annotated documentation at mysql.com for this, as my oversimplifications don't actually do the proper answers any justice at all.

Now, one thing you really want to note is that optimize puts a lock on the tables while it rewrites them. depending upon whether you're using INNODB or MYISAM it could be read or write. This means that a table might, and probably will, be completely useless while the optimize is running. Consider that if you have a rather large messages table and plan accordingly.



This thread for sale. Click here! [Linked Image from navaho.infopop.cc]
Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
The MySQL Analyze command checks the table indexes, you generally won't really need to do so however, Optimize/Repair should be just about anything you'd need.


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.
Do you need Forum Install or Upgrade Services?
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!
Joined: Aug 2004
Posts: 460
Addict
Addict
Joined: Aug 2004
Posts: 460
Thanks guys.

If my posts table is over 500 megs and has never been optimized (with the forums running for a few years now), would it be a good idea to do this? Is there any risk to this (database corruption, the optimization process hanging up/timing out half-way through, etc.)?

How long can it take? It took less than a minute to dump the entire database, but around how long would it take to optimize a 500 meg posts table (we're talking about a dedicated machine, Dual Xeon, two gigs ram)? Are we talking minutes here or hours?

Joined: Jul 2006
Posts: 2,143
Pooh-Bah
Pooh-Bah
Joined: Jul 2006
Posts: 2,143
Doing it via command line shouldn't take but a few minutes if a dump only takes just a minute. If it dies in the middle of the operation nothing gets hurt.

Optimizing the table actually writes out the table into a temp table (with no space in the middle.) When that space free copy is finished MySQL deletes the fragmented table and renames the temp in it's place.

If the operation dies in the middle the temp gets cleaned up and nothing happens.


This thread for sale. Click here! [Linked Image from navaho.infopop.cc]
Joined: Aug 2004
Posts: 460
Addict
Addict
Joined: Aug 2004
Posts: 460
Huge thanks!

Just two really quick questions before I start optimizing at around 3pm Seattle time (hope I can reach you for a few quick tips if anything goes wrong!).

- I only need to run the regular "OPTIMIZE TABLE <TABLE NAME>" command without any fancy options, right?

- Anything to do before or after I run the command? Lock the board, run the command, possibly clear the cache, unlock the board, is that ok?

One last thing, should I run this for each table or just w3t_Posts?

P.S. Will the table really be reduced in size if there have been lots of deletions here and there over the years? smile

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
SHOULD be minutes; and I've never had any database issues while running the commands.

I would however recommend turning your forums off while running such maintenance tasks as it does lock the database while it runs.


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.
Do you need Forum Install or Upgrade Services?
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!
Joined: Aug 2004
Posts: 460
Addict
Addict
Joined: Aug 2004
Posts: 460
Hmm, what's going on? I tried the command and it keeps telling me that no such command is available?

Joined: Aug 2004
Posts: 460
Addict
Addict
Joined: Aug 2004
Posts: 460
Never mind. I ran that via the root login. Everything worked perfectly using the forum CP.

I just saved like 150-200 megs in a single go. smile Any other tables I should optimize? Or should I not even bother since the second largest one is under 15 megs...?

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
The query i posted above should hit every .T7 table.

As for the command, it's a MySQL command, not a command line command; if you enter mysql via command line (command: mysql -uuser -ppassword) you can run the command from there.


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.
Do you need Forum Install or Upgrade Services?
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!
Joined: Dec 2006
Posts: 10
Z
stranger
stranger
Z Offline
Joined: Dec 2006
Posts: 10
If you have access to MySQL Admin you can do it from there.
I have a DB that is about 650 megabytes and do the optimize once a month. It takes about an hour.

Not sure where the info that it takes minutes is coming from but that has not been my experience at all.

The analyze option can be used if you have a corrupt table as a second opinion. There are a ton of tables in this main DB and once in while one of them will take a shot across the bow and need to be repaired.

As always make a complete back up of the *.sql prior to running any commands on it. If the db has a bad table it won't let you do a backup more than likely.

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
I've had currupted tables backup several times, however i've had the currupted data trunicated from the backup (using mysqldump).

As for optimize taking so long, it'd vary based on several variables, such as db size, available resources (memory/processor). If you have a busy server, at peak hours, and a large database, it'll definitely take longer than a server at off peak hours with available resources and a smaller database.

Also, executing the command via the MySQL command line interface will run much faster than issuing the command through a web script (such as PHPMyAdmin).

Last edited by Gizmo; 12/05/2006 1:59 AM.

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.
Do you need Forum Install or Upgrade Services?
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!
Joined: Dec 2006
Posts: 10
Z
stranger
stranger
Z Offline
Joined: Dec 2006
Posts: 10
Its a dedicated server with 2 gig memory. Now you have me wondering. confused

As far as MySQLAdmin goes, it's available from the web without a shell and I can't make any typos.

You know that feeling when you hit the enter key, don't you? sick

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
If you're doing it "from the web" you're likely doing it through a PHP script which will also go through Apache; both of which will utilize several systems (versus doing it through the command line which does it through MySQL directly)


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.
Do you need Forum Install or Upgrade Services?
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!
Joined: Dec 2006
Posts: 10
Z
stranger
stranger
Z Offline
Joined: Dec 2006
Posts: 10
Thanks Gizmo.

I'm old, habits die slowly. wink

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
lol its understandable, at first doing things through the command line can be kind of scary/overwhelming, but you'll notice that things go much faster 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.
Do you need Forum Install or Upgrade Services?
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!
Joined: Nov 2006
Posts: 3,095
Likes: 1
Carpal Tunnel
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095
Likes: 1
Well my database is only about 2/3 that size but it completes in a few minutes for me. I run it from the command line when I do it.

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
Large database, through a script, you'd also have to worry about timeouts on certain functions...


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.
Do you need Forum Install or Upgrade Services?
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!
Joined: Dec 2006
Posts: 10
Z
stranger
stranger
Z Offline
Joined: Dec 2006
Posts: 10
Thanks Folks.

It was also heartwarming to discover that if the optimize fails or times out that the original .sql file does not get hosed.

Now I need a Jack and Coke and a little time to try the command line. After a backup of course. laugh

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
Yes, always backup before playing lol... It cannot be stressed enough wink...

Some good SSH command line knowledge can go a long way, most of the things you can do through FTP you can do through SSH (and those that you can't you can use SCP).

I use the SSH Secure Shell application from SSH.com (available here) because it allows me to use SSH keys to connect to my server (versus storing passwords on the server), but thats just my paranoid ways hehe...


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.
Do you need Forum Install or Upgrade Services?
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!
Joined: Nov 2006
Posts: 3,095
Likes: 1
Carpal Tunnel
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095
Likes: 1
Gizmo... That doesn't look like a freeware version.

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
Originally Posted by ntdoc
Gizmo... That doesn't look like a freeware version.
it's what i'm running, it's freeware, it's their "tecra" version which isn't freeware.


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.
Do you need Forum Install or Upgrade Services?
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!
Joined: Nov 2006
Posts: 3,095
Likes: 1
Carpal Tunnel
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095
Likes: 1
I see so it is. July 9, 2004 when that project closed.

There are some newer projects.

A Comparison of Free SSH and SCP Programs
for Windows 9x, NT, ME, 2000 and XP
http://www.jfitz.com/tips/ssh_for_windows.html


copSSH 1.4.1
OpenSSH 4.5 has just been released. It will be available from the
mirrors listed at http://www.openssh.com/ shortly.
OpenSSH is a 100% complete SSH protocol version 1.3, 1.5 and 2.0
implementation and includes sftp client and server support.

Highlights:

* OpenSSH 4.4p1 and OpenSSL 0.9.8d
* copSSH administration tool
* use NONTSEC in CYGWIN environment
* GUI Wizards for user activation and deactivation
* Improved support for localized windows versions
* and more!
http://www.itefix.no/phpws/index.php?module=announce&ANN_user_op=view&ANN_id=79


http://www.openssh.com/

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
I've tried several clients, keep returning to this one wink... The only other one I've used that's even remotely decent is Putty, but it's easier to operate the SSH.com client.

Last edited by Gizmo; 12/06/2006 5:41 PM.

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.
Do you need Forum Install or Upgrade Services?
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!

Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
spam issues
by ECNet - 03/19/2024 11:45 PM
Looking for a forum
by azr - 03/15/2024 11:26 PM
Editing Links in Post
by Outdoorking - 03/15/2024 9:31 AM
Question on barkrowler and the like
by Mors - 02/29/2024 6:51 PM
Member Permissions Help
by domspeak - 02/27/2024 6:31 PM
Who's Online Now
3 members (rootman, Gizmo, Nightcrawler), 562 guests, and 186 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)