Does the increasing Order History create query speed problems?

[skipunda]

Hi,

My Server info (/admin/server_info.php) always hits 25% and above load at peak times. Unfortunately it is only 1% at off-peak times which would otherwise pointed at a specific problem. I have read up on many contributions and threads (pls don’t post me any more!) but I would like to share with you a breakdown of what’s on my database as a different take on things.

 

Downloading the database used to be around 30 meg. Now it is 80 and climbing by 15-20 per month. My concerns are that this is a big contributor to the server load problem. The data is also scattered i.e. tables are defined in one place but data actually written in several other places. This means to pull this data you would have to search these fragmented areas. A cause for concern?

 

 

The graph is pretty accurate but summarizes many functions.

 

As a side note: If I'm adding 15-20 meg of order history can I store this on a different database altogether?

[♥geoffreywalton]

Follow my useful thread link below on this,

 

It covers optimisation

 

G

[skipunda]

Follow my useful thread link below on this,

 

It covers optimisation

 

G

 

Hi Geoffrey,

Thanks for your reply. I have seen most of these threads already...they are very useful. Couldn't find anything specifically to do with the amount of Order History and where improvements could be made here.

Do you agree that migrating this part of the database to it's own, dedicated database is the way forward or is a 500meg database consisting of mainly old orders very normal?

[MrPhil]

500MB database? That's getting fairly big for MySQL. My hosting service recommends that we keep each database down to 400 to 500 MB or less, for best response times. I'm not an SQL expert, so I don't know if there's anything you can do in the way of "cleaning" the big tables or re-indexing in some way that would help.

 

Does your order history table include customers who are inactive or long gone? Maybe you could delete entries more than X months old or something? The table should have a "date" field, so in theory you could do an SQL query to DELETE FROM orders_status_history WHERE date_added < some_cutoff_date. Be VERY careful, back up your database first, test on a scrap table (or copy of a real one) first, and consider whether there are any flags in the table indicating that this order is still active somehow and should be preserved. Maybe someone more familiar with the innards of osC can talk to this point.

 

If the overall database is becoming too large, there may be other tables (especially logs) that can be cut back or even truncated (emptied). That may improve processing speed.

[skipunda]

500MB database? That's getting fairly big for MySQL. My hosting service recommends that we keep each database down to 400 to 500 MB or less, for best response times. I'm not an SQL expert, so I don't know if there's anything you can do in the way of "cleaning" the big tables or re-indexing in some way that would help.

 

Does your order history table include customers who are inactive or long gone? Maybe you could delete entries more than X months old or something? The table should have a "date" field, so in theory you could do an SQL query to DELETE FROM orders_status_history WHERE date_added < some_cutoff_date. Be VERY careful, back up your database first, test on a scrap table (or copy of a real one) first, and consider whether there are any flags in the table indicating that this order is still active somehow and should be preserved. Maybe someone more familiar with the innards of osC can talk to this point.

 

If the overall database is becoming too large, there may be other tables (especially logs) that can be cut back or even truncated (emptied). That may improve processing speed.

 

I actually keep a copy of orders on a separate system altogether using xml for a variety of reasons. The only reason I would be interested in keeping people's old orders is for account history that the customer can look up. I wonder how very large companies such as Ebuyer.com do it?

 

Oh yeah, 500MB, well thats only 2 years away. Maybe that could be the 'cut off'...1 years worth of orders per customer

[♥geoffreywalton]

There is a good thread on optimisation which is what you may want to do.

 

Having a large no of order is only of relevance when selecting data to use/display from this table. Having 2.8 million records sitting in a table not being referenced is not a problem to mysql.

 

If you are reading a specific record such as an order header then that should be v quick. Listing all orders between 2 dates will be slower as every record in the table has to be read.