Difference between revisions of "Magento Database Optimization - Log Cleaning Script"

From Brian Nelson Ramblings
Jump to: navigation, search
(Created page with "==Magento Database Optimization - Log Cleaning Script== Has your Magento store been running slower and slower. When was the last time you did a real log cleaning and databas...")
 
(Log Cleaning to Speed up your Site)
 
(14 intermediate revisions by the same user not shown)
Line 4: Line 4:
  
 
===Log Cleaning to Speed up your Site===
 
===Log Cleaning to Speed up your Site===
 +
 +
'''<span style="color: red">DOES NOT WORK WITH MAGENTO 2</span>'''
  
 
The following tables are accessed during normal operation of your Magento store.  Some of the logs files are access with every page view, and write/reading to them can dramatically slow down your site when they get large in size.
 
The following tables are accessed during normal operation of your Magento store.  Some of the logs files are access with every page view, and write/reading to them can dramatically slow down your site when they get large in size.
Line 29: Line 31:
 
* dataflow_batch_import
 
* dataflow_batch_import
  
After cleaning out those logs, I have seen a site gain over 5 seconds to their load times.
+
'''Additional Enterprise tables need'''
 +
 
 +
* enterprise_logging_event
 +
* enterprise_logging_event_changes
 +
 
 +
After cleaning out those logs, I have seen a site gain over 5 seconds off their load times.
  
 
===Optimization/Repairing your Database Tables===
 
===Optimization/Repairing your Database Tables===
Line 41: Line 48:
 
Basic walk-through of what the script will do.
 
Basic walk-through of what the script will do.
  
1) Put your site into maintenance mode - Important step to prevent database corruption.
+
# Put your site into maintenance mode - Important step to prevent database corruption/increase the speed and reduce locking of tables
2) Truncates the above tables
+
# Truncates the above tables
3) Repairs any tables that are in need of repair
+
# Repairs any tables that are in need of repair
4) Optimize tables/recreate + analyze
+
# Optimize tables/recreate + analyze
5) Takes your site out of maintenance mode
+
# Takes your site out of maintenance mode
  
 
Also creates a log file in your home directory, letting you know whats going on in the background
 
Also creates a log file in your home directory, letting you know whats going on in the background
Line 53: Line 60:
 
database-optimized-04-25-14-22:26.txt
 
database-optimized-04-25-14-22:26.txt
  
Link to [http://briansnelson.com/stuff/magento-db-optimize.script Download the script]
+
* Link to Community Version - [http://briansnelson.com/stuff/magento-ce-db-optimize.script Download the script]
 +
* Link to Enterprise Version - [http://briansnelson.com/stuff/magento-ent-db-optimize.script Download the script]
 +
 
 +
The only difference between Community and Enterprise is we truncate 2 additional logging tables.
  
 
'''This script needs to reside in your sites top level directory'''
 
'''This script needs to reside in your sites top level directory'''
Line 59: Line 69:
 
===Install via the Command Line===
 
===Install via the Command Line===
  
  wget http://briansnelson.com/stuff/magento-db-optimize.script
+
  wget http://briansnelson.com/stuff/magento-ce-db-optimize.script
  chmod +x magento-db-optimize.script
+
  chmod +x magento-ce-db-optimize.script
 +
 
 +
or
 +
 
 +
wget http://briansnelson.com/stuff/magento-ent-db-optimize.script
 +
chmod +x magento-ent-db-optimize.script
  
 
Now Lets run the script
 
Now Lets run the script
  
  ./magento-db-optimize.script
+
  ./magento-ce-db-optimize.script
  
 
<sub>'''-- Have not tested this on Magento 1.4 or older'''</sub>
 
<sub>'''-- Have not tested this on Magento 1.4 or older'''</sub>
 +
 
===Sample Output===
 
===Sample Output===
  
Line 127: Line 143:
 
This script can also be ran from a weekly cron
 
This script can also be ran from a weekly cron
  
  crontab -u {your user} -e
+
  crontab -e
  
 
Then add the following line for every Sun at 5AM
 
Then add the following line for every Sun at 5AM
  
 
  0 5 * * 0 /bin/bash /path/to/html/magento-db-optimize.script
 
  0 5 * * 0 /bin/bash /path/to/html/magento-db-optimize.script
 +
 +
===Helps with the following===
 +
 +
magento speed, magento mysql optimization, magento is slow, magento slow performance, magento mysql, magento loading slow

Latest revision as of 18:26, 1 January 2016

Magento Database Optimization - Log Cleaning Script

Has your Magento store been running slower and slower. When was the last time you did a real log cleaning and database optimization. Doing those two things will ensure that your Magento store will stay running smoother for longer.

Log Cleaning to Speed up your Site

DOES NOT WORK WITH MAGENTO 2

The following tables are accessed during normal operation of your Magento store. Some of the logs files are access with every page view, and write/reading to them can dramatically slow down your site when they get large in size.

When I talk about log cleaning I mean truncating the following tables:

  • core_cache
  • core_cache_option
  • core_cache_tag
  • core_session
  • log_customer
  • log_quote
  • log_summary
  • log_summary_type
  • log_url
  • log_url_info
  • log_visitor
  • log_visitor_info
  • log_visitor_online
  • index_event
  • index_process_event
  • report_event
  • report_viewed_product_index
  • report_compared_product_index
  • dataflow_batch_export
  • dataflow_batch_import

Additional Enterprise tables need

  • enterprise_logging_event
  • enterprise_logging_event_changes

After cleaning out those logs, I have seen a site gain over 5 seconds off their load times.

Optimization/Repairing your Database Tables

I know that Magento uses alot of innodb tables and innodb tables do not use optimization but when your run the command it will recreate + analyze the table. This can help increase your page load times and database access speeds.

Bash Script to Clear Logs and Optimize Magento Tables

The bash script does everything except make a database backup, which I recommend doing anytime you make any changes to your database.

Basic walk-through of what the script will do.

  1. Put your site into maintenance mode - Important step to prevent database corruption/increase the speed and reduce locking of tables
  2. Truncates the above tables
  3. Repairs any tables that are in need of repair
  4. Optimize tables/recreate + analyze
  5. Takes your site out of maintenance mode

Also creates a log file in your home directory, letting you know whats going on in the background

Example of the log file, look for them in your home directory, feel free to delete them but can be used for debugging.

database-optimized-04-25-14-22:26.txt

The only difference between Community and Enterprise is we truncate 2 additional logging tables.

This script needs to reside in your sites top level directory

Install via the Command Line

wget http://briansnelson.com/stuff/magento-ce-db-optimize.script
chmod +x magento-ce-db-optimize.script

or

wget http://briansnelson.com/stuff/magento-ent-db-optimize.script
chmod +x magento-ent-db-optimize.script

Now Lets run the script

./magento-ce-db-optimize.script

-- Have not tested this on Magento 1.4 or older

Sample Output

=============================
Using Database: magento1501
Magento Version: 1.5.0.1
Database Size:  15.40 MB
Written by Brian Nelson @ http://briansnelson.com
Download at http://briansnelson.com/stuff/magento-db-optimize.script
=============================

Setting maintenance.flag ..
Setting Maintenacne Flag... [OK] 

Optimization Tables ...
=============================

Truncating Log tables ..
[1/20] Truncated: core_cache ... [OK]
[2/20] Truncated: core_cache_option ... [OK]
[3/20] Truncated: core_cache_tag ... [OK]
[4/20] Truncated: core_session ... [OK]
[5/20] Truncated: log_customer ... [OK]
[6/20] Truncated: log_quote ... [OK]
[7/20] Truncated: log_summary ... [OK]
[8/20] Truncated: log_summary_type ... [OK]
[9/20] Truncated: log_url ... [OK]
[10/20] Truncated: log_url_info ... [OK]
[11/20] Truncated: log_visitor ... [OK]
[12/20] Truncated: log_visitor_info ... [OK]
[13/20] Truncated: log_visitor_online ... [OK]
[14/20] Truncated: index_event ... [OK]
[15/20] Truncated: index_process_event ... [OK]
[16/20] Truncated: report_event ... [OK]
[17/20] Truncated: report_viewed_product_index ... [OK]
[18/20] Truncated: report_compared_product_index ... [OK]
[19/20] Truncated: dataflow_batch_export ... [OK]
[20/20] Truncated: dataflow_batch_import ... [OK]

Repairing Tables ..
Repairing Tables: ... [OK]

Optimizing Tables ..
Optimizing Table: ... [OK]

=============================

Removing maintenance.flag ..
Removing Maintenance Flag... [OK]

New Database Size ..
Database Size:  15.40 MB

Check Site to Make sure its Working...
=============================
Click Here -> {base_url}

Setting Up a Weekly Cron

This script can also be ran from a weekly cron

crontab -e

Then add the following line for every Sun at 5AM

0 5 * * 0 /bin/bash /path/to/html/magento-db-optimize.script

Helps with the following

magento speed, magento mysql optimization, magento is slow, magento slow performance, magento mysql, magento loading slow