Mysql оптимизация таблиц php

Optimize tables in MySQL automatically with PHP

In previous posts I looked at how to optimize a MySQL table from the MySQL command line interface and from phpMyAdmin by using the optimize [tablename] command to free up unused space. In this post I will look at how to do this with a PHP script which could be run periodically to optimise all non-optimal MySQL tables.

The SQL we’ll use to find tables which are non-optimal looks like this:

SHOW TABLE STATUS WHERE Data_free > [integer value]

substituting [integer value] for an integer value, which is the free data space in bytes. This could be e.g. 102400 for tables with 100k of free space. This will then only return the tables which have more than 100k of free space.

An alternative way of searching would be to look for tables that have e.g. 10% of overhead free space by doing this:

SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1

The downside with this is that it would include small tables with very small amounts of free space so it could be combined with the first SQL query to only get tables with more than 10% overhead and more than 100k of free space:

SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400

Using the above SQL, the PHP code would look like this:

$res = mysql_query(‘ SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400 ‘); while($row = mysql_fetch_assoc($res))

And that’s all there is to it. You could then run this PHP code snippet within a full PHP script and run it via cron once per day.

Check Out These Related posts:

Источник

Optimize A MySQL Table Using PHP

Note: This post is over two years old and so the information contained here might be out of date. If you do spot something please leave a comment and we will endeavour to correct.

In MySQL the OPTIMIZE TABLE can be used if you have made changes or have deleted large parts of the table.

Any deleted rows are kept behind the scenes in the server in order to allow the reuse of these spaces. The OPTIMIZE TABLE command reclaims the unused space and defragments the data file.

1. If the table has deleted or split rows, repair the table.
2. If the index pages are not sorted, sort them.
3. If the table’s statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

You can do this automatically by using the following PHP code.

 $tablename)< $sql = 'OPTIMIZE TABLE '.$tablename.';'; $response = mysql_query($sql) or die(mysql_error()); $output[] = mysql_fetch_assoc($response); >; >; // print output print_r($output);

Here is a sample of what the output array contains

 Array ( [Table] => database.table1 [Op] => optimize [Msg_type] => status [Msg_text] => Table is already up to date ) [1] => Array ( [Table] => database.table2 [Op] => optimize [Msg_type] => status [Msg_text] => OK )

Note that you don’t need to run this command every time you do anything. You should only run this after a major data upheaval, or after a few months or weeks of usage.

Phil is the founder and administrator of #! code and is an IT professional working in the North West of the UK. Graduating in 2003 from Aberystwyth University with an MSc in Computer Science Phil has previously worked as a database administrator, on an IT help desk, systems trainer, web architect, usability consultant, blogger and SEO specialist. Phil has lots of experience building and maintaining PHP websites as well as working with associated technologies like JavaScript, HTML, CSS, XML, Flex, Apache, MySQL and Linux.

Want to know more? Need some help?

Let us help! Hire us to provide training, advice, troubleshooting and more.

Support Us!

Please support us and allow us to continue writing articles.

Источник

Optimize MySQL tables for better performance

If you run a web application like WordPress you should know that a many database transactions creating an overhead on your database tables. This overhead will slow down the database server because more memory need to used than without overhead. While this PHP tutorial is for the beginning webmaster is the PHP code (or the idea behind the code) also useful for the professional webmaster, which host his websites on a VPS or dedicated server.

How-to remove database table overhead?

The simplest way is to login to your database using phpMyAdmin and optimize the tables using the available functions. This action will take some time because you need to check each database first. Much better would be a script that is executed once a day. In this PHP tutorial we will create a PHP script which is able do this task for all your databases and all your tables.

Prepare your MySQL databases

If you host your websites on your a VPS or dedicated server you can use the credentials for the MySQL root user. If you can’t use the root user for this task or if you’re using shared web hosting you need to create a database user which has access to all your databases. If you have SSH access you can create a user which get’s access to all your database tables using the MySQL console (you need to login with the database user: “mysql -u dbuser -p” first):

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON thedatasename.* TO 'optiuser'@'localhost' IDENTIFIED BY 'somepassword';

You can run this command also in phpMyadmin or any other MySQL tool. If you have a lot of different database users and no “global” user, you should ask your hosting provider to create such a user. If you have access to a control panel like DirectAdmin or cPanel, they offer the option to create this user too. Don’t forget that you need to authenticate as the current database user first before you’re able to create a new user for the selected database.

Don’t forget to add this “new” user to new databases for your future projects.

MySQL optimization script

The PHP tutorial code is using the MySQLi class install of the older MySQL functions. MySQLi offers much more functions than the other MySQL extension. You will learn how-to connect and query the database using the MySQLi class and how-to use the MySQLi_Result class to parse your result set. My advise is to use the MySQLi class for future projects only.

$mysqli = new mysqli('localhost', 'optiuser', 'somepassword'); if ($mysqli->connect_error) < $errors[] = $mysqli->connect_error; > else < if ($result = $mysqli->query("SHOW DATABASES")) < while ($dbo = $result->fetch_object()) < $dbname = $dbo->Database; if ($dbo->Database != 'information_schema') < $mysqli->select_db($dbo->Database); if ($tableresult = $mysqli->query("SHOW TABLE STATUS")) < while ($obj = $tableresult->fetch_object()) < if ($obj->Data_free > 0) < if ($optresult = $mysqli->query(sprintf("OPTIMIZE TABLE %s", $obj->Name))) < $optimized[] = $dbname.'.'.$obj->Name; > else < $errors[] = $mysqli->error; > $optresult->close(); > > $tableresult->close(); > else < $errors[] = $mysqli->error; > > > $result->close(); > else < $errors[] = $mysqli->error; > > $mysqli->close();

First we create a new database object (MySQLi instance) using the login from the MySQL user we have created. Next we do a query to get all the database names for that specific user and store them in a database result-set. Inside the loop we select each database name to create a new query to receive the “tables” data for each database. We filter the result for the name “information_schema”, because this one is not a regular data table. We create a new while loop to parse the “tables” data for the “Data_free” value, if this value is greater than “0” we need to optimize that table. We optimize that specific table and store that table name inside the array called “$optimized”.

You might have noticed the array named “$errors” on several places with the code, we use that data for an error report at the end of the script.

Save the file under the name “optimize.php” and test it using the command line (if you have access to SSH) or try it inside the browser. My advise is to store this PHP script (after the test) above the public HTML directory to keep the database credentials safe.

Install a CRON job to optimize your databases

How-to setup the CRON job depends on how you can access your server, if you have access via SSH you can add this job directly to a crontab file (use “crontab -e”);

0 4 * * * /usr/local/bin/php /home/linuxuser/optmize.php

This job will execute the PHP script every morning at 4 o’clock.

Error and status reporting

We stored the errors and success messages inside the optimization script. Now we like to send them by mail after the PHP code was executed. Add the following code below the last “curly” bracket (end of the last “IF” clause):

$msg = ''; if (count($errors) > 0) < $msg .= 'Error\'s:'.PHP_EOL; $msg .= implode(PHP_EOL, $errors); $msg .= PHP_EOL; >if (count($optimized) > 0) < $msg .= PHP_EOL.'The following database table(s) are optimized:'.PHP_EOL; $msg .= implode(PHP_EOL, $optimized); >if ($msg != '') echo $msg;

This code will show the error messages (if available) and also a list of optimized tables. Because we put an “echo” into the code the message is send by to the users (local) mailbox. Often is this mailbox not monitored by the user and that’s why we use the following function to get use a custom e-mail address. Open again the crontab file and add this row at the top of all CRON jobs:

MAILTO=your@email-address.com

This short command will send any output which is generated by the CRON demon to your email address.

If you can’t enter the email address in the crontab file or using the control panel, it’s possible to use the “mail()” function inside the script. Replace the row with the “echo” command with this code:

mail('your@email-address.com', 'MySQL table optimization on '.date('Y-m-d'), $msg);

If you have any questions or comments don’t hesitate and post them using the form below. We appreciate if you share this article to Twitter or any other social media network you like.

Related Posts

How-to optimize JPEG images for websites Even if internet connections becoming faster and faster, it’s still important to keep your website as…

Optimize your DirectAdmin backups with rSync DirectAdmin offers some great backup which makes it possible to create a backup file for…

How-to choose a WordPress Hosting Provider If you’re using the popular blog tool WordPress, you’re probably looking for the best possible…

Источник