MySQL Large Tables - Cleaning Up
Permalink
Hi Everyone,
I've been having speed issues with my site recently so I've been doing some cleaning up. The site has about 7000 members and has ecommerce installed, though not much selling going on there have been four orders to date.
I've been optimising the database today. The database size is about 560mb and most of that is two tables:
QueueMessages - 3 million rows - 300mb
CoreCommerceOrders - 5 million rows - 200mb
I would be grateful if anyone knows what can be done to reduce these two tables, without affecting privacy and so on. For example, I read that just deleting entries from CoreCommerceOrders can lead to people's invoices being populated with other people's data. However, I don't want 200mb of space being taken up for four orders! I'm assuming that because I have the cart visible on each page it is generating the null entries in the database for everyone who logs in, each time.
Something feels wrong about the size of these two tables and they have mostly null entries so I hope someone can advise how to reduce them and prevent them from bloating up again.
Many thanks,
Mike
I've been having speed issues with my site recently so I've been doing some cleaning up. The site has about 7000 members and has ecommerce installed, though not much selling going on there have been four orders to date.
I've been optimising the database today. The database size is about 560mb and most of that is two tables:
QueueMessages - 3 million rows - 300mb
CoreCommerceOrders - 5 million rows - 200mb
I would be grateful if anyone knows what can be done to reduce these two tables, without affecting privacy and so on. For example, I read that just deleting entries from CoreCommerceOrders can lead to people's invoices being populated with other people's data. However, I don't want 200mb of space being taken up for four orders! I'm assuming that because I have the cart visible on each page it is generating the null entries in the database for everyone who logs in, each time.
Something feels wrong about the size of these two tables and they have mostly null entries so I hope someone can advise how to reduce them and prevent them from bloating up again.
Many thanks,
Mike
Bump. Speculation anyone?
I think with the CoreCommerceOrders you can delete all the rows where oStatus = 0 but if there is somebody who is currently shopping on your site that could cause issues for them, I would strongly suggest making a database backup before you change anything.
As for the QueueMessages table I don't know what that is used for, none of my sites have any records in it. The only file I find with reference to it is concrete/libraries/Zend/Queue/Adapter/Concrete5.php
As for the QueueMessages table I don't know what that is used for, none of my sites have any records in it. The only file I find with reference to it is concrete/libraries/Zend/Queue/Adapter/Concrete5.php
If you delete null status orders, there is a glitch in eCommerce such that any pending carts that are in a browsers' cookies will lock up and prevent purchases being made from that browser.
hutman and JTF,
Thanks for your responses, I'm still trying to sort this and have asked the ecommerce team to look at this thread.
The following thread also points out issues with deleting old orders, especially the last post in the thread.
https://www.concrete5.org/marketplace/addons/ecommerce/support/clear...
I would really like to clean out several million rows from the table that aren't worth keeping for four orders (that were made many months ago). People don't often clear their cookies so that concerns me from what John says and the post I linked to talks of security issues too. Maybe I need the core ecommerce team to edit the code?
I still can't figure what QueueMessages does but again a few million rows in the database. If they are forum posts or something like that I don't want to mess with them.
JTF - I'm making inroads with the host, speed and SSL issues and I'm plugging away at those in my limited capacity so that I can leverage the work you're going to do for me without you suffering from ridiculous speeds. The SSL / https was relatively easy when I got into it so your skills are better suited to more complex things I'd like to achieve.
Suggestions from anyone still appreciated!
Many thanks,
Mike
Thanks for your responses, I'm still trying to sort this and have asked the ecommerce team to look at this thread.
The following thread also points out issues with deleting old orders, especially the last post in the thread.
https://www.concrete5.org/marketplace/addons/ecommerce/support/clear...
I would really like to clean out several million rows from the table that aren't worth keeping for four orders (that were made many months ago). People don't often clear their cookies so that concerns me from what John says and the post I linked to talks of security issues too. Maybe I need the core ecommerce team to edit the code?
I still can't figure what QueueMessages does but again a few million rows in the database. If they are forum posts or something like that I don't want to mess with them.
JTF - I'm making inroads with the host, speed and SSL issues and I'm plugging away at those in my limited capacity so that I can leverage the work you're going to do for me without you suffering from ridiculous speeds. The SSL / https was relatively easy when I got into it so your skills are better suited to more complex things I'd like to achieve.
Suggestions from anyone still appreciated!
Many thanks,
Mike
Following Greg's instructions on clearing out orders is probably your best bet to reducing the number of stale orders in your database.
The QueueMessages table is tied to the Queues table and hold the individual instructions for queue based jobs and other queue based things. I'd check go to the dashboard and hit the "Reset all running jobs" button and see where that gets you.
Best wishes,
Korvin
The QueueMessages table is tied to the Queues table and hold the individual instructions for queue based jobs and other queue based things. I'd check go to the dashboard and hit the "Reset all running jobs" button and see where that gets you.
Best wishes,
Korvin
Very old thread I know - But - I just had an issue with an 5.6 legacy site where the QueueMessages table had reached a ridiculously large size (over 50mb). After backing everything up, I took a deep breath and truncated/emptied the table, and there are no issues.
I am not sure what it does beyond what has been written above, and obviously make sure backup your own database before doing anything to your site - but I thought I'd add this here as a reference as I can't find anything else about this issue in the forums or elsewhere online, so hopefully it is of some help.
I am not sure what it does beyond what has been written above, and obviously make sure backup your own database before doing anything to your site - but I thought I'd add this here as a reference as I can't find anything else about this issue in the forums or elsewhere online, so hopefully it is of some help.
