Optimize Overhead for database

[edhan]

Hi

Once awhile I will check using phpMyAdmin to check on individual database tables and find there are some overhead for some fields. So I will manual select them and click to optimize.

I find this method is a little troublesome as I have about 100 databases and going through one at a time to check if there are overhead and selecting them to optimize. This really takes up a lot of my time.

So I am wondering if there is a better method where I can do it automatically?

Any suggestion? Or this is the only method?

Thanks!

[kgun]

Data base optimization is a big, complex and very important topic.

May be a good and thoughtful (book?) search is the best. On the site in the third link in my signature you find some links under the heading

"Optimize, structure and document your code. Cache your pages where possible"

that may be relevant.

[edhan]

Many thanks!

I will check out your link. Hopefully it helps to widen my knowledge and get some insights.

Appreciate your reply.

[datetopia]

If you just refer to managing existing databases and data you can make a script to run REPAIR and OPTIMIZE on all tables.

[wdillsmith]

I've found that this happens most often in mysql if you delete a lot of rows. So you might want to look at how your system is organized to reduce the deletes or target just those tables that are affected. I probably have 75 tables, but only a couple have regular deletions, so I just optimize those 2 once a week and I'm all set.

[edhan]

Originally Posted by datetopia

If you just refer to managing existing databases and data you can make a script to run REPAIR and OPTIMIZE on all tables.

I am currently running about 100 domains in one server and each domain has its own database. Currently what I am doing is accessing the phpMyAdmin once a month using root account to view all databases one by one to see if the tables are having any overhead. If there are, then I will manual choose the tables and optimize it.

Do you have a sample of the script or where can I find such script to be able to assist optimization of all domains databases from the same server at once?

Thanks!

[RichAtVNS]

Originally Posted by edhan

I am currently running about 100 domains in one server and each domain has its own database. Currently what I am doing is accessing the phpMyAdmin once a month using root account to view all databases one by one to see if the tables are having any overhead. If there are, then I will manual choose the tables and optimize it.

Do you have a sample of the script or where can I find such script to be able to assist optimization of all domains databases from the same server at once?

Thanks!

We actually run a script every night at 4 am to compress all our databases.

Just make a batch file to run them,
1) turn off the domain for the maintence for approximately 2 minutes.
2)copy the database to a temporary copy,
3) compress the copy (through an embedded function in a module),
4) if it succeeds copy it back (so as not to corrupt the database in case it doesn't work.)
5) turn back on the domain.
6) goto the (1) for the next domain.

You don't need to do every domain every night. if you have to many.

To launch the batch use the Timer in the system scheduler. (this is available in both dos/windows and unix/linux enviornments)

[edhan]

Originally Posted by RichAtVNS

We actually run a script every night at 4 am to compress all our databases.

Just make a batch file to run them,
1) turn off the domain for the maintence for approximately 2 minutes.
2)copy the database to a temporary copy,
3) compress the copy (through an embedded function in a module),
4) if it succeeds copy it back (so as not to corrupt the database in case it doesn't work.)
5) turn back on the domain.
6) goto the (1) for the next domain.

You don't need to do every domain every night. if you have to many.

To launch the batch use the Timer in the system scheduler. (this is available in both dos/windows and unix/linux enviornments)

That sounds good. Where do I find this script?

[RichAtVNS]

the script is a BATCH file (.bat)

You can run any program from it. Didn't you ever learn DOS or Unix?

Use a program like Workspace Macro ($25 shareware) to send keystrokes to you database if your website and database software don't take command line launches.

Note programs like access and IIS Manager can take command line parameters already. So all you need to do is to call them from in the file.

Here is a sample batch file to check if my machine needs to reboot because connectivity has been lost and may need a reset

Note it checks 3 sites 3 times to see if it can get through......

-------------------------------
@echo off

:START
echo Testing Yahoo
ping.exe yahooxxxxxx.com -n 3 -w 1000 > D:\rebootcontrol\log\YahooIP.txt
IF NOT EXIST D:\rebootcontrol\log\YahooIP.txt goto TESTFAILED1
findstr /b /c:"Reply from " D:\rebootcontrol\log\YahooIP.txt > nul
IF %errorlevel%==1 goto TESTFAILED1
echo Success
goto end


:TESTFAILED1
echo Testing USNO
ping.exe tychoxxxxxxx.usno.navy.mil -n 3 -w 1000 > D:\rebootcontrol\log\USNOIP.txt
IF NOT EXIST D:\rebootcontrol\log\USNOIP.txt goto TESTFAILED2
findstr /b /c:"Reply from " D:\rebootcontrol\log\USNOIP.txt > nul
IF %errorlevel%==1 goto TESTFAILED2
echo Success
goto end


:TESTFAILED2
echo Testing Google
ping.exe googlexxxxxx.com -n 3 -w 1000 > D:\rebootcontrol\log\GoogleIP.txt
IF NOT EXIST D:\rebootcontrol\log\Google.txt goto TESTFAILED3
findstr /b /c:"Reply from " D:\rebootcontrol\log\GoogleIP.txt > nul
IF %errorlevel%==1 goto TESTFAILED3
echo Success
goto end


:TESTFAILED3
echo Failed
IF NumberOFTries==3 GOTO REBOOT
pause 15
GOTO START
goto end

:REBOOT
call app

:end
echo The end.

pause
-------------------------------

I don't know what operating system you are running windows or unix
but they are very similar

if you need help with batch language just bring up the command line prompt in windows and type help it will come up with all the commands you can use.

After you write the batch file place it in scheduler is
which in windows is under
"program files->accesories->system tools " in your start menu.
Scheduled Tasks.

---------------------------------

If you need any more one of my programmers could probably write the whole kit and kaboodle for you in under a day it wouldn't cost to much.

[edhan]

Originally Posted by RichAtVNS

the script is a BATCH file (.bat)

You can run any program from it. Didn't you ever learn DOS or Unix?

Yes. I do learn DOS. I am not running my website on local computer but hosting them overseas. I am doing remote access using telnet, ftp or browser for access. So, from what you have said, I will need to create a cron file (since I am using Unix) to run the cronjob for processing the optimization.

So, guess I have to learn the command for running the cronjob to optimize the database.

Any other better way of clearing overhead for the database? I have checked and found that most of the overheads are using Wordpress Blogs.