1

Yesterday, my store completely down with this critical error

Can't connect to local MySQL server through socket '/tmp/mysql.sock'

I contacted hosting company, they said error is due to HDD is full and my mysql data is over 36 GB

and they also said 'core_cache' table have 26 GB data size

(Last week, when I dumb the database, I got 3 ~ 5 GB sql file)

enter image description here

While I'm checking, I found mysql data suddenly become 9 GB

enter image description here

So my store become work again since there is hdd space

When I check mysql data today, it is 14 GB few hour ago, and now it's 16 GB it keep increase the size

I think when my hdd become full due to the mysql size again , I will get critical error and store will be down

Can I know why mysql data is so big and keep increase, big size core_cache tables , what should I check..? or solution

( innodb_version | 5.6.19 )

Appreciate any answer or comments!

UPDATE - Most space is taken by index tables (like core_cache, core_url_rewrite, catalog_product_index_eav_idx and so on)

We have around 90,000 SKU products (group + simple)

Tables ! | Size in MB

m_core_cache | 8453.14 |

m_core_url_rewrite | 896.05 |

m_catalog_product_index_eav_idx | 280.88 |

m_catalog_product_entity_varchar | 231.56 |

  • Are you storing sessions in the db? – mbalparda Feb 22 '15 at 12:27
  • @mbalparda Just checked /var/session folder, I think session files is stored in the folder since there are many files created recently '<session_save><![CDATA[files]]></session_save>' on app/etc/local.xml – Moon Feb 22 '15 at 13:23
  • 1
    What version of Magento? If it's 1.7 or 1.8, there are a few bugs related to the core_url_rewrite table growing exponentially on product updates and adds. You can truncate the core_url_rewrite table and re-index, but if you have any manual rewrites in there, they will be lost, so be careful if you choose this route. Also, make sure log cleaning is set to one day at most (System -> Configuration -> System -> 1 day, enabled). I've sen logging tables with 10 million+ rows. – Rob Mangiafico Feb 22 '15 at 16:08
  • 1
    @RobMangiafico It's 1.8.1 I just found our Enable Log Cleaning setting is 'No' (I remember it was 'Yes' before no idea why it's changed) I just set 'Yes' and daily Hope it works Great point! Thank you!! – Moon Feb 22 '15 at 16:25
  • Added an answer to detail setting up crontab job and the enable log cleaning. I'm assuming you set up crontab, but added it for other's use. Additional in the answer is that Magento includes shell utilities you can use to help expedite this so you're not waiting for the once a day that cron will run the log clean, since you probably want the website up and running sooner. – Fiasco Labs Feb 22 '15 at 18:03
3

Magento needs to have log_ table cleaning enabled, website traffic will build up a horrific amount of data in all the log_ tables. Two steps are needed:

  1. you must have a crontab entry to run cron.sh or cron.php at least every 15 minutes. This is the cron trigger that serves as the heartbeat for Magento's internal cron processes. Sample lines in crontab to run.

    */5 * * * * /bin/sh /absolute/path/to/magento/cron.sh

    or

    */5 * * * * /absolute/path/to/bin/php /absolute/path/to/magento/cron.php

  2. you must go to System->Config->Advanced->System to complete the Log Cleaning setup.

enter image description here

For manual cleaning, you can use the command line shell/ utilities. SSH into your document root and run:

php shell/log.php clean

Given the sheer quantity of junk it sounds like you have built up, expect it to error out with a memory error. You might try adding the --days switch and figure out how old your website is and slowly reduce the number so it's not trying to process so much at once.

php shell/log.php clean --days 360

The following will tell you the log tables status.

php shell/log.php status
-----------------------------------+------------+------------+------------+
Table Name                         | Rows       | Data Size  | Index Size |
-----------------------------------+------------+------------+------------+
log_customer                       | 3.34K      | 180.22Kb   | 98.30Kb    |
log_visitor                        | 46.52K     | 4.21Mb     | 0 b        |
log_visitor_info                   | 46.57K     | 8.40Mb     | 0 b        |
log_url                            | 82.49K     | 5.26Mb     | 5.26Mb     |
log_url_info                       | 84.05K     | 12.60Mb    | 0 b        |
log_quote                          | 527        | 49.15Kb    | 0 b        |
report_viewed_product_index        | 115.99K    | 6.83Mb     | 23.35Mb    |
report_compared_product_index      | 0          | 16.38Kb    | 81.92Kb    |
report_event                       | 79.56K     | 5.78Mb     | 16.20Mb    |
catalog_compare_item               | 0          | 16.38Kb    | 81.92Kb    |
-----------------------------------+------------+------------+------------+
Total                              | 459.04K    | 43.35Mb    | 45.07Mb    |
-----------------------------------+------------+------------+------------+
  • Regarding logging, aside from the tables, checking /var/log might show some massive system.log or exception.log (or other third-party log files, I see an FPC log on a site I work on get to 400Mb in just a few hours without a way to disable logging in the module). These are safe to remove, but if you are seeing them get quite large in just a couple of days, you should investigate what is being logged to them and fix those issues. – pspahn Feb 24 '15 at 18:39
  • Wow! On an unrelated note, another one to watch is var/report, had a question the other day about a newly integrated API module that was throwing so many errors, it was constantly running the website out of diskspace. Time to break out the diagnostics. – Fiasco Labs Feb 24 '15 at 20:33
5

You are using the database as cache back end, I wouldn't recommend that. It's not the most performant alternative and there are some bugs. Relevant read with a possible workaround: http://fbrnc.net/blog/2011/09/magentos-database-cache-backend-mess

If possible, switch to Redis as cache backend, otherwise you still can use the default: "files".

The cache backend is configured in app/etc/local.xml

  • How can I make not using database as cache back end? or switch to Redid? I checked your link but it's hard to understand to me as store owner.. It's weird there is no issue like this for months but suddenly this happen installing some module can make change database cache setting? Thank you for your answer!! – Moon Feb 22 '15 at 13:20
  • 1
    First, find <backend>database</backend> below <cache> in app/atc/local.xml and replace it with <backend>file</backend> to use the default file cache. Then truncate the core_cache table and check if the directory var/cache in Magento contains subdirectories with a new timestamp. Also, core_cache should stay empty now. – Fabian Schmengler Feb 22 '15 at 15:37
  • 1
    For Redis, see briansnelson.com/Install_Redis_Caching_for_Magento - if you are on Magento CE 1.8+ it's just configuration, for older versions you must install an extension. Also your server has to run a Redis database (ask your hoster) – Fabian Schmengler Feb 22 '15 at 15:38
  • Just checked local.xml There is no line like <backend>database</backend>, (I had <session_save><![CDATA[files]]></session_save>), Checked the /var/cache folder, it have subdirectories and files with a new timestamp. I think my cache is stored as files in the ftp – Moon Feb 22 '15 at 15:51
  • 1
    One such module that might write to the core_cache table is an FPC (front page cache). Check in app/etc for .xml files besides local.xml if you have an FPC module installed. It will have the location where the caching is being done. – Fiasco Labs Feb 22 '15 at 21:16
1

In one of my installations, the table 'core_cache' was growing up to 13GB. After researching, found Amasty full page cache has a configuration file at app/etc/amfpc.xml forcing to use database for storage.

<!-- Storage configuration -->
        <options>
            <backend>database</backend><!-- apc / memcached / xcache / empty=file -->

Just clear the value in the backend tag and this fixed the problem. Will use file mode.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.