By Brian Holecko Certified Magento Developer at InteractOne
A large database can significantly slow down Magento development
When it comes to Magento, one hindrance is developing with a very large DB. This can slow down Magento development considerably making page load speeds grind to a crawl.
With a few techniques, you can reduce page load times and enhance security by removing unneeded data.
First, let’s make sure you are running the system log cleaners on a regular basis in all environments. Under System > Configuration > System > Log > Enable Log Cleaning, make sure this is set to Yes, and Save Log, Days is set to the minimum value required by the client for production systems and about 3 days for development. There is another log cleaner in Enterprise Edition which might be overlooked at System > Configuration > Index Management > Index Clean Schedule > Enable Scheduled Cleanup which should be set to Yes. These settings will prevent some of the most commonly oversized DB tables from filling up too quickly.
Second, make sure you are aware of magerun’s db:dump command. With the @trade and @stripped tags added to the command, you can export a Magento DB with dozens of tables sanitized of their data which will negligibly affect development. Examples include orders, customer, and report data.
Third, you may want to consider a custom shell script to delete large numbers of objects not needed for development. A large product catalog is a common source of slowing down Magento when it comes to page load and reindexing times, however, deleting this data with SQL commands can cause errors and should really be isolated to local development. If you are concerned about truncating tables without adding errors to the system, create a custom shell script to select the objects with a collection and delete them one-by-one. This will ensure only the correct tables are deleted from and will prevent SQL constraints from causing exceptions afterward.
With the above techniques, you should be able to develop without a bottleneck caused by the size of your DB as well as providing enhanced performance to your production environment.