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.

[devkant]

Mysql tables often get overburdened and create overhead, after several deletes or several changes (edits) to a table, with variable-length rows (i.e. tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns).Overhead is comparable to defragmentation in a hard dive.Overhead for active mysql tables can reach high levels,

[edhan]

I still can't find a better way of doing optimization so I am sticking to manual for the time being. I am sorting out some issues as why there are overhead for wordpress as I am running around 40 per server.

[MrGamm]

Sometimes it sucks to have to write a shell script... it can be alot easier to just go in through php...

$con = mysql_connect()

// I forgot the command to retrieve the table names so I am guessing...

$tables = get_a_list_of_all_the_tables($con);

foreach($tables as $a => $b){

$q = mysql_query("REPAIR TABLE `".$b."`",$con);

}

I did not try this code... I don't expect it to work... it's just for illustration...


PHP: mysql_list_tables - Manual




The best place to figure out more about optimizing tables is with the EXPLAIN statement... jmo...

You can prepend it to the beginning of your database query and mysql will return info about the query rather than the data you asked for...

"SELECT * FROM `table` WHERE `cat` = '2' ORDER BY `created`"

just add...

"EXPLAIN SELECT * FROM `table` WHERE `cat` = '2' ORDER BY `created`"

It will give you a good idea on which queries are overloading the server with table scans and which columns need keys on them.

MySQL :: MySQL 5.1 Reference Manual :: 12.3.2 EXPLAIN Syntax

It's probably not going to fix the overhead issue though... I don't know...



You can also dump a cron into linux via php as well... ( I forget the proper scheduling numbers )

system('0 0 0 0 5 wget -O - http://domain.com/the_script_which_r...ir_command.php --user-agent cron');


http://ca2.php.net/system

[edhan]

Originally Posted by MrGamm

Sometimes it sucks to have to write a shell script... it can be alot easier to just go in through php...

$con = mysql_connect()

// I forgot the command to retrieve the table names so I am guessing...

$tables = get_a_list_of_all_the_tables($con);

foreach($tables as $a => $b){

$q = mysql_query("REPAIR TABLE `".$b."`",$con);

}

I did not try this code... I don't expect it to work... it's just for illustration...


PHP: mysql_list_tables - Manual

Guess I will have to spend time reading this ... hope to find a solution so I do not need to do manually.


The best place to figure out more about optimizing tables is with the EXPLAIN statement... jmo...

You can prepend it to the beginning of your database query and mysql will return info about the query rather than the data you asked for...

"SELECT * FROM `table` WHERE `cat` = '2' ORDER BY `created`"

just add...

"EXPLAIN SELECT * FROM `table` WHERE `cat` = '2' ORDER BY `created`"

It will give you a good idea on which queries are overloading the server with table scans and which columns need keys on them.

MySQL :: MySQL 5.1 Reference Manual :: 12.3.2 EXPLAIN Syntax

It's probably not going to fix the overhead issue though... I don't know...



You can also dump a cron into linux via php as well... ( I forget the proper scheduling numbers )

system('0 0 0 0 5 wget -O - http://domain.com/the_script_which_r...ir_command.php --user-agent cron');


PHP: system - Manual

I know as I am having difficulties doing so ...


Guess I will have to spend time reading this ... hope to find a solution so I do not need to do manually. AS for the cron, I do know how to do the schedule. Thanks! Anyway, I am still hoping for someone who can help with the script that works otherwise I will have to do trial and error myself.

[Gtal]

Yes that's your best solution to spend some time reading that. Then you might have to do it manually. But guess that's alright too.

[IndusaExpo]

I completly Agree...!

[edhan]

Update: Till now I am still unable to find the best way to optimize the entire database with my cronjob. If I can find the correct script to run on cronjob, I will put it here for others to see.

Meantime, still reading up on mysql database.

[edhan]

Thanks. I will check SEM blogs as suggested. Hopefully I can get a solution.

[danlefree]

I threw together a basic MySQL Automated Maintenance Script which should do the trick.

Please let me know by private message if you encounter any issues with it.

[edhan]

Thanks a million! I will check it out and if it does not work, I will send you a PM.

[edhan]

Danlefree

Received this error:

Fatal error: Class 'mysqli' not found on line 43

[danlefree]

Sounds like it may be time for a PHP update?

The mysqli extension is included with PHP versions 5 and later.

- PHP MySQLi Documentation

Added legacy support to the mysql_maintenance.php script, in any case.

[edhan]

Originally Posted by danlefree

Sounds like it may be time for a PHP update?



- PHP MySQLi Documentation

Added legacy support to the mysql_maintenance.php script, in any case.

My server is running with PHP 5. I will check with the tech support.

[danlefree]

Originally Posted by edhan

My server is running with PHP 5. I will check with the tech support.

I did update the script to support PHP's legacy mysql functions, so you should be able to run it either way.

Note: You will need to add a shebang to the beginning of the script and set the correct script execution permissions if you plan to call the script directly from a cron job - alternatively, if you have PHP's Command Line Interface (php-cli) library installed, you can invoke the script from cron with a call to PHP followed by the script's filename.

[edhan]

Originally Posted by danlefree

I did update the script to support PHP's legacy mysql functions, so you should be able to run it either way.

Note: You will need to add a shebang to the beginning of the script and set the correct script execution permissions if you plan to call the script directly from a cron job - alternatively, if you have PHP's Command Line Interface (php-cli) library installed, you can invoke the script from cron with a call to PHP followed by the script's filename.

This time no error but it does not optimize the table. I ended up going into phpMysql to do manual optimization.

[danlefree]

Ah, sorry - got that sorted.


I was calling mysql_fetch_assoc instead of mysql_fetch_array so the $table[0] reference was returning null.

Updated + tested for both mysqli and mysql_* pathways.

[edhan]

Hi danlefree

Thanks! You have solved my problem of doing manual optimization.

I would recommend anyone who faces the same problem to use your script.