0

I am trying to reduce the size of my Magento database. I have followed the simple instructions in this article, which involves emptying Log files via phpMyAdmin, and enabling Log Cleaning via the Magento backend. http://docs.nexcess.net/article/magento-database-maintenance.html

From doing this my database was reduced from 1500MB to 500MB.

phpMyAdmin only allows me to import a database less than 50MB. Does anyone know how to reduce the database further or how to get around the problem of phpMyAdmin only allowing me to import a database less than 50MB?

Note: My site only have 44 products and has only been live 2 weeks. I am surprised the database is so big. Please also note that I am very much a rookie when it come to programming and especially databases.

Thanks

Prix
  • 19,417
  • 15
  • 73
  • 132
conor500
  • 109
  • 3
  • 9
  • Have you stop to consider that you can import your database using different methods? – Prix Mar 08 '14 at 16:58
  • Use a utility to transfer data from one db to another. Forget phpmyadmin. – Tarik Mar 08 '14 at 16:59
  • I'd check the tables and see where you have many records, significantly many. 44 products is not much. I know sites with thousands of db entries and they don't exceed a few megs. – NickOpris Mar 08 '14 at 17:01
  • Thanks Prix. What different methods would you suggest. Please remember I am a rookie. Thanks Tarik. Do you have any recommendations on good utilities to use. I am using a Mac. – conor500 Mar 08 '14 at 17:27
  • @tarik - yep, phpmyadmin is the slowest, most error prone way I've found between the GUI overhead, network timing issues and upload timeouts. Manual upload makes sure it all got there, commandline import is fast. Nothing like banging your head against the wall after the sixth partial upload. – Fiasco Labs Mar 11 '14 at 21:34

4 Answers4

2

This has always been a major problem for moving databases. The problem is it's easier to make dump files than it is to read them back in and, due to the upload restrictions in PHP, it's generally not something phpMyAdmin can help you with. While Steve Martin's solution can work for smaller tables, what if you have a larger table with thousands of rows?

The best and easiest solution is to read the file into mysql directly. Unfortunately, not everyone has access to mysql via the command line.

The next best solution I've found is to use a PHP parser to read the file and execute it. There's a parser you can use to help that along.

As to the main question of how to reduce the size, I'm not sure you can do that after a certain point, not without splitting your database and archiving off records. Sooner or later your database is going to get unwieldy.

Community
  • 1
  • 1
Machavity
  • 30,841
  • 27
  • 92
  • 100
  • This is a good answer as I also consider the best solution to be directly dumping with `mysqldump` and reading with `mysql` from the command line. The next best thing is the phpMyAdmin UploadDir and SaveDir functionality; upload a file to the webserver and have it appear from within phpMyAdmin. See http://docs.phpmyadmin.net/en/latest/config.html#cfg_UploadDir for specifics. – Isaac Bennetch Mar 09 '14 at 21:55
  • Machavity. Thank you for your answer. Your answer gives a solution to the problem as well as further detail on the topic. However, as I am very much a rookie, I found the solution difficult to follow as it was all new to me. Perhaps this would be the best solution for other people struggling with this problem, but for me I found Malachy's solution easier to figure out. Thanks again. – conor500 Mar 10 '14 at 08:29
0

In phpmyadmin export each table separately and the you can import them.

To do this - don't press export button until you have the DB open in PHPMyAdmin , then you will see all the tables highlighted on the export page, select one at a time and save it.

To import - make a new DB , open it and then press Import for each table

You will also then find out which table is big- magneto probably stores images from posts directly in the table.

enter image description here

Steven Martin
  • 3,150
  • 1
  • 20
  • 27
  • Thank you for your answer. In the end I found Malachy's solution the easiest to follow, but thanks for your help. – conor500 Mar 10 '14 at 08:30
0

Have you tried using MySQL Workbench? http://dev.mysql.com/downloads/tools/workbench

Although you should really figure out why it gets so big so fast.

NickOpris
  • 509
  • 2
  • 8
  • 20
  • Nick thanks for your help. I had a look at MySQL workbench, however being a rookie I was not familiar with anything it was saying. This solution could work for someone else reading though. – conor500 Mar 10 '14 at 08:32
  • Check this out https://dev.mysql.com/doc/workbench/en/wb-migration-overview-steps.html – NickOpris Mar 10 '14 at 12:08
0

phpmyadmin does not have a maximum import file size limitation.

The file size limit is set by PHP in php.ini and all you have to do is change that maximum and then you will be able to upload and import your large file - you can import a file of any size.

The location of your php.ini file depends on your flavour of Linux and your server set up but it should be under an /etc folder somewhere (also make sure you are editing the php.ini that corresponds to Apache's PHP settings, sometimes there is a php.ini for PHP on the command line that is different). Change or add these directives:

memory_limit = 512M
upload_max_filesize = 512M
post_max_size = 512M

Then restart your web server to apply these config changes and refresh your phpmyadmin import page. You will see the new max file size limit, and you will be able to upload your large file. Here is a screen shot from my server:

phpmyadmin file size limit is not a limit

Alternatively edit your .htaccess file to set the php configuration:

php_value upload_max_filesize 512M
php_value post_max_size 512M
php_value memory_limit 512M

(for htaccess your server needs to be configured to allow that but if you are on a server where you can't control php.ini you are going to find tuning Magento difficult. For servers running Suhosin patch you may also need to set suhosin.memory_limit=512M in suhosin.ini but I have never needed to do that)

Note also that phpmyadmin can import compressed SQL.

You may want to reduce the maximum upload file size after you have imported your file.

Regarding database size reduction, for the record, I run a store with 9000 products and the database has a total size of 458MB.

So I also recommend you use phpmyadmin to sort the tables by size and see what is dominating your database size.

(For more info on finding and changing php.ini to allow a larger upload file size see the answers and comments here)

Community
  • 1
  • 1
Malachy
  • 1,580
  • 1
  • 11
  • 9
  • Malachy. Thanks for this comment. I have tried following your instructions but I am unsure in certain areas. I will show you where I am getting to and hopefully you can advise me further. Currently my PHP import max is 32MiB. I have located the php.ini file in many different locations, but the two files that are relevant to my version of PHP (5.5.10) are located as follows: MAMP/conf/php5.5.10/php.ini and MAMP/bin/php/php5.5.10/conf/php.ini (continued below) – conor500 Mar 09 '14 at 09:29
  • Both files appear to be identical. In each file there is two areas of code that I think could be the correct code to edit. However I am not sure if I have the correct file. Please see the code I think I should be editing below. I think I should be editing "Maximum size of POST data that PHP will accept", but do I also need to edit the other area of code? (continued below) – conor500 Mar 09 '14 at 09:31
  • (line 230) max_execution_time = 30 ; Maximum execution time of each script, in seconds (Line 231) max_input_time = 60 ; Maximum amount of time each script may spend parsing request data (Line 232) memory_limit = 128M ; Maximum amount of memory a script may consume (8MB) (Next area of code below) – conor500 Mar 09 '14 at 09:34
  • (Line 372); Maximum size of POST data that PHP will accept. post_max_size = 32M – conor500 Mar 09 '14 at 09:34
  • Sorry for the long reply. Do you know which of those two files is the correct file, and which area of code is the correct line to edit. Thanks. – conor500 Mar 09 '14 at 09:36
  • Hi, yes, for simplicity just scroll to the end of both php.ini files and add in the three lines `memory_limit = 512M` `upload_max_filesize = 512M` `post_max_size = 512M` because they are last they will take precedence over any previous declarations. You might need to bump up the maximum execution times, yes, `max_execution_time = 300` and there is no harm in bumping up `max_input_time = 600` either. Add these 5 lines to the end of your php.ini files and remember to restart your server. – Malachy Mar 09 '14 at 10:06
  • For clarification, I am saying edit both files - that way you are bound to get the right one. To really know, look at your php_info() output. It will list which php.ini file Apache is using. (maybe there is a link to php_info() from your mamp dashboard) – Malachy Mar 09 '14 at 10:19
  • Malachy. Thank you for your help, that worked find. The correct file to edit was MAMP/bin/php/php5.5.10/conf/php.ini. Thanks again. – conor500 Mar 10 '14 at 08:23