SambaPOS v5.1.6 very Sluggish

Turn off windows defender. One of my clients systems was very sluggish because defender was taking up too much cpu

Edit… I’m not saying it’ll fix your problem but it might help.

1 Like

Ok. Windows defender makes sense…

Another thing i found out was in my Windows Event Viewer, The SQL server is starting up the database pretty much all the time…every 10-15 minutes there is a log for SQL server starting a DB. is that normal ?

I checked your database. You added 1 second refresh to Ticket Lister in delivery screen. That is too much. Either increase it to 30 seconds or setup message server to auto refresh when tickets creates.

gpedit.msi

applications I think it was, then windows defender

Click Turn Off Windows Defender.

Check ENABLE, apply and OK

Thanks Emre for looking into my DB.

I found out one tiny setting causing a major issue. The SQL server was Auto-Closing the DB, and forcing a new load everytime a query was made. Fixed that in the DB properties in SQL express, and the sluggishness seems to be gone.
Dont know why SQL Server Express decided to make that the default setting.

3 Likes

Please show a screenshot of that setting so others might check for and correct that issue.

2 Likes

Also can you share with us what version of SQL you are running? Is it 2016? 2014? I cant find any setting related to that… but perhaps its called something else and I am not recognizing it. I also have not noticed mine doing that…

I am on SQL Express 2016 basic install with mixed mode and sa user enabled.

I am running event viewer and making transactions etc and its starting it up once. So are you referring to GQL only?

Hmm testing GQL its not doing it either… whats unique about your calls thats causing that? It literally started the database once … I made multiple orders on SambaPOS and on my GQL PMPOS app and it never closed and opened it again.

EDIT: Ok I think I found it… its definitely not enabled by default:

Menu > Tools > Options in MSSMS

People should check that though if they experience this.

EDIT2: I am not convinced this setting is related to the server though. I think that is only related to Queries ran from MSSMS I need to test that.

3 Likes

Ok I tested that setting… its like I thought its not related to server it is only affecting queries ran in MSSMS. I still can not find a setting relating to this on the server additionally I cant find behavior that is consistent with it starting and stopping the db on every query. It is definitely not doing this.

After some time of inactivity it seems it closes the connection but its certainly not doing it every query. I tested with SambaPOS and with GraphQL and once the database was started it never restarted. The only time it seems to disconnect is after inactivity which makes sense to me.

I just cant find any settings related to this for the the actual server… @digithai I am definitely interested in which setting you are referring too.

I was thinking the same thing too. That particular setting is only related to SSMS, and has nothing to do with other Apps. I would really like to know where the setting is that is mentioned by @digithai

Me too.

Ok I found it… and reading it… I think you should leave that enabled.

This is Database specific not server setting.

Notice Auto Close.

This is an old article and it advocates for not enabling this setting.

@emre what is your thought? Is this something you designed SambaPOS to enable? if so why?

I want to point out that even WITH this setting enabled it is certainly not closing on EVERY query. It has a timeout feature.

“By default, databases created by SQL Server aren’t set to AutoClose by default (unless your model database has been explicitly set to enable AutoClose)”

"And, in the case of databases that were designed to be deployed on mobile laptops without full-blown ‘server’ capabilities, I think you COULD argue that it sort of makes sense that AutoClose might be beneficial. "

That last quote makes sense to me.

Yes, from the following thread, this seems to be the case, having to to with SQL Express SKUs, indicating that DBs are by default created with Auto-close turned on. We should be able to use the setting shown by @Jesse, or you can change the setting with T-SQL …

ALTER DATABASE <dbname> SET AUTO_CLOSE OFF;

@emre, it might be a good idea to ensure that SambaPOS executes the above setting when a new DB is created.


EDIT: confirming the T-SQL statement does what it should, I executed the following, then checked the DB Properties to verify Auto-Close is turned off …

ALTER DATABASE [SambaPOS5] SET AUTO_CLOSE OFF;

3 Likes

From what I am reading the only reason for enabling this is to save resources… useful in dev environments and maybe very slow computers… but I cant find compelling reasons to leave it enabled.

This might solve a lot of issues. Thank you @digithai for finding that.

1 Like

Now I guess the right thing to do would be to test just how much resources it uses with this turned off. Since resources is the only thing at stake. I have a feeling with SambaPOS its minimal if at all and we should always turn Auto Close off.

EDIT: Quick test with running multiple tickets and operations in SambaPOS as well as PMPOS build using GraphQL its staying at 164.3 MB max for the database when running queries. When not running anything its dropped to 86mb. Thats on my machine. 0% CPU for most part.

I think that should be just fine for keeping this setting turned off. I am certainly going to do that on my production server. I will keep it on for my dev machine…

EDIT: Interesting tidbit and I probably know why because PMPOS Is not doing nearly the stuff SambaPOS is doing but… PMPOS never caused it to use more than 88mb… SambaPOS used as much as 164.3

1 Like

I did post regarding this a few refreshes ago, showing that GQL appeared to be consuming more and more memory with each execution, even through GraphiQL, indicating that “garbage collection” was not quite performing properly. @emre said he would check that to see if it could be tweaked. I will run some more tests to see if this is changed for the better.

EDIT: this is the post in Beta …

That particular Report when called via GQL still continues to consume memory and does not appear to release it. Before running it, the Message Server was at 48 MB, and after running the GQL a few times, it is now up to 186 MB

Actually that was a bad test. I had SambaPOS open during that… with sambaPOS closed and running PMPOS doing multiple actions including refreshing the app over and over… it never budged past 88mb.

EDIT: I stand corrected… Just did some tests… let me do more before I speak too quickly again.

Hmm now I am starting to wonder if this would be the reason for Auto Close. Surely there is a better method?

@QMcKay I just tested… SQL EXpress does NOT set Auto Close to true by default… SambaPOS is doing that when it creates the DB. I just created a DB in MSSMS and it was set to False by default. I think we need @emre to look at this and explain some things :stuck_out_tongue:

1 Like

It seems eventually the memory is released from Message Server. Not sure what triggers this (timed, sessions, etc), but it had consumed upwards of 300 MB and just now it dropped to 52 MB … so there is some type of maintenance going on here.

On the other hand, SQL Server continues to eat memory… it started at 252 MB it is currently 633 MB.

1 Like

I noticed that about SQL Server. So for time being I set the Auto Close back to true… but I think we could (should) find a better approach.

I found this its interesting… I may try a few of the ideas but I wonder if it will impact performance?

I am inclined to think that SambaPOS is managing that but GQL is not. So perhaps emre needs to look at something there?

Wow… Thank you very much for looking to that. So what do you think about Auto Close? I don’t specifically enable that but framework I’m using to create database may enable that so sounds like it might be a better idea to keep that as is…

I strongly believe that issue primarily relates with 1 sec. refresh on ticket lister. If he have multiple terminals (seems he have) that will create a lot of network traffic.

Don’t care memory a lot. Windows 10 seems to behave differently on memory consumption. On task manager memory reported to be in use by SQL Server or SambaPOS might not be actually used. I can slowdown message server consumption a little bit by manually disposing stuff but sooner or later it will be released by GC as that memory not really referenced. However I’ll keep my eye on it to ensure that does not fully consume memory and lead to issues.

3 Likes

Thats right!
That is the setting i found… Sorry about the delayed response, im a bit ahead on timezone (GMT +7)

I turned that Autoclose Off in the DB settings, and it seemed to help a bit.

I guess its because our system can have 20-30 minute gaps between orders at times, and that allows the sql server to timeout and close.
Eventually i started to suspect windows being a culprit too, so ran a few updates, upgraded graphics adapter. Also allotted more virtual memory for windows, disabled all power saving options and got rid of any background processes that i felt i could do without… so far so good.

will let the day pass before i give a proper review on whether it helped.