Database sizes whats acceptable

[puffdade]

Hi All

The topic is slightly open I know as its very much dependant on so many factors  but im concerned that mine is excessive and possibly leading to some slowing up of the shopping cart

Im currently running 1.6.1.24  yes I know caveman addition  but its running sweet its still relativly quick im getting reasonable GT Metrix scrores  but like most of us we strive for more  I started looking at the database an area I have no knowledge of other than without it the Cart wouldnt exist.

My current stats are as follows

 

902mb  seems excessive  but to give you an idea of my current Cart setup and stats

I currently have 2700 products around 1200 of those currently have combinations some quite heavily loaded with as many as 12 combinations  I have plenty of pictures  a minimum of 2 per product and in most cases a seperate image for each combination,  

I have around 1/4 of a million orders on the system so thats a 1/4 of a million invoices etc etc 

Around 18,000 Customers

I regularly clear the logs, abandoned carts, old emails, customers with no address's etc.

 

So my question is for anyone with database experience,

Is there anything I can do to shrink that baby down  and maybe get that little extra speed that we all dream of, does my DBase seem ok size wise given the amount of data im trying to store

would I benefit from any future upgrades (database) not Presta Version

 

Thanks for looking

 

Paul

 

 

[Guest]

All you have to do is truncate table of the ps_connection and ps_guest tables.
There will be millions of records.

[puffdade]

11 hours ago, 4you.software said:

All you have to do is truncate table of the ps_connection and ps_guest tables.
There will be millions of records.

 

Thank you very much  

[puffdade]

I have truncated those  and its made a small difference  

I have sorted the tables by size and  have posted below,  most of the large tables  seem legit to me, Im wonderig if its a case of its just a big busy site

can you see anything  in this list that maybe shouldnt be there or I could do anything with to shrink it down

 

Regards

Paul

 

 

[Guest]

I see a large number of orders.
There is an option to export and delete old orders.
E.g. you select all orders until 31.12.2019 and export them via a php script to, for example, an XML file or create an SQL file for each order.
Due to the large number, a script must be written that will be run repeatedly.

ps_smarty_cache_bkp is probably some backup of the ps_smarty_cache table

 

[JBW]

Do you had really >190.000 orders? If not something is going wrong here... If yes, congratulations, seems to be a well running shop and 900MB seems reasonable size for this amount of order data.

ps_smarty_cache_bkp sound like backup of Smarty Cache on DB, is this option still activated? If not you might want to backup this table into a file and remove it from DB

[puffdade]

1 hour ago, JBW said:

Do you had really >190.000 orders? If not something is going wrong here... If yes, congratulations, seems to be a well running shop and 900MB seems reasonable size for this amount of order data.

ps_smarty_cache_bkp sound like backup of Smarty Cache on DB, is this option still activated? If not you might want to backup this table into a file and remove it from DB

On this installation the number of orders is correct  however we had some problems with a 1.5 version when i first started using PS  and lost around 50k order history (my inexperience with cart software) and had to do a total rebuild in 2014,  so this particular business has around 250k orders since end of 2012 im quite proud of it despite all the sticky tape holding the cart together    and if im totally honest would have given up on PS if it wasnt for the help of you guys on this forum.

i looked at ps-smarty-cache-bkp   and considered if this was a backup entry in the database and after digging down into the entry  there have been no additions to this table since 13-8-20  this also coincides with a major issue we had when we got hacked and we had to dig deep and do some major fixing of the site, 

I have added a snippet below sorted by date (newest entries at the top), im far from an expert hence my question here and if those entries are safe to delete  thats 100mb saved.

 

 

Edited December 17, 2020 by puffdade (see edit history)

[puffdade]

2 hours ago, 4you.software said:

I see a large number of orders.
There is an option to export and delete old orders.
E.g. you select all orders until 31.12.2019 and export them via a php script to, for example, an XML file or create an SQL file for each order.
Due to the large number, a script must be written that will be run repeatedly.

ps_smarty_cache_bkp is probably some backup of the ps_smarty_cache table

 

 

This is something I would really like to do as I only need to keep 2 years of orders on the system and each year I could repeatedly remove the oldest year into an archive on my NAS,  

is this something you would be prepared to write for me im happy to pay your going rate 

the thought of me sitting here selecting small segments  saving  and archiving each segment gives me bad vibes  I could easily mess up and thats something I don't want, 

 

Regards

Paul

[Guest]

I created a module for Paul that can:

• select date from / to
• create a folder where it will be saved to save
• create a subfolder with the order creation date
• save the sql file according to the order reference number
• run Cron task once (runs repeatedly automatically)
• security token
• low server load
• PrestaShop 1.6

[puffdade]

Daniel Thanks for all the help you Sir are a superstar 

4you.software  made me a module that I have been running over the last couple of days  i still have a little more to do  but  its knocked  200+ mb off the size of my database so far  its still work in progress for me as im so busy trying to keep the business fully operational which is difficult with our government forever changing the Covid rules ( our retail stores forced closed yet again from midnight last night)

This module is just magic  it happily runs along in the background  and my 3 retails stores operate with no issues  no speed constraints what so ever

Daniel is a database guru  during our shared screen I watched him just flow through proceedures and I learnt bundles just watching,  so much so I have started reading up on SQL although at 55 yrs old  I may be a bit late for that  . simple things like database backup to see it done the right way made all the hairs stand up on the back of my neck (I have been doing it wrong for 7 years on Presta).

I still cannot find any resources online regarding ps_smarty_cache_bkp  the table hasnt had an entries since 13/8/20 and only has data from 2017 so im presuming module related  and its now unused and possibly surplus to requirements  so im thinking of removing that as thats another 15% saving

 

comparison to image i posted at the start of the thread,  as you can see its reduced drastically,

 

Daniel thank you for the module your time and being on the end of a pc 

 

[Guest]

I gladly helped.
You can safely delete the ps_smarty_cache_bkp table.

[El Patron]

1. when not sure to delete something and want to find out possibly if used
2. rename it
3. wait x(x) days....
4. no problems?  then backit up and delete it...

(tip) we are recommending https://www.fasthosts.co.uk/dedicated-servers an ovh.....why?  see nvme, this significantly reduces data transfer speeds and reduces ttfb...waiting on VPS nvme...still waiting even though it might be very well under the hood on aggressive vps hosters.  ask you hosting provider 

Happy selling puffdaddy

[puffdade]

"UPDATE"

 

Nice trick El Patron

 

Database is under 500mb  now  nearly half its old size,   

Edited December 27, 2020 by puffdade (see edit history)

[El Patron]

21 hours ago, puffdade said:

"UPDATE"

 

Nice trick El Patron

 

Database is under 500mb  now  nearly half its old size,   

Email me via website and I can have Daryl do a free mysql performance review and changes.  Even if not current client you still in our bubble.  Happy new year