12 replies to this topic

[TheExoticFish]

Does anyone know the (I guess it's called) command line I need to put to automatically optimize my database every few days ?

[Dan W]

Does anyone know the (I guess it's called) command line I need to put to automatically optimize my database every few days ?

If you have the optimization code in a php file just do "php /home/username/SCRIPT.PHP" where /home/username/SCRIPT.PHP is the full path to the file.

[TheExoticFish]

I don't, that's what I need.

[Dan W]

Optimizing is such a vague term. I went into phpMyAdmin from cPanel, and noticed that within a database (under the structure tab) you can select all tables and optimize them. I ran this option and it spit out the query that it used to perform the operation.

I was using a database for RadiantCMS. I selected all tables. The query looks like this:

CODE

OPTIMIZE TABLE  `assets` ,  `config` ,  `extension_meta` ,  `layouts` ,  `old_page_attachments` ,  `pages` ,  `page_attachments` ,  `page_parts` ,  `schema_migrations` ,  `sessions` ,  `snippets` ,  `users`

So, in your PHP file, you need to open up a database connection and run a query similar to mine on your database. Do you know how to do this?

There are also queries available for "check" "repair" and "analyze" in addition to "optimize" -- I am not sure EXACTLY what any of them do. Do some research! Play around with phpMyAdmin, it's a great tool.

[TheExoticFish]

I already do "optimize", "check", etc manually. What I want to do is just add a cron job to do it every few days. I'm not sure how to do the whole php file that opens up a database connection etc etc.

[Dan W]

Something like this may help. I made this script from my own knowledge, and a little bit of research on Google. This script can only optimize one database. If you need to optimize more than one, you will have to add a few more lines of code in there.

CODE

<?php

$server = 'localhost';
$username = 'mysql_username';
$password = 'mysql_password';
$database = 'mysql_database_name';

### connects to the database, or dies with error
$connection = mysql_connect($server,$username,$password);
if (!$connection)
{
  die( mysql_error() );
}

### selects the db of choice, or dies with error
$db_selection = mysql_select_db($database, $connection);
if (!$db_selection)
{
  die( mysql_error() );
}

### selects all tables in the db of choice, or dies with error
$alltables = mysql_query("SHOW TABLES") or die ( mysql_error() );

### loops through all of the tables and optimizes each, or dies with error
while ( $table = mysql_fetch_array($alltables) )
{
  mysql_query("OPTIMIZE TABLE `".$table."`") or die( mysql_error() );
}

### closes the mysql connection
mysql_close($connection);

?>

Be sure to edit the variables at the top to the values that are appropriate for you. When you go to run your cron job, again just do "php /full/path/to/script.php" and since optimizing tables may lock down your DB for a second, run the script at a time when you don't expect traffic.

Edited by Dan W, 20 July 2009 - 10:38 AM.

[Dan W]

Theres no sense doing this with PHP when it can be done with the mysql command line tool.

CODE

mysql -u<username> -p<password> <database name> -e "OPTIMIZE TABLE `table1`, `table2`, `etc`;"

now if the tables are constantly changing (not likely) it might be easier to use the PHP method above.

Good call! It would lighten the server load a tad as well.

[TheExoticFish]

Thanks  

[goldenboy48]

-ASO- Frank, on 20 July 2009 - 01:21 PM, said:

Theres no sense doing this with PHP when it can be done with the mysql command line tool.

<!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->mysql -u<username> -p<password> <database name> -e "OPTIMIZE TABLE `table1`, `table2`, `etc`;"<!--c2--></div><!--ec2-->

now if the tables are constantly changing (not likely) it might be easier to use the PHP method above.

Thanks. Is this a php file? Where do I put in the code? Does it go inside cron "code area" in the cPanel? Thanks

[IBBoard]

No, it isn't PHP, it is a command with some variables (that was then mangled when you quoted it).

As an example, if your database login was "fred", your password was "pwd", your database was "fred_maindb" and you wanted to optimise the tables "members" and "posts" then you'd get:

mysql -ufred -ppwd fred_maindb -e "OPTIMIZE TABLE `members`, `posts`;"

Do keep in mind, though, that your password will be stored in plain-text on the file system and will be visible while the command runs to anyone running "ps aux" to get a list of running process.

[goldenboy48]

Thanks for the reply. Very helpful.

Is there a command that optimizes the whole database?

[IBBoard]

"OPTIMIZE TABLE *" may work, but if not then the command would be "OPTIMIZE TABLE [insert list of all tables here]".