[sqlite] Random performance issues with SQLite
Dear all, Just to give some feedback on this problem : I changed the HDD of my client, and configured his anti-virus to exclude the database directory. I don't know which one of those actions solved the problem, but it now seems to work like a charm. I will still wait a few days/weeks to be sure the problem does not come back, as it was quite random, but I am relatively confident. Thanks to all for your advices. Regards, Brice 2015-11-10 17:44 GMT+01:00 Scott Hess : > Also look for failures at the bus level. I have had cases where some > component was mucking up the bus, and got long pauses because the OS kept > resetting the bus. These caused huge pauses, like 30 seconds or more, and > due to how various OS components mostly do synchronous disk access, that > kind of thing can have pretty wide-ranging effects. > > [For Linux, usually the errors will show up in the dmesg output. No idea > where to look for other operating systems.] > > -scott > > > On Tue, Nov 10, 2015 at 1:52 AM, Simon Slavin > wrote: > > > > > On 10 Nov 2015, at 8:18am, OBones wrote: > > > > > However, using tools to read the SMART values for this harddrive, I > > noticed that it had been eating up spare sectors for quite a while. > > > > That's actually a good suggestion for you: look at the SMART values, run > > any hard disk diagnostics you have, etc.. Once you've backed-up, of > course. > > > > > So to answer, yes, it is definitely possible that the hardware is > > crashing and windows does not notice it. > > > > You will see errors that components report to Windows. But a dodgy > sector > > on a drive may just cause the driver to retry reading repeatedly (mystery > > delays) at the driver level, and be reported as an error only if reading > > fails completely. > > > > Simon. > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Random performance issues with SQLite
On 10 Nov 2015, at 8:18am, OBones wrote: > However, using tools to read the SMART values for this harddrive, I noticed > that it had been eating up spare sectors for quite a while. That's actually a good suggestion for you: look at the SMART values, run any hard disk diagnostics you have, etc.. Once you've backed-up, of course. > So to answer, yes, it is definitely possible that the hardware is crashing > and windows does not notice it. You will see errors that components report to Windows. But a dodgy sector on a drive may just cause the driver to retry reading repeatedly (mystery delays) at the driver level, and be reported as an error only if reading fails completely. Simon.
[sqlite] Random performance issues with SQLite
Brice Andr? wrote: > So, my question is the following : does someone has already experienced > performance issues on database with an HDD device starting to fail (without > any corruption problem) ? And if yes, is it possible that those performance > issues come with no notification in the windows log journal ? A few months ago I was (apparently) having random total freezes on my computer. Considering that it was when it got hotter and that I had a fanless graphics card, I replaced it with a fan based graphics card. There were no traces in the windows event log While it helped a bit, the issue came back but this time I noticed that it actually happened when accessing a particular file on one of the harddrives inside the computer. And yet, no traces in windows event log. However, using tools to read the SMART values for this harddrive, I noticed that it had been eating up spare sectors for quite a while. I replaced the drive, and haven't had any issue since then, when I was having an issue at least once a day before doing so. So to answer, yes, it is definitely possible that the hardware is crashing and windows does not notice it.
[sqlite] Random performance issues with SQLite
Also look for failures at the bus level. I have had cases where some component was mucking up the bus, and got long pauses because the OS kept resetting the bus. These caused huge pauses, like 30 seconds or more, and due to how various OS components mostly do synchronous disk access, that kind of thing can have pretty wide-ranging effects. [For Linux, usually the errors will show up in the dmesg output. No idea where to look for other operating systems.] -scott On Tue, Nov 10, 2015 at 1:52 AM, Simon Slavin wrote: > > On 10 Nov 2015, at 8:18am, OBones wrote: > > > However, using tools to read the SMART values for this harddrive, I > noticed that it had been eating up spare sectors for quite a while. > > That's actually a good suggestion for you: look at the SMART values, run > any hard disk diagnostics you have, etc.. Once you've backed-up, of course. > > > So to answer, yes, it is definitely possible that the hardware is > crashing and windows does not notice it. > > You will see errors that components report to Windows. But a dodgy sector > on a drive may just cause the driver to retry reading repeatedly (mystery > delays) at the driver level, and be reported as an error only if reading > fails completely. > > Simon. > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Random performance issues with SQLite
Dear all, Thank you very much for those advices. I will try them and get back if it does not solve the issue. Regarding the advice of Simon on HDD starting to fail, I have one more question. In fact, I suspected this, as my client has other problems with his computer. But, in such case, I expected to get logs in Windows Events Journal. I scanned those logs and did not see any logs regarding Hdd problems or related stuff. So, my question is the following : does someone has already experienced performance issues on database with an HDD device starting to fail (without any corruption problem) ? And if yes, is it possible that those performance issues come with no notification in the windows log journal ? Thanks in advance, Brice 2015-11-09 17:39 GMT+01:00 Simon Slavin : > > On 9 Nov 2015, at 3:21pm, Brice Andr? > wrote: > > > So, does someone has an idea on what can cause such performance issues on > > some sqlite DB requests ? > > Since you > > 1) are getting delays and not errors, and > 2) you are using very common OS and hardware, and > 3) you are accessing a database from an internal disk, > > I strongly suspect that any fault is not in SQLite. From experience this > sort of problem is caused by either > > A) an anti-virus package or > B) a hard disk which is just starting to fail and will get worst until it > fails completely > > Diagnosis and fixes should relate to trying a different disk or > temporarily disabling the anti-virus software, not to anything inside > SQLite. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Random performance issues with SQLite
On 2015/11/09 6:33 PM, Brice Andr? wrote: > OK, but if this scenario was at the origin of my problem, I guess I would > have such a log each time a db query freezes ? > > If yes, then there is probably another problem, as I am sure I got such > freezes with no such logs from sqlite. > > By the way, if the antivirus is at the origin of the problem, which > practical solution may I find to the problem ? I cannot request my client > to deactivate his antivirus. And he is using a very common one (norton). > So, I would probably not be the only one to have this problem ? Actually, you are not the only one. This problem has been discussed here a lot and is very common. Keeping updates in transactions may help, but a lot of the time you have no control over how many times a user requests things from the DB. Having the Antivirus exclude the folder (or DB file at a minimum) usually helps for us. (It's part of our systems' documentation even). All this assumes it /IS/ in fact the anti-virus causing the problem - you need to check that first. Best of luck! Ryan
[sqlite] Random performance issues with SQLite
OK, but if this scenario was at the origin of my problem, I guess I would have such a log each time a db query freezes ? If yes, then there is probably another problem, as I am sure I got such freezes with no such logs from sqlite. By the way, if the antivirus is at the origin of the problem, which practical solution may I find to the problem ? I cannot request my client to deactivate his antivirus. And he is using a very common one (norton). So, I would probably not be the only one to have this problem ? Is there a way to request SQlite to don't release control of the file between two operations ? As I only open one descriptor, this could maybe help if it was possible ? Maybe I could ask sqlite to take control of the file at the beginning of my program, and request it to release control at the end ? Thanks for your help, Regards, Brice 2015-11-09 17:26 GMT+01:00 Richard Hipp : > So the problem is occurring on a call to GetFileAttributesEx(). > > I'm guessing that this is an adverse interaction with an anti-virus > (AV) program. > > The scenario might be something like this. > > (1) SQLite writes one small part of a complex change to the database file > (2) The AV seizes control and scans the entire database, taking a long > time to do so (because the file is large) and denying SQLite access > while it does. > (3) The AV finally releases control of the file > (4) Goto 1. > > > On 11/9/15, Brice Andr? wrote: > > Dear Richard, > > > > Thanks for your answer. The version of sqlite I am using is the 3.9.1 > > amalgamation. > > > > But, as stated above, I am not sure my freeze problem is linked to the > > strange log observed at my client as this log does not systematically > > appears on database queries that freeze. Those logs sometimes appear on > > queries that do not freeze, and sometimes, queries that freeze do not > have > > those logs. > > > > I checked in the different logs I have collected, and it seems to be > always > > the same line (39428) that causes this strange log. > > > > Regards, > > > > Brice > > > > 2015-11-09 16:36 GMT+01:00 Richard Hipp : > > > >> On 11/9/15, Brice Andr? wrote: > >> > > >> > 08-11-2015 (13h25): Exception: SQLiteDataBase : Error callback -> 27 : > >> > delayed 25ms for lock/sharing conflict at line 39428 > >> > >> The "line 39428" refers to a specific source code line number in the > >> "sqlite3.c" file, which in turn depends on exactly which version of > >> SQLite you are running - information which you failed to share with > >> us. If you will let us know which version of SQLite you are running, > >> we might be able to figure out what is going wrong. > >> -- > >> D. Richard Hipp > >> drh at sqlite.org > >> ___ > >> sqlite-users mailing list > >> sqlite-users at mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Random performance issues with SQLite
On Mon, Nov 9, 2015 at 4:41 PM, R Smith wrote: > All this assumes it /IS/ in fact the anti-virus causing the problem - you > need to check that first. Just let him run with the AV disabled for a while or ignore the folder and try to replicate the problem. However, I would **uninstall the program completely**, as many AVs like to keep some things running even when they are "disabled". -- Bernardo Sulzbach
[sqlite] Random performance issues with SQLite
Dear Richard, Thanks for your answer. The version of sqlite I am using is the 3.9.1 amalgamation. But, as stated above, I am not sure my freeze problem is linked to the strange log observed at my client as this log does not systematically appears on database queries that freeze. Those logs sometimes appear on queries that do not freeze, and sometimes, queries that freeze do not have those logs. I checked in the different logs I have collected, and it seems to be always the same line (39428) that causes this strange log. Regards, Brice 2015-11-09 16:36 GMT+01:00 Richard Hipp : > On 11/9/15, Brice Andr? wrote: > > > > 08-11-2015 (13h25): Exception: SQLiteDataBase : Error callback -> 27 : > > delayed 25ms for lock/sharing conflict at line 39428 > > The "line 39428" refers to a specific source code line number in the > "sqlite3.c" file, which in turn depends on exactly which version of > SQLite you are running - information which you failed to share with > us. If you will let us know which version of SQLite you are running, > we might be able to figure out what is going wrong. > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Random performance issues with SQLite
On 9 Nov 2015, at 3:21pm, Brice Andr? wrote: > So, does someone has an idea on what can cause such performance issues on > some sqlite DB requests ? Since you 1) are getting delays and not errors, and 2) you are using very common OS and hardware, and 3) you are accessing a database from an internal disk, I strongly suspect that any fault is not in SQLite. From experience this sort of problem is caused by either A) an anti-virus package or B) a hard disk which is just starting to fail and will get worst until it fails completely Diagnosis and fixes should relate to trying a different disk or temporarily disabling the anti-virus software, not to anything inside SQLite. Simon.
[sqlite] Random performance issues with SQLite
Dear all, I experience some performance issues with SQLite. I have an application that uses SQLite to store user data, including agenda information. One of my client has a relatively big database (around 1.3Gb). I have indexed the database so that the queries take a reasonable time to execute. In the general case, the complete update process of the more demanding tasks (handling of user action, search in database and screen refresh) takes less than 1 second. But, at very sparse moments, for reasons I cannot identify, one of my clients experiences freezes in the application that can raise up to several minutes ! After those freezes, the application continues to work normally I instrumented my application to find the origin of the freeze. From this instrumentation, I can guarantee that the time is spent in the database request. But what is strange is that executing exactly the same request, with exactly the same parameters on exactly the same database content just after, or just before the problem occurs does not cause any performance issue. I logged all information on what was happening on my client database (I currently only have one client that encounters this strange behaviour), and the only strange thing that I collected from his computer is the following kind log from sqlite : 08-11-2015 (13h25): Exception: SQLiteDataBase : Error callback -> 27 : delayed 25ms for lock/sharing conflict at line 39428 08-11-2015 (13h25): Exception: SQLiteDataBase : Error callback -> 27 : delayed 75ms for lock/sharing conflict at line 39428 27 is the SQLIte error code, and the text located after is the sqlite message. The occurrence of such warnings do not seem to be direcly linked to my problem, as they can appear on database access that do not suffer from performance issues, and some database access with performance issues can appear without those strange logs. But, for me, those logs are strange because : - I never encountered them on other computers (even if it's true that I did not pay attention to those logs before this problem was reported by my client) - my application is supposed to be the only process to access the database, and only one thread of my application is accessing the database. So, does someone has an idea on what can cause such performance issues on some sqlite DB requests ? Thanks in advance, Brice PS: the problem is present at a client computer running a Windows 7 over a relatively old computer (intel core 2 duo) with 4G ram. The version of sqlite is the last one (because I upgraded hoping this would solve the issue), but was also observerd with an older version of sqlite.
[sqlite] Random performance issues with SQLite
On 11/9/15, Brice Andr? wrote: > OK, but if this scenario was at the origin of my problem, I guess I would > have such a log each time a db query freezes ? > > If yes, then there is probably another problem, as I am sure I got such > freezes with no such logs from sqlite. > > By the way, if the antivirus is at the origin of the problem, which > practical solution may I find to the problem ? I cannot request my client > to deactivate his antivirus. And he is using a very common one (norton). > So, I would probably not be the only one to have this problem ? > > Is there a way to request SQlite to don't release control of the file > between two operations ? As I only open one descriptor, this could maybe > help if it was possible ? Maybe I could ask sqlite to take control of the > file at the beginning of my program, and request it to release control at > the end ? > If you run your large update inside a transaction, it should run to completion without getting interference from AV software. -- D. Richard Hipp drh at sqlite.org
[sqlite] Random performance issues with SQLite
So the problem is occurring on a call to GetFileAttributesEx(). I'm guessing that this is an adverse interaction with an anti-virus (AV) program. The scenario might be something like this. (1) SQLite writes one small part of a complex change to the database file (2) The AV seizes control and scans the entire database, taking a long time to do so (because the file is large) and denying SQLite access while it does. (3) The AV finally releases control of the file (4) Goto 1. On 11/9/15, Brice Andr? wrote: > Dear Richard, > > Thanks for your answer. The version of sqlite I am using is the 3.9.1 > amalgamation. > > But, as stated above, I am not sure my freeze problem is linked to the > strange log observed at my client as this log does not systematically > appears on database queries that freeze. Those logs sometimes appear on > queries that do not freeze, and sometimes, queries that freeze do not have > those logs. > > I checked in the different logs I have collected, and it seems to be always > the same line (39428) that causes this strange log. > > Regards, > > Brice > > 2015-11-09 16:36 GMT+01:00 Richard Hipp : > >> On 11/9/15, Brice Andr? wrote: >> > >> > 08-11-2015 (13h25): Exception: SQLiteDataBase : Error callback -> 27 : >> > delayed 25ms for lock/sharing conflict at line 39428 >> >> The "line 39428" refers to a specific source code line number in the >> "sqlite3.c" file, which in turn depends on exactly which version of >> SQLite you are running - information which you failed to share with >> us. If you will let us know which version of SQLite you are running, >> we might be able to figure out what is going wrong. >> -- >> D. Richard Hipp >> drh at sqlite.org >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] Random performance issues with SQLite
On 11/9/15, Brice Andr? wrote: > > 08-11-2015 (13h25): Exception: SQLiteDataBase : Error callback -> 27 : > delayed 25ms for lock/sharing conflict at line 39428 The "line 39428" refers to a specific source code line number in the "sqlite3.c" file, which in turn depends on exactly which version of SQLite you are running - information which you failed to share with us. If you will let us know which version of SQLite you are running, we might be able to figure out what is going wrong. -- D. Richard Hipp drh at sqlite.org
[sqlite] Random performance issues with SQLite
I am not a user of Norton but I expect it has the same features as others. In Windows Defender you can specify files to exclude and that may help. *Jim Dodgen* On Mon, Nov 9, 2015 at 8:44 AM, Richard Hipp wrote: > On 11/9/15, Brice Andr? wrote: > > OK, but if this scenario was at the origin of my problem, I guess I would > > have such a log each time a db query freezes ? > > > > If yes, then there is probably another problem, as I am sure I got such > > freezes with no such logs from sqlite. > > > > By the way, if the antivirus is at the origin of the problem, which > > practical solution may I find to the problem ? I cannot request my client > > to deactivate his antivirus. And he is using a very common one (norton). > > So, I would probably not be the only one to have this problem ? > > > > Is there a way to request SQlite to don't release control of the file > > between two operations ? As I only open one descriptor, this could maybe > > help if it was possible ? Maybe I could ask sqlite to take control of the > > file at the beginning of my program, and request it to release control at > > the end ? > > > > If you run your large update inside a transaction, it should run to > completion without getting interference from AV software. > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
