MySQL Maintenance outside NMP

Post new topic   Reply to topic Forum Index : Scripts and User Contributions
View previous topic :: View next topic  
Author Message

Joined: 12 Dec 2004
Posts: 36
Location: UK

PostPosted: Sat Mar 04, 2006 4:11 am    Post subject: MySQL Maintenance outside NMP Reply with quote

NMP uses SQL commands to optimise its tables. What makes these commands safe, also makes them slow. They also affect the performance of the entire MySQL server and its other databases. Fortunately, the MySQL server has utility programs to do the optimising and repair of tables. However, these programs can cause table damage if used without locking the tables against changes while being optimised. the easiest way to prevent such damage is to shut down the server while maintenance is carried out.

I've been using a batch script to optimise my server for some time, adding the NMP tables was simple. However, NMP carries out other house-keeping tasks on the tables before optimising them. One of these tasks, is removing orphaned headers from tables. Orphaned headers are the result of entries being removed from the main header table before all references to them have been cleared. This can happen for various reasons, OS crashes, power failure etc. Orphans take up space in the tables, but more importantly occupy space in the indexes that are used to track entries. The larger your indexes, the slower they are. So keeping the indexes lean, improves performance.

Recently, Admin gave me some very useful info in the arrangement of the tables. From this, I was able to write a script that cleans out any orphaned entries in the attachment, bodies, messages, and parts tables. When I ran the script for the first time, most work-groups had few, if any, orphans in them. But the heaviest traffic group I have, which daily receives about 2-4 million headers, had over 1.5 million orphans in it.

In order to share, I've had to adapt my script so that anyone running NMP can use it (it was originally written in a scripting language, which few of you will have installed). To do this, I've borrowed heavily from Wilbert's pwrPurge script (thanks for that script, and apologies for plagiarism in advance), any bugs found are mine. This adapted version has been given basic testing and seems to work fine, but I would only consider it beta quality until tested further by usage. I've been running the optimising batch file for over two years without problem.

There are three files: 'Optimise.bat', 'CleanUpOrphans.bat', and 'generate.sql'. All three files must be kept in the same directory, but can be located anywhere. Generate.sql is used by CleanUpOrphans.bat to create a script of SQL commands customised to your particular NMP database. This script is then sent to the server to do the actual cleaning. Optimise.bat first calls CleanUpOrphans.bat before flushing all table data to disc, shutting down the server, optimising all NMP's database files, defragging the drive, and finally restarting the server.

These files assume that you have a default configuration, i.e. that your NMP database is called 'newsman', that the MySQL server is installed to its default location on a Windows box and is installed as a service called 'MySQL'. If you don't have a default configuration, the files need to be modified accordingly. They are commented to help with modifications.

You can run the 'Optimise.bat' manually, doulble-clicking it, but it's easier to set it to run using Window's scheduler after putting in the appropriate username/password into the files.

Optimise files
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic Forum Index : Scripts and User Contributions All times are GMT
Page 1 of 1

Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Copyright 2003-2006, Daniel Cumpian
NMP Default By