Database Size Increased

Our database usually sit around the 48,000,000 bytes region, however in the last week it has increased to 163,000,000.



We clean out the logs, statistics and site optimised weekly, but this hasn't made much difference.



When a back-up is carried out, it sticks for a while at cscart_store_location_desscriptions before finishing.



Just wondered if anyone had any thoughts.



Many Thanks

[quote name='The Joker' timestamp='1393786782' post='178590']

Our database usually sit around the 48,000,000 bytes region, however in the last week it has increased to 163,000,000.



We clean out the logs, statistics and site optimised weekly, but this hasn't made much difference.



When a back-up is carried out, it sticks for a while at cscart_store_location_desscriptions before finishing.



Just wondered if anyone had any thoughts.



Many Thanks

[/quote]

Try going to administration/Database/Maintenance and Optimize database link top right. should make a difference.



Alan

Alan,



I have tried that and it hasn't made any difference

Customers abandoned carts ?

Keep that at the bare minimum.

Not the same problem but was at 3.06 then installed a new store and imported the products and customers but did not import the orders and previous database was around 60Megs and now the new one which does not have all the products and orders is at 72Megs



400 less products and 1200 orders not in new one.



Hope there is not an issue or is this version just larger.

Using Ultimate with 1 store.

I would use phpMyAdmin and look at table sizes. When you see one that is unsually large, try to identify what it's used for.



Note that in V4, the store import process brings forward tables of incompatible addons so it's possible you just have a bunch of garbage sitting there…

[quote name='tbirnseth' timestamp='1398127331' post='182030']

I would use phpMyAdmin and look at table sizes. When you see one that is unsually large, try to identify what it's used for.



Note that in V4, the store import process brings forward tables of incompatible addons so it's possible you just have a bunch of garbage sitting there…

[/quote]



[size=4]Looks like

[color=#000000][font=sans-serif]cscart_stored_sessions is 65megs[/font][/color]

[color=#000000][font=sans-serif]I have cleared cache and log files and user carts still same.[/font][/color]

After doing some more reading about this table it seems this might be common and someone suggested

to open mysql and enter

[color=#000000]TRUNCATE cscart_stored_sessions[/color][/size]



Update

Just did that and my database is now down to 4megs.

Is this something that needs to be done all the time?

Or did I just screw my database up.

Scott

No, you just destroyed any active user sessions! :-)

It used to be managed by “clear statistics”. And there should be something in the code that removes all sessions older than 2 weeks or so.



You should have looked at the timestamps and determined how old the rows were before deleting them… It might also tell you if you have a lot of old stuff that is not getting removed properly.

This new store install is only about 3 weeks old so not really sure what might be in there. Will keep an eye on it and see what happens.



Not that comfortable in sql but will browse around to see that happens with it.



Thanks for your help.

Scotty

Maybe you just have lots of visitors. I.e. if you have 100/day and sessions are kept for 30 days and everyone comes back every day then the session info will never expire (which is a good thing for your customers).



Having a large DB in not necessarily a bad thing in and of itself. However, having lots of unused/useless info that has to be scanned for each page request will cause performance issues. If you saved 65M then that's a pretty paltry amount of data to be concerned about. A well constructed set of tables/indexes can scan through that in a few milliseonds.

I have the same problem. my cscart_stored_sessions table is already 300MB large. Is there no way to delete it from admin panel???[color=#333333] [/color]

You can delete rows via a query. However, the system should be deleting all rows that are older than what's defined as SESSIONS_STORAGE_ALIVE_TIME (defined in config.php) which by default is set to 2 weeks.

Well Im not sure it deleted any since I moved to 4.2…This table alone is already at 500MB.

So I just delete all the rows from phpmyadmin and all will be ok?



Dont understand why CScart didnt add delete button to admin panel as well!!!

Maybe a bot is doing the job. We had the same problem and a robot was crawling thousand pages per day.

When a new session is created, all the old entries are deleted. You'd have to look at the timestamp column to determine how old the entries are. 500MB is a lot for that table which would lead me to believe you have other issues, ie. bots hammering your site or other intrusion attempts.

So how do I check this? And should I just delete entries in the table from phpmyadmin?

Suggest you do something like:


SELECT session_id FROM cscart_stored_sessions where expiry < (unix_timestamp() - (60*60*24*30));


This will return all the table entries that have timestamps > 30 days old. If that looks okay to you then you could


DELETE FROM cscart_stored_sessions where expiry < (unix_timestamp() - (60*60*24*30));




But if you have a lot of sessions returned from the 1st query, you might want to have cs-cart helpdesk investigate WHY before you destroy it.



If you still have a big table and it only contains 30 or less of history (1st query returns nothing), then like I said, you probably have other issues and this is just an indicator…

Hi Tony, We have BIG problem with cscart_stored_sessions db growing to >1GB.

Im creating an adaptation of PHP code from another post,

cscart_stored_sessions - General Questions - CS-Cart Community Forums

and have created a quick link to the PHP code. in admin.

But Im no PHP coder, so could you show me how to apply this code into the PHP file?

Removal of old session data should be done at each login (admin, customer, vendor) and should be part of the system.

Did you run the first SELECT querey to see how many entries you have that are more than 30 days old?



If you do and it comes back as empty (none) then the system is behaving as designed. And if it shows data that is more than 30 days old, then you should submit to bugtracker and open a helpdesk ticket so they can address the problem. Otherwise it will just be there again in the next release.



If you want help with this specific issue, contact me through the link in my signature.