Problem With Database File Size

Hello

The database of my website becomes: 1,491,305,979 bytes, very big, can't download on my local computer, but before was: 106,632kb only, and can download to my computer easily. Before having the file with sql.tgz only, but now have the sql more. Please see the image for more detail.

Does someone know how to change the database like before with small size please?

My website is multi-vendor 4.2.3.

Thank very much in advance!!!

Franck

problem with database file size.jpg

Hi Enew

From the small snap shot you have posted, it looks like you need to clear your cache.

Barry

Sorry

Admin>Administration>Storage>Clear Cache

Barry

Are you using 'database' as the caching method? Suggest that you do not do this.

You can also clear things like the cscart_new_orders table, cscart_user_sessions table, cscart_logs table, etc.

Our EZ Admin Helper addon has events that do this either on a scheduled basis or on-demand. Note that it will also do things like preserve log entries so you can retain 30 days of logs.

I would identify where your growth has been and determine if you can reduce those table sizes.

Open phpMyAdmin, sort table by size and check what tables caused the issue. If it is cscart_logs, you can clear it

Open phpMyAdmin, sort table by size and check what tables caused the issue. If it is cscart_logs, you can clear it

Hi, Ecomlabs

Can you show me how to open phpmyadmin and where to open this? I have clear the logs every month, and the log file is small.

Thank

Are you using 'database' as the caching method? Suggest that you do not do this.

You can also clear things like the cscart_new_orders table, cscart_user_sessions table, cscart_logs table, etc.

Our EZ Admin Helper addon has events that do this either on a scheduled basis or on-demand. Note that it will also do things like preserve log entries so you can retain 30 days of logs.

I would identify where your growth has been and determine if you can reduce those table sizes.

Hi Tbirnseth

I already have setting the clear cache on the server everyday, may be is another problem?

Thank

Sorry

Admin>Administration>Storage>Clear Cache

Barry

Hi Barry

I already have setting the server to clear the cache automatic every night, so I think may be is another problem.

Best!

hi

Open your cPanel look for icon with phpMyAdmin

Barry

Hi Tbirnseth

I already have setting the clear cache on the server everyday, may be is another problem?

Thank

You didn't answer my question. What caching method are you using in config.local.php? Database, file, etc.... Suggest you do NOT use database.

Can you show me how to open phpmyadmin and where to open this? I have clear the logs every month, and the log file is small.

Usually it is available in the control panel of your server (e.g. in cPanel)

Usually it is available in the control panel of your server (e.g. in cPanel)

Hi Ecomlabs

I have find the database file and sort by big size, please check the image for detail! Can you show me how to do for the next please?

https://ezlis.com/wp-content/uploads/2017/03/Enewwholesale-database-file-image.jpg

Thank!

Try doing these steps in phpMyAdmin (UNTESTED).

1) Select the SQL tab.

2) enter the following:

delete from cscart_stored_sessions WHERE expiry < unix_timestamp('2017-03-15');
delete from cscart_sessions WHERE expiry < unix_timestamp('2017-03-15');

3) click Go

This should remove all entries that are greater than 30 days old (or earlier than the date in the query).

Ultimately need to figure out why the sessions are not clearing automatically if they are old.

Yep, but one thing at a time....

Hi Ecomlabs

I have find the database file and sort by big size, please check the image for detail! Can you show me how to do for the next please?

https://ezlis.com/wp-content/uploads/2017/03/Enewwholesale-database-file-image.jpg

Thank!


Clear cscart_stored_sessions and cscart_sessions tables using previous posts. Then monitor the situation. If the size of the tables will grow up significantly again, contact CS-Cart support team

Clear cscart_stored_sessions and cscart_sessions tables using previous posts. Then monitor the situation. If the size of the tables will grow up significantly again, contact CS-Cart support team

Hi Ecomlabs, it's work now, thank very much for your help!

Hi Ecomlabs, it's work now, thank very much for your help!

You are welcome!

OK so I have big increase in db size, from ~100mb to 400mb

according to phpmyadmin cscart_stored_sessions seems to be largest one

done this

delete from cscart_stored_sessions WHERE expiry < unix_timestamp

used cenverter from here

https://www.unixtimestamp.com/index.php

majority of sessions start 158, means its from within month or maximum 2 months old. Got only couple hundreds of abandoned carts from this year (deleted older ones), also ran to optimise db but this has taken away just 20mb.

So how do I separate actual cart data from this junk ?

they will be removed automatically based on SESSIONS_STORAGE_ALIVE_TIME which (by default) is set to 2 weeks.

If you have older data in there, you might have a customization that is preventing the standard DROP condition from executing or possibly have increased SESSIONS_STORAGE_ALIVE_TIME.

Note also that the table user_session_products will relate to the stored_sessions table so if you manually delete things from stored_sessions, they will remain in user_session_products.