Reducing DB size by deleting Transactions


#21

Good points to bring up. The speed to recover a database I am pretty sure would not be an issue. The longest it might take is couple minutes at most. For curiosity sake I think I will test this myself.

As far as uploading to a DropBox that is a specific need specific to your setup. I am not sure if keeping database size small so it can upload on slow internet is a foundation to base a design on. However maybe we could explore other things that might help with this situation.

@emre what about an ability to break up large backups into smaller pieces? Maybe a way to backup selected Date Ranged data? For example backup only 2014 data…

Yes this is what I stated. I am saying why does it have to be this way? It doesnt. We need to leverage this in a way that SambaPOS users can use it to improve their business. I am saying we should attempt to make it user friendly so that SambaPOS allows its users to do something the competition can not.


#22

@kendash

IT best practices regarding backups are one local and one offsite backup. My IT career is related to IT infrastructure design and IT disaster recovery, so I test backups and restores very frequently.

Sometimes, I think, my day job kicks in with everything I touch related to computers. Maybe I am over-thinking!

Just my 2 cents. :smile:


#23

I am not saying what you do is a bad practice its certainly not. Also a good practice is to have the data easily accessible and currently a DROP box that relies on slow internet is not easily accessible. A simple Jump Drive can be offsite.

That said again I am not saying your method is a bad practice. I am saying as a design stand point I agree with @emre we should not delete data. Maybe we should explore other backup methods.


#24

I went and looked at my old system. The entire drive has no fragmentation at all, because I had been running nightly defrags. :wink: Now my new system is on SSD, and I don’t do defrag at all. Is there such a thing on SSD? I think not. Bottom line - I no longer have any way to test this. Hopefully someone else can take a look into it.

No, since I don’t delete anything from them, the Shrink processes achieves no significant reduction.


It appears that we can agree that performance is not an issue. At least not yet, and it won’t be for quite some time. This was my main concern, and what really prompted me to ask the questions.

That said, there is still the question of portability (small file size), and no matter your intent for your DB Backup file, this is something that should be considered.

At this time however, an archival solution (Warehousing) isn’t something that we should spend any more time discussing, since it should require awareness from SambaPOS - and there are more important features to be built and tested in the near future.


#25

I agree with this. And I think you covered this @na1978 when you said

Our focus should be on improving other things right now.


#26

SSD’s have no fragmentation issues. I too use SSD’s so I have no way to test this either. In fact if you do defrag an SSD you will severely limit its lifespan. Because of the way it stores data in Blocks. It would have to erase the block and write it again.

For those wondering what the big deal with fragmentation is. For magnetic HD’s they have an actual ARM that reads the data. Defragging physically puts the data for the files closer together so it reduces the distance and # of times the arm has to physically move to retreive this data.


#27

Ive recently been getting complaints about small delays with sql. Current server is running on a i5 6400 cpu and 8gigs of ram and a ssd for the c drive and a separate ssd for the sql database. The database is currently around 2.2gigs and is running on windows 10 pro with sql 2014 express. Not sure what to do other than rebuild the indexes which helps for a little but not for long. Any pointers?


#28

Delays when, doing what?
Most general POS stuff doesn’t require delving into transaction history.
Knowing events surrounding lag may help diagnose causes beyond just database size.


#29

Logging in, adding items to tickets and sending the tickets to the kitchen. Its like a one to two second delay switching screens. Its not crazy bad but noticeable and it wasn’t like this when they first started out. It seems to be the last year or slow its just getting more delayed.


#30

Any custom report tags used in entity screens?


#31

Would this effect aspects outside of entity screens? Login and adding orders to new ticket?


#32

I can review your database if you can send me a backup.


#33

When I profile it with an SQL profiler I noticed no database related issues. It should work fine. When memory usage is more than %90 you may encounter memory allocation issues with SQL Server. You can try Program Settings > Maintenance > Auto Size > Safely Auto Size columns. Please create a backup before using it and test if it works fine for your setup before applying to production.

One thing I noticed is button images are taken from a network location. If you’re having delays while displaying navigation screen moving images to local drive may improve performance.

Also Setting up a 30 minute cache for Weather widget may also help.


#34

Ok so I made those changes. Another thing I discovered was on the terminals in the local settings database name was blank. The IP and instance and said username and password were there but not the database name so I added that and it seemed to be more responsive after. Also has anyone noticed a performance increase with using the latest sql express version over 2014?


#35

Yes I notice a performance increase with latest version.


#36

I also got a big increase when I did the safe mode autosize in fact @emre added that when I complained of similar issues on my food truck terminal.


#37

Is that with sql 2017 or does it also work well with sql 2014?


#38

Yes 2017 is what I use. My food truck terminal is one of the terminals that I resell its running a celeron j1900 4gig ram and a non SSD HD. It was freezing quite a bit and having little delays. When I used the Safely Auto Size all of that pretty much disapeared.


#39

How long does the auto sizing typically take? Also what does it do exactly. Just curious to learn.


#40

Well just to post a update. Things have noticeably gotten much faster after setting all images to a local source as well as running the database auto size. I am still having a issue on one terminal. I have two terminals that are identical hardware cpu, ram, motherboard, ssd. The problem im having on one of them is that when taking a order sometimes samba seems to freeze but doesn’t. Basically the program is on the order entry screen I can click buttons but the program does nothing. It doesn’t enter any orders and cant close the ticket to go back to the main menu. Samba does not crash and windows is fine I can hit ctrl alt del and kill samba and open it again. Its like it looses connection but ive ran a continuous ping to the server and im not dropping any packets. It is using a wired connection as well its just very bizarre. This also only happens when taking a order.