Hi all,
after almost 2 yrs of using CSC 4.3.7 Im facing growing database. Usualy I do delete logs which helps to save few megs, but database "order_data" is growing continuosly without possibility to trim or lower.
Firstly I tought I can lower this database when deleting older orders, but size of order_data lowers only little and when look into database I can still see even the oldest order numbers from beggining of eshop.
Database optimization from backup/restore menu does not make any difference on this particular database and this keep growing, actualy 150MB.
Do you have any idea how to solve this?
Many thanks, Marek
Why do you think its too big? Cs db is actually pretty well structured and performs well on a properly configured server. what is the problem you are trying to solve. You can always delete old orders if you choose to.
Why do you think its too big? Cs db is actually pretty well structured and performs well on a properly configured server. what is the problem you are trying to solve. You can always delete old orders if you choose to.
ok thanks for aswer, ...my eshop has 300MB limit for whole database and last few months Im balancing on 80-90% of this limit. This limit is kind of VIP size for my provider and I cant expect this limit will be rised easily, so I would rather stay in this limit. Thats why I need to get lower.
As I mentioned I deleted almost half of all orders (thousands) and order_data was lowered only by about 10-20% and when I open the database I still can see entries numbered with order numbers I deleted. Im suspicious if the database is realy getting rid of old data...
Did you use the fn_delete_order() function (or the admin UI) to delete you orders?
What are the sizes (rows and total size) of the tables (orders, order_data, order_details)? Note that for each order you can have up to maybe 5 or more different 'type's of order_data (shipping, taxes, promotions, addons, etc.) You also have 'order_details' which relates to the line items (products) within the order.
Did you use the fn_delete_order() function (or the admin UI) to delete you orders?
What are the sizes (rows and total size) of the tables (orders, order_data, order_details)? Note that for each order you can have up to maybe 5 or more different 'type's of order_data (shipping, taxes, promotions, addons, etc.) You also have 'order_details' which relates to the line items (products) within the order.
Im using admin UI for deleting orders (dont know how to use fn_delete_order)
tables...
orders 3,9MB
order_data 149MB
order_details 10,9MB
all tables seems to have all orders data from beginning of eshop, even those I deleted from admin UI.
any idea what to do with it?
thanks
Im using admin UI for deleting orders (dont know how to use fn_delete_order)
tables...
orders 3,9MB
order_data 149MB
order_details 10,9MB
all tables seems to have all orders data from beginning of eshop, even those I deleted from admin UI.
any idea what to do with it?
thanks
orders 3,9MB 11215 rows
order_data 149MB 56854 rows
order_details 10,9MB 27012 rows
The relationship in number of rows between the tables looks reasonable. 5X order_data, average number of products per order is about 2.4.
I guess if you have a store that has 11215 orders AFTER you've purged orders that a hosting environment that restricts you to 300MB of database size may not be the proper type of hosting plan for your environment.
My suggestion would be to invest in having a developer (like me or others on here) create an order-archiver for you that would export the SQL data for the orders (all tables) and then delete the data related to that order based on a "prune to [date]" type of interface. This would allow you to re-import orders from a particular period if you ever needed to get back to the original data (like litigation or chargeback issues).
But I still think you have the wrong hosting plan/provider for your needs.
The relationship in number of rows between the tables looks reasonable. 5X order_data, average number of products per order is about 2.4.
I guess if you have a store that has 11215 orders AFTER you've purged orders that a hosting environment that restricts you to 300MB of database size may not be the proper type of hosting plan for your environment.
My suggestion would be to invest in having a developer (like me or others on here) create an order-archiver for you that would export the SQL data for the orders (all tables) and then delete the data related to that order based on a "prune to [date]" type of interface. This would allow you to re-import orders from a particular period if you ever needed to get back to the original data (like litigation or chargeback issues).
But I still think you have the wrong hosting plan/provider for your needs.
...agree with provider thing and understand that database archivation would be way to go, but I still dont understand why database tables contains data of manualy deleted orders...? Should I understand this as normal CSC behaviour, then why there is possibility to delete orders? or this is unusual behaviour and I should care about fixing it...
...agree with provider thing and understand that database archivation would be way to go, but I still dont understand why database tables contains data of manualy deleted orders...? Should I understand this as normal CSC behaviour, then why there is possibility to delete orders? or this is unusual behaviour and I should care about fixing it...
I don't think it does or the orders have not in fact been deleted. As I said, the number of orders you have on your site is 11215. Suggest you go in and delete 10 more and verify that the number does indeed drop by 10 or you have a different issue.
ok I doublechecked deleting of orders one by one on both my eshops and it is afterwards deleted from database right. There is only about 20-30 "ghost" orders still "sleeping" in database in one of my eshops, but not appearing in admin. So my main question is solved for now.
Thanks for helping me.
Have you any suggestion about "right" hosting for CSC type of eshop?
I would use a VPS (cloud or server based) with 4-6GB memory, several CPU cores and at least double what you think you need for SSD drives.