Very Slow delete times on larger databases, please help!

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Very Slow delete times on larger databases, please help!

Allan, Mark
We are experiencing incredibly slow delete times when deleting a large number of rows:-

We are using SQLite on an embdedded platform with an ARM7 processor, 2Mb RAM and 32Mb NOR Flash memory 25Mb of which is allocated for storage for our database.

There are 3 tables in the database but the two we are concerned with here are created as follows:-

CREATE TABLE EXAMINATIONS (
        'EXAM_ID' integer PRIMARY KEY AUTOINCREMENT,
        'PATIENT_ID' varchar(15) NOT NULL,
        'STATUS_FLAG' smallint,
        'DATE' timestamp,
        'EXAM_TYPE' smallint,
        'DATE' timestamp,
        'EXAM_TYPE' smallint,
        'HEIGHT' smallint,
        'WEIGHT' smallint,
        'DYSPNOEA_SCORE' smallint,
        'NOTES' varchar(450),
        FOREIGN KEY (PATIENT_ID) REFERENCES PATIENTS(PATIENT_ID) ON DELETE CASCADE )

CREATE TABLE SPIRO_TEST(
        'TEST_ID' integer PRIMARYKEY AUTOINCREMENT,
        'EXAM_ID' integer NOT NULL,
        'STATUS_FLAG' smallint,
        'TEST_TYPE' smallint,
        'DATE' timestamp,
        'CONTENT' blob,
        FOREIGN KEY (EXAM_ID) REFERENCES EXAMINATIONS(EXAM_ID) ON DELETE CASCADE )

Note: the following columns are indexed:-

EXAMINATIONS:-

PATIENT_ID
DATE
EXAM_TYPE
STATUS_FLAG

SPIRO_TEST

EXAM_ID



It is taking 6 minutes just to execute the following SQL:-

DELETE FROM SPIRO_TEST

Where SPIRO_TEST contains 11,601 records.

In reality we will never peform the above SQL on the database, we did this only to test how long it would take to delete all the records from the SPIRO_TEST table.

We did this as during testing we noticed that when running the following SQL it took 11 minutes to complete:-

DELETE FROM EXAMINATIONS WHERE DATE < datetime('2005-10-11 00:00:00')

There is a trigger on the SPIRO_TEST table to clear out related records in the module tables when an exam is deleted as such:-

CREATE TRIGGER MODULE_EXAM_TRIGG
BEFORE DELETE ON EXAMINATIONS
        FOR EACH ROW BEGIN
                DELETE FROM SPIRO_TEST WHERE EXAM_ID = OLD.EXAM_ID
        END;

We have also tried removing this trigger and deleting related records from the SPIRO_TEST table maunually before deleting the exams but this took 20 minutes to complete.

We also notice that queries generally run slower when the database contains a large number of records.

Can anyone please help us determine what the problem is and suggest any fix?


Kind Regards

Mark Allan


PS

The SQL:- DELETE FROM EXAMINATIONS WHERE DATE < datetime('2005-10-11 00:00:00'), when the trigger exists, results in the following opcodes:-

0 Goto 0 112
1 Statement 0 0
2 Integer 0 0
3 OpenRead 2 14 keyinfo(1,BINARY)
4 KeyAsData 2 1
5 SetNumColumns 2 2
6 String8 0 0 11/10/2005 00:00
7 Function 1 1 dateTime(1)
8 NotNull -1 11
9 Pop 1 0
10 Goto 0 21
11 MakeRecord 1 0 n
12 MemStore 0 1
13 Rewind 2 21
14 MemLoad 0 0
15 IdxGE 2 21
16 RowKey 2 0
17 IdxIsNull 1 20
18 IdxRecno 2 0
19 ListWrite 0 0
20 Next 2 14
21 Close 2 0
22 OpenPseudo 0 0
23 SetNumColumns 0 9
24 ListRewind 0 0
25 ListRead 0 110
26 Dup 0 0
27 Integer 0 0
28 OpenRead 1 9 # EXAMINATIONS
29 SetNumColumns 1 9
30 MoveGe 1 0
31 Recno 1 0
32 RowData 1 0
33 PutIntKey 0 0
34 Close 1 0
35 ContextPush 0 0 # begin trigger fkd_SPIRO_TEST_EXAM_ID
36 ResetCount 0 0
37 Integer 0 0
38 OpenRead 4 19 keyinfo(1,BINARY)
39 KeyAsData 4 1
40 SetNumColumns 4 2
41 Recno 0 0
42 NotNull -1 45
43 Pop 1 0
44 Goto 0 55
45 MakeRecord 1 0 i
46 MemStore 1 0
47 MoveGe 4 55
48 MemLoad 1 0
49 IdxGE 4 55 +
50 RowKey 4 0
51 IdxIsNull 1 54
52 IdxRecno 4 0
53 ListWrite 0 0
54 Next 4 48
55 Close 4 0
56 ListRewind 0 0
57 Integer 0 0
58 OpenWrite 3 16 # SPIRO_TEST
59 SetNumColumns 3 6
60 Integer 0 0
61 OpenWrite 4 20 keyinfo(1,BINARY)
62 Integer 0 0
63 OpenWrite 5 19 keyinfo(1,BINARY)
64 ListRead 0 76
65 NotExists 3 75
66 Recno 3 0
67 Column 3 2
68 MakeRecord 1 16777216 i
69 IdxDelete 4 0
70 Recno 3 0
71 Column 3 1
72 MakeRecord 1 16777216 i
73 IdxDelete 5 0
74 Delete 3 1
75 Goto 0 64
76 ListReset 0 0
77 Close 4 20
78 Close 5 19
79 Close 3 0
80 ResetCount 1 0
81 ContextPop 0 0 # end trigger fkd_SPIRO_TEST_EXAM_ID
82 Integer 0 0
83 OpenWrite 1 9 # EXAMINATIONS
84 SetNumColumns 1 9
85 Integer 0 0
86 OpenWrite 2 15 keyinfo(1,BINARY)
87 Integer 0 0
88 OpenWrite 3 14 keyinfo(1,BINARY)
89 Integer 0 0
90 OpenWrite 4 13 keyinfo(1,BINARY)
91 NotExists 1 105
92 Recno 1 0
93 Column 1 4
94 MakeRecord 1 16777216 i
95 IdxDelete 2 0
96 Recno 1 0
97 Column 1 3
98 MakeRecord 1 16777216 n
99 IdxDelete 3 0
100 Recno 1 0
101 Column 1 1
102 MakeRecord 1 16777216 t
103 IdxDelete 4 0
104 Delete 1 1
105 Close 2 15
106 Close 3 14
107 Close 4 13
108 Close 1 0
109 Goto 0 25
110 ListReset 0 0
111 Halt 0 0
112 Transaction 0 1
113 VerifyCookie 0 17
114 Goto 0 1
115 Noop 0 0


DISCLAIMER:
This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law.  If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.
Reply | Threaded
Open this post in threaded view
|

Re: Very Slow delete times on larger databases, please help!

D. Richard Hipp
"Allan, Mark" <[hidden email]> wrote:
> We are experiencing incredibly slow delete times when deleting a
> large number of rows:-
>
> We are using SQLite on an embdedded platform with an ARM7 processor,
> 2Mb RAM and 32Mb NOR Flash memory 25Mb of which is allocated for
> storage for our database.
>

What operating system?

Can you send the output of sqlite3_analyzer run against your
database file prior to doing the delete?

Have you tried upgrading to a later version of SQLite?

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

RE: Very Slow delete times on larger databases, please help!

Allan, Mark
In reply to this post by Allan, Mark
Have you been able to investigate this yet? Any ideas or recommendations? I sent you the analyzer output to [hidden email] as it was too large to post on here.


-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: 12 October 2005 14:58
To: [hidden email]
Subject: Re: [sqlite] Very Slow delete times on larger databases, please
help!


"Allan, Mark" <[hidden email]> wrote:
> We are experiencing incredibly slow delete times when deleting a
> large number of rows:-
>
> We are using SQLite on an embdedded platform with an ARM7 processor,
> 2Mb RAM and 32Mb NOR Flash memory 25Mb of which is allocated for
> storage for our database.
>

What operating system?

Can you send the output of sqlite3_analyzer run against your
database file prior to doing the delete?

Have you tried upgrading to a later version of SQLite?

--
D. Richard Hipp <[hidden email]>




DISCLAIMER:
This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law.  If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.
Reply | Threaded
Open this post in threaded view
|

Re: Very Slow delete times on larger databases, please help!

D. Richard Hipp
In reply to this post by Allan, Mark
"Allan, Mark" <[hidden email]> wrote:
> Have you been able to investigate this yet?

I have investigated and I found nothing wrong.  I am unable
to reproduce the problem.
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

RE: Very Slow delete times on larger databases, please help!

Allan, Mark
In reply to this post by Allan, Mark
We are able to compile our application for both target and host. When compiled for host the application runs on Win32 and will create/read/write to a database file on the host PC.

The performance of the deletions on Win32 will not take the 11 minutes I specified, this is only a problem for our target. The same operation on the PC will take only 3-4 seconds. Please note that since my first email we have tried increasing the page size of SQLite and have increased the page size from 1024 bytes to 8192 bytes. This has decreased the time to process the same delete operation from 11 minutes to 3.75 minutes on our target hardware. Both host and target versions of the software use the same configuration a page size of 8192 bytes and a cache of 75 pages = 600k.

The only real differences are 1) the hardware, 2) the filing system. We would expect a difference in performance as the PC is much faster than our target hardware and the write speed to NOR flash is comparatively slow but not as drastic a drop-off as we see. 3-4 seconds -> 3-4 minutes.

It may be useful for us to know what SQLite is doing during deletion so that we can try and optimise our code and/or any configuration of SQLite, our filesystem code or the hardware to try and get this figure down. Can anyone give me a reasonably detailed description of what is happening during delete. The documentation on the website has not helped us diagnose where our problem lies.

Best Regards

Mark



-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: 18 October 2005 19:06
To: [hidden email]
Subject: Re: [sqlite] Very Slow delete times on larger databases, please
help!


"Allan, Mark" <[hidden email]> wrote:
> Have you been able to investigate this yet?

I have investigated and I found nothing wrong.  I am unable
to reproduce the problem.
--
D. Richard Hipp <[hidden email]>




DISCLAIMER:
This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law.  If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.
Reply | Threaded
Open this post in threaded view
|

Re: Very Slow delete times on larger databases, please help!

John Stanton-3
This may or may not help, depending upon your schema.  We find in
general that insertions and deletions are the major overhead in index
maintenance and there is a point where it is cheaper to drop the indices
you are not using and rebuild them rather than to involve the high
overhead of repeated B-Tree rebalancing etc.  The drop and rebuild has
the added benefit of ending up with an optimally organized index free
from fragmentation.

The cost of insertions and deletions in an index is not linear with
index size.

I haven't looked at the Sqlite B-Tree algorithms, so this I can only
suggest this as an experiment.  If Sqlite uses some form of B-Tree
optimization, the overhead of insertions and deletions is greater and
the drop and rebuild more likely to be an improvement.
JS

Allan, Mark wrote:

> We are able to compile our application for both target and host. When compiled for host the application runs on Win32 and will create/read/write to a database file on the host PC.
>
> The performance of the deletions on Win32 will not take the 11 minutes I specified, this is only a problem for our target. The same operation on the PC will take only 3-4 seconds. Please note that since my first email we have tried increasing the page size of SQLite and have increased the page size from 1024 bytes to 8192 bytes. This has decreased the time to process the same delete operation from 11 minutes to 3.75 minutes on our target hardware. Both host and target versions of the software use the same configuration a page size of 8192 bytes and a cache of 75 pages = 600k.
>
> The only real differences are 1) the hardware, 2) the filing system. We would expect a difference in performance as the PC is much faster than our target hardware and the write speed to NOR flash is comparatively slow but not as drastic a drop-off as we see. 3-4 seconds -> 3-4 minutes.
>
> It may be useful for us to know what SQLite is doing during deletion so that we can try and optimise our code and/or any configuration of SQLite, our filesystem code or the hardware to try and get this figure down. Can anyone give me a reasonably detailed description of what is happening during delete. The documentation on the website has not helped us diagnose where our problem lies.
>
> Best Regards
>
> Mark
>
>
>
> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]]
> Sent: 18 October 2005 19:06
> To: [hidden email]
> Subject: Re: [sqlite] Very Slow delete times on larger databases, please
> help!
>
>
> "Allan, Mark" <[hidden email]> wrote:
>
>>Have you been able to investigate this yet?
>
>
> I have investigated and I found nothing wrong.  I am unable
> to reproduce the problem.
> --
> D. Richard Hipp <[hidden email]>
>
>
>
>
> DISCLAIMER:
> This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law.  If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.

Reply | Threaded
Open this post in threaded view
|

RE: Very Slow delete times on larger databases, please help!

Christian Smith
In reply to this post by Allan, Mark
On Wed, 19 Oct 2005, Allan, Mark wrote:

>We are able to compile our application for both target and host. When
>compiled for host the application runs on Win32 and will
>create/read/write to a database file on the host PC.
>
>The performance of the deletions on Win32 will not take the 11 minutes I
>specified, this is only a problem for our target. The same operation on
>the PC will take only 3-4 seconds. Please note that since my first email
>we have tried increasing the page size of SQLite and have increased the
>page size from 1024 bytes to 8192 bytes. This has decreased the time to
>process the same delete operation from 11 minutes to 3.75 minutes on our
>target hardware. Both host and target versions of the software use the
>same configuration a page size of 8192 bytes and a cache of 75 pages =
>600k.
>
>The only real differences are 1) the hardware, 2) the filing system. We
>would expect a difference in performance as the PC is much faster than
>our target hardware and the write speed to NOR flash is comparatively
>slow but not as drastic a drop-off as we see. 3-4 seconds -> 3-4 minutes.


From the VDBE output you originally posted, you are doing a fair amount of
work for each deleted row:
- Index search to find the next row from EXAMINATIONS to delete
- Removing the row from 3 indexes on EXAMINATIONS
- (trigger) Remove related row in SPIRO_TEST from 2 indexes on SPIRO_TEST
- (trigger) Remove related row in SPIRO_TEST
- Remove the row from EXAMINATIONS

Check your cache size. If the above work is causing the 75 page entry
cache to thrash, you're likely to hit worst case performance as the
thrashing pages may be being accessed in a cyclical fashion. Not sure how
like it is that your page cache is not big enough. How big is a row of
data, typically?

Also, a 60x slowdown is not to be unexpected. The PC version, while
probably having the same SQLite page cache size in the SQLite app itself,
will most likely be reading and writing to the OSes cache at memory to
memory copy speed most of the time, with synchronous writes only done when
needed. The embedded platform you're using probably writes straight to
FLASH, which is necassarily a synchronous operation if your OS doesn't
have a cache between your app and the FLASH FS. While flash writes are low
latency, they are also low bandwidth, and won't be within an order of
magnitude of performance when compared to a desktop PC write to OS
filesystem cache.

Finally, you give no indication on the actual CPU speed of the embedded
platform. It's quite reasonable to assume a development PC could be an
order of magnitude faster on sheer integer throughput. I'm amazed how slow
my 50MHz microSPARC based SPARCclassic is. Such a platform would not be
much, if at all, slower than a modern embedded platform, and has the
benefit of gobs of RAM, but still runs the same code two orders of
magnitude slower at least than my Athlon XP 1700 based desktop. You have
to keep your performance expectations realistic. You are, afterall,
running a complete, ACID transaction, SQL relational database.


>
>It may be useful for us to know what SQLite is doing during deletion so
>that we can try and optimise our code and/or any configuration of SQLite,
>our filesystem code or the hardware to try and get this figure down. Can
>anyone give me a reasonably detailed description of what is happening
>during delete. The documentation on the website has not helped us
>diagnose where our problem lies.


Others have indicated that dropping indexes might help when deleting or
inserting records. However, have you tried simply not having indexes at
all? Would that cause unacceptable slowdown? Perhaps, for the demo query
from the original post, just keep the DATE index on EXAMINATIONS, and use
full table scans for queries based on EXAM_TYPE and STATUS_FLAG. Truth is,
given the small number of EXAM_TYPE and STATUS_FLAG values (I presume),
you're as well just doing table scans when looking for specific exam types
and statuses. Indexes only really help when you have a large variation in
values with few collisions. Doing this will leave a single index update in
addition to the actual row removals, which should improve performance.

>
>Best Regards
>
>Mark
>


Christian



>
>
>-----Original Message-----
>From: [hidden email] [mailto:[hidden email]]
>Sent: 18 October 2005 19:06
>To: [hidden email]
>Subject: Re: [sqlite] Very Slow delete times on larger databases, please
>help!
>
>
>"Allan, Mark" <[hidden email]> wrote:
>> Have you been able to investigate this yet?
>
>I have investigated and I found nothing wrong.  I am unable
>to reproduce the problem.
>--
>D. Richard Hipp <[hidden email]>
>
>
>
>
>DISCLAIMER:
>This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law.  If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.
>

--
    /"\
    \ /    ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
     X                           - AGAINST MS ATTACHMENTS
    / \
Reply | Threaded
Open this post in threaded view
|

RE: Very Slow delete times on larger databases, please help!

Allan, Mark
In reply to this post by Allan, Mark

Thanks to both Christian Smith and John Stanton for your posts.

> On Wed, 19 Oct 2005, Christian Smith wrote:
> From the VDBE output you originally posted, you are doing a
> fair amount of
> work for each deleted row:
> - Index search to find the next row from EXAMINATIONS to delete
> - Removing the row from 3 indexes on EXAMINATIONS
> - (trigger) Remove related row in SPIRO_TEST from 2 indexes
> on SPIRO_TEST
> - (trigger) Remove related row in SPIRO_TEST
> - Remove the row from EXAMINATIONS
>
> Check your cache size. If the above work is causing the 75 page entry
> cache to thrash, you're likely to hit worst case performance as the
> thrashing pages may be being accessed in a cyclical fashion.
> Not sure how
> like it is that your page cache is not big enough. How big is a row of
> data, typically?

Our cache size is 75 pages of 8192 bytes = 600Kb.

The maximum size of an EXAMINATIONS record is about 500 bytes, 450 bytes of this is a varchar field. In the test example the average size of an EXAMINATIONS record is 60 bytes as not much text is saved.

The maximum size of a SPIRO_TEST record is about 5Kb, these will vary from test to test, but for the test example the size of each SPIRO_TEST record is fixed to approx 1Kb.

Based on this I dont think that we should be thrashing the cache. I am however unsure how SQlite works here.

> Also, a 60x slowdown is not to be unexpected. The PC version, while
> probably having the same SQLite page cache size in the SQLite
> app itself,
> will most likely be reading and writing to the OSes cache at memory to
> memory copy speed most of the time, with synchronous writes
> only done when
> needed. The embedded platform you're using probably writes straight to
> FLASH, which is necassarily a synchronous operation if your OS doesn't
> have a cache between your app and the FLASH FS. While flash
> writes are low
> latency, they are also low bandwidth, and won't be within an order of
> magnitude of performance when compared to a desktop PC write to OS
> filesystem cache.
>
> Finally, you give no indication on the actual CPU speed of
> the embedded
> platform. It's quite reasonable to assume a development PC could be an
> order of magnitude faster on sheer integer throughput. I'm
> amazed how slow
> my 50MHz microSPARC based SPARCclassic is. Such a platform
> would not be
> much, if at all, slower than a modern embedded platform, and has the
> benefit of gobs of RAM, but still runs the same code two orders of
> magnitude slower at least than my Athlon XP 1700 based
> desktop. You have
> to keep your performance expectations realistic. You are, afterall,
> running a complete, ACID transaction, SQL relational database.


The maximum CPU speed of our ARM7 chip is 71Mhz.

> Others have indicated that dropping indexes might help when
> deleting or
> inserting records. However, have you tried simply not having
> indexes at
> all? Would that cause unacceptable slowdown? Perhaps, for the
> demo query
> from the original post, just keep the DATE index on
> EXAMINATIONS, and use
> full table scans for queries based on EXAM_TYPE and
> STATUS_FLAG. Truth is,
> given the small number of EXAM_TYPE and STATUS_FLAG values (I
> presume),
> you're as well just doing table scans when looking for
> specific exam types
> and statuses. Indexes only really help when you have a large
> variation in
> values with few collisions. Doing this will leave a single
> index update in
> addition to the actual row removals, which should improve performance.

I have tried permanently dropping the indexes on EXAM_TYPE and STATUS_FLAG and this gives some improvement in time. Indeed it does seem that the STATUS_FLAG index is worthless and in the initial version of the software we will have only 1 EXAM_TYPE (although this will increase for each module we release over the next few months).

I have also tried the suggested method of dropping the EXAM_PATIENT_ID_INDEX index on the examinations table before delete and rebuilding it on completion. I cannot delete the remaining indexes as they are used during the delete operation and this slows the whole operation down.

The latest changes have reduced the time to delete the same number of records from 3:45 minutes to 2:53 minutes. Still a long time to wait but any time saving is welcome, especially as the test is for a 50% full scenario so at 99% we can expect it to take 6 minutes.

Thanks again for your help.

If there are any other ideas on how we can optimise this further then please let me know.


Mark


DISCLAIMER:
This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law.  If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.

Reply | Threaded
Open this post in threaded view
|

Re: Very Slow delete times on larger databases, please help!

R S-2
In my case Delete happens reasonably OK but Vaccuuming takes incredibly
long?


On 10/21/05, Allan, Mark <[hidden email]> wrote:

>
>
> Thanks to both Christian Smith and John Stanton for your posts.
>
> > On Wed, 19 Oct 2005, Christian Smith wrote:
> > From the VDBE output you originally posted, you are doing a
> > fair amount of
> > work for each deleted row:
> > - Index search to find the next row from EXAMINATIONS to delete
> > - Removing the row from 3 indexes on EXAMINATIONS
> > - (trigger) Remove related row in SPIRO_TEST from 2 indexes
> > on SPIRO_TEST
> > - (trigger) Remove related row in SPIRO_TEST
> > - Remove the row from EXAMINATIONS
> >
> > Check your cache size. If the above work is causing the 75 page entry
> > cache to thrash, you're likely to hit worst case performance as the
> > thrashing pages may be being accessed in a cyclical fashion.
> > Not sure how
> > like it is that your page cache is not big enough. How big is a row of
> > data, typically?
>
> Our cache size is 75 pages of 8192 bytes = 600Kb.
>
> The maximum size of an EXAMINATIONS record is about 500 bytes, 450 bytes
> of this is a varchar field. In the test example the average size of an
> EXAMINATIONS record is 60 bytes as not much text is saved.
>
> The maximum size of a SPIRO_TEST record is about 5Kb, these will vary from
> test to test, but for the test example the size of each SPIRO_TEST record is
> fixed to approx 1Kb.
>
> Based on this I dont think that we should be thrashing the cache. I am
> however unsure how SQlite works here.
>
> > Also, a 60x slowdown is not to be unexpected. The PC version, while
> > probably having the same SQLite page cache size in the SQLite
> > app itself,
> > will most likely be reading and writing to the OSes cache at memory to
> > memory copy speed most of the time, with synchronous writes
> > only done when
> > needed. The embedded platform you're using probably writes straight to
> > FLASH, which is necassarily a synchronous operation if your OS doesn't
> > have a cache between your app and the FLASH FS. While flash
> > writes are low
> > latency, they are also low bandwidth, and won't be within an order of
> > magnitude of performance when compared to a desktop PC write to OS
> > filesystem cache.
> >
> > Finally, you give no indication on the actual CPU speed of
> > the embedded
> > platform. It's quite reasonable to assume a development PC could be an
> > order of magnitude faster on sheer integer throughput. I'm
> > amazed how slow
> > my 50MHz microSPARC based SPARCclassic is. Such a platform
> > would not be
> > much, if at all, slower than a modern embedded platform, and has the
> > benefit of gobs of RAM, but still runs the same code two orders of
> > magnitude slower at least than my Athlon XP 1700 based
> > desktop. You have
> > to keep your performance expectations realistic. You are, afterall,
> > running a complete, ACID transaction, SQL relational database.
>
>
> The maximum CPU speed of our ARM7 chip is 71Mhz.
>
> > Others have indicated that dropping indexes might help when
> > deleting or
> > inserting records. However, have you tried simply not having
> > indexes at
> > all? Would that cause unacceptable slowdown? Perhaps, for the
> > demo query
> > from the original post, just keep the DATE index on
> > EXAMINATIONS, and use
> > full table scans for queries based on EXAM_TYPE and
> > STATUS_FLAG. Truth is,
> > given the small number of EXAM_TYPE and STATUS_FLAG values (I
> > presume),
> > you're as well just doing table scans when looking for
> > specific exam types
> > and statuses. Indexes only really help when you have a large
> > variation in
> > values with few collisions. Doing this will leave a single
> > index update in
> > addition to the actual row removals, which should improve performance.
>
> I have tried permanently dropping the indexes on EXAM_TYPE and STATUS_FLAG
> and this gives some improvement in time. Indeed it does seem that the
> STATUS_FLAG index is worthless and in the initial version of the software we
> will have only 1 EXAM_TYPE (although this will increase for each module we
> release over the next few months).
>
> I have also tried the suggested method of dropping the
> EXAM_PATIENT_ID_INDEX index on the examinations table before delete and
> rebuilding it on completion. I cannot delete the remaining indexes as they
> are used during the delete operation and this slows the whole operation
> down.
>
> The latest changes have reduced the time to delete the same number of
> records from 3:45 minutes to 2:53 minutes. Still a long time to wait but any
> time saving is welcome, especially as the test is for a 50% full scenario so
> at 99% we can expect it to take 6 minutes.
>
> Thanks again for your help.
>
> If there are any other ideas on how we can optimise this further then
> please let me know.
>
>
> Mark
>
>
> DISCLAIMER:
> This information and any attachments contained in this email message is
> intended only for the use of the individual or entity to which it is
> addressed and may contain information that is privileged, confidential, and
> exempt from disclosure under applicable law. If the reader of this message
> is not the intended recipient, or the employee or agent responsible for
> delivering the message to the intended recipient, you are hereby notified
> that any dissemination, distribution, forwarding, or copying of this
> communication is strictly prohibited. If you have received this
> communication in error, please notify the sender immediately by return
> email, and delete the original message immediately.
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Very Slow delete times on larger databases, please help!

Brett Wilson
Vacuuming is just slow. I don't think there is much you can do except
don't do it unless you really need it, and don't turn on autovacuum.

Brett

On 10/26/05, R S <[hidden email]> wrote:

> In my case Delete happens reasonably OK but Vaccuuming takes incredibly
> long?
>
>
> On 10/21/05, Allan, Mark <[hidden email]> wrote:
> >
> >
> > Thanks to both Christian Smith and John Stanton for your posts.
> >
> > > On Wed, 19 Oct 2005, Christian Smith wrote:
> > > From the VDBE output you originally posted, you are doing a
> > > fair amount of
> > > work for each deleted row:
> > > - Index search to find the next row from EXAMINATIONS to delete
> > > - Removing the row from 3 indexes on EXAMINATIONS
> > > - (trigger) Remove related row in SPIRO_TEST from 2 indexes
> > > on SPIRO_TEST
> > > - (trigger) Remove related row in SPIRO_TEST
> > > - Remove the row from EXAMINATIONS
> > >
> > > Check your cache size. If the above work is causing the 75 page entry
> > > cache to thrash, you're likely to hit worst case performance as the
> > > thrashing pages may be being accessed in a cyclical fashion.
> > > Not sure how
> > > like it is that your page cache is not big enough. How big is a row of
> > > data, typically?
> >
> > Our cache size is 75 pages of 8192 bytes = 600Kb.
> >
> > The maximum size of an EXAMINATIONS record is about 500 bytes, 450 bytes
> > of this is a varchar field. In the test example the average size of an
> > EXAMINATIONS record is 60 bytes as not much text is saved.
> >
> > The maximum size of a SPIRO_TEST record is about 5Kb, these will vary from
> > test to test, but for the test example the size of each SPIRO_TEST record is
> > fixed to approx 1Kb.
> >
> > Based on this I dont think that we should be thrashing the cache. I am
> > however unsure how SQlite works here.
> >
> > > Also, a 60x slowdown is not to be unexpected. The PC version, while
> > > probably having the same SQLite page cache size in the SQLite
> > > app itself,
> > > will most likely be reading and writing to the OSes cache at memory to
> > > memory copy speed most of the time, with synchronous writes
> > > only done when
> > > needed. The embedded platform you're using probably writes straight to
> > > FLASH, which is necassarily a synchronous operation if your OS doesn't
> > > have a cache between your app and the FLASH FS. While flash
> > > writes are low
> > > latency, they are also low bandwidth, and won't be within an order of
> > > magnitude of performance when compared to a desktop PC write to OS
> > > filesystem cache.
> > >
> > > Finally, you give no indication on the actual CPU speed of
> > > the embedded
> > > platform. It's quite reasonable to assume a development PC could be an
> > > order of magnitude faster on sheer integer throughput. I'm
> > > amazed how slow
> > > my 50MHz microSPARC based SPARCclassic is. Such a platform
> > > would not be
> > > much, if at all, slower than a modern embedded platform, and has the
> > > benefit of gobs of RAM, but still runs the same code two orders of
> > > magnitude slower at least than my Athlon XP 1700 based
> > > desktop. You have
> > > to keep your performance expectations realistic. You are, afterall,
> > > running a complete, ACID transaction, SQL relational database.
> >
> >
> > The maximum CPU speed of our ARM7 chip is 71Mhz.
> >
> > > Others have indicated that dropping indexes might help when
> > > deleting or
> > > inserting records. However, have you tried simply not having
> > > indexes at
> > > all? Would that cause unacceptable slowdown? Perhaps, for the
> > > demo query
> > > from the original post, just keep the DATE index on
> > > EXAMINATIONS, and use
> > > full table scans for queries based on EXAM_TYPE and
> > > STATUS_FLAG. Truth is,
> > > given the small number of EXAM_TYPE and STATUS_FLAG values (I
> > > presume),
> > > you're as well just doing table scans when looking for
> > > specific exam types
> > > and statuses. Indexes only really help when you have a large
> > > variation in
> > > values with few collisions. Doing this will leave a single
> > > index update in
> > > addition to the actual row removals, which should improve performance.
> >
> > I have tried permanently dropping the indexes on EXAM_TYPE and STATUS_FLAG
> > and this gives some improvement in time. Indeed it does seem that the
> > STATUS_FLAG index is worthless and in the initial version of the software we
> > will have only 1 EXAM_TYPE (although this will increase for each module we
> > release over the next few months).
> >
> > I have also tried the suggested method of dropping the
> > EXAM_PATIENT_ID_INDEX index on the examinations table before delete and
> > rebuilding it on completion. I cannot delete the remaining indexes as they
> > are used during the delete operation and this slows the whole operation
> > down.
> >
> > The latest changes have reduced the time to delete the same number of
> > records from 3:45 minutes to 2:53 minutes. Still a long time to wait but any
> > time saving is welcome, especially as the test is for a 50% full scenario so
> > at 99% we can expect it to take 6 minutes.
> >
> > Thanks again for your help.
> >
> > If there are any other ideas on how we can optimise this further then
> > please let me know.
> >
> >
> > Mark
> >
> >
> > DISCLAIMER:
> > This information and any attachments contained in this email message is
> > intended only for the use of the individual or entity to which it is
> > addressed and may contain information that is privileged, confidential, and
> > exempt from disclosure under applicable law. If the reader of this message
> > is not the intended recipient, or the employee or agent responsible for
> > delivering the message to the intended recipient, you are hereby notified
> > that any dissemination, distribution, forwarding, or copying of this
> > communication is strictly prohibited. If you have received this
> > communication in error, please notify the sender immediately by return
> > email, and delete the original message immediately.
> >
> >
>
>
Reply | Threaded
Open this post in threaded view
|

RE: Very Slow delete times on larger databases, please help!

Allan, Mark
In reply to this post by Allan, Mark
Yes we found this out too. We never vacuum the file, it is acceptable for it to just get larger not smaller. We generate an indication on the current database capacity not from the file size but from the total number of pages in the database file minus the number of free pages in the database.

So these times are not affected by vacuum.

Thanks

Mark


> -----Original Message-----
> From: Brett Wilson [mailto:[hidden email]]
> Sent: 26 October 2005 19:22
> To: [hidden email]
> Subject: Re: [sqlite] Very Slow delete times on larger
> databases, please
> help!
>
>
> Vacuuming is just slow. I don't think there is much you can do except
> don't do it unless you really need it, and don't turn on autovacuum.
>
> Brett
>
> On 10/26/05, R S <[hidden email]> wrote:
> > In my case Delete happens reasonably OK but Vaccuuming
> takes incredibly
> > long?
> >
> >
> > On 10/21/05, Allan, Mark <[hidden email]> wrote:
> > >
> > >
> > > Thanks to both Christian Smith and John Stanton for your posts.
> > >
> > > > On Wed, 19 Oct 2005, Christian Smith wrote:
> > > > From the VDBE output you originally posted, you are doing a
> > > > fair amount of
> > > > work for each deleted row:
> > > > - Index search to find the next row from EXAMINATIONS to delete
> > > > - Removing the row from 3 indexes on EXAMINATIONS
> > > > - (trigger) Remove related row in SPIRO_TEST from 2 indexes
> > > > on SPIRO_TEST
> > > > - (trigger) Remove related row in SPIRO_TEST
> > > > - Remove the row from EXAMINATIONS
> > > >
> > > > Check your cache size. If the above work is causing the
> 75 page entry
> > > > cache to thrash, you're likely to hit worst case
> performance as the
> > > > thrashing pages may be being accessed in a cyclical fashion.
> > > > Not sure how
> > > > like it is that your page cache is not big enough. How
> big is a row of
> > > > data, typically?
> > >
> > > Our cache size is 75 pages of 8192 bytes = 600Kb.
> > >
> > > The maximum size of an EXAMINATIONS record is about 500
> bytes, 450 bytes
> > > of this is a varchar field. In the test example the
> average size of an
> > > EXAMINATIONS record is 60 bytes as not much text is saved.
> > >
> > > The maximum size of a SPIRO_TEST record is about 5Kb,
> these will vary from
> > > test to test, but for the test example the size of each
> SPIRO_TEST record is
> > > fixed to approx 1Kb.
> > >
> > > Based on this I dont think that we should be thrashing
> the cache. I am
> > > however unsure how SQlite works here.
> > >
> > > > Also, a 60x slowdown is not to be unexpected. The PC
> version, while
> > > > probably having the same SQLite page cache size in the SQLite
> > > > app itself,
> > > > will most likely be reading and writing to the OSes
> cache at memory to
> > > > memory copy speed most of the time, with synchronous writes
> > > > only done when
> > > > needed. The embedded platform you're using probably
> writes straight to
> > > > FLASH, which is necassarily a synchronous operation if
> your OS doesn't
> > > > have a cache between your app and the FLASH FS. While flash
> > > > writes are low
> > > > latency, they are also low bandwidth, and won't be
> within an order of
> > > > magnitude of performance when compared to a desktop PC
> write to OS
> > > > filesystem cache.
> > > >
> > > > Finally, you give no indication on the actual CPU speed of
> > > > the embedded
> > > > platform. It's quite reasonable to assume a development
> PC could be an
> > > > order of magnitude faster on sheer integer throughput. I'm
> > > > amazed how slow
> > > > my 50MHz microSPARC based SPARCclassic is. Such a platform
> > > > would not be
> > > > much, if at all, slower than a modern embedded
> platform, and has the
> > > > benefit of gobs of RAM, but still runs the same code
> two orders of
> > > > magnitude slower at least than my Athlon XP 1700 based
> > > > desktop. You have
> > > > to keep your performance expectations realistic. You
> are, afterall,
> > > > running a complete, ACID transaction, SQL relational database.
> > >
> > >
> > > The maximum CPU speed of our ARM7 chip is 71Mhz.
> > >
> > > > Others have indicated that dropping indexes might help when
> > > > deleting or
> > > > inserting records. However, have you tried simply not having
> > > > indexes at
> > > > all? Would that cause unacceptable slowdown? Perhaps, for the
> > > > demo query
> > > > from the original post, just keep the DATE index on
> > > > EXAMINATIONS, and use
> > > > full table scans for queries based on EXAM_TYPE and
> > > > STATUS_FLAG. Truth is,
> > > > given the small number of EXAM_TYPE and STATUS_FLAG values (I
> > > > presume),
> > > > you're as well just doing table scans when looking for
> > > > specific exam types
> > > > and statuses. Indexes only really help when you have a large
> > > > variation in
> > > > values with few collisions. Doing this will leave a single
> > > > index update in
> > > > addition to the actual row removals, which should
> improve performance.
> > >
> > > I have tried permanently dropping the indexes on
> EXAM_TYPE and STATUS_FLAG
> > > and this gives some improvement in time. Indeed it does
> seem that the
> > > STATUS_FLAG index is worthless and in the initial version
> of the software we
> > > will have only 1 EXAM_TYPE (although this will increase
> for each module we
> > > release over the next few months).
> > >
> > > I have also tried the suggested method of dropping the
> > > EXAM_PATIENT_ID_INDEX index on the examinations table
> before delete and
> > > rebuilding it on completion. I cannot delete the
> remaining indexes as they
> > > are used during the delete operation and this slows the
> whole operation
> > > down.
> > >
> > > The latest changes have reduced the time to delete the
> same number of
> > > records from 3:45 minutes to 2:53 minutes. Still a long
> time to wait but any
> > > time saving is welcome, especially as the test is for a
> 50% full scenario so
> > > at 99% we can expect it to take 6 minutes.
> > >
> > > Thanks again for your help.
> > >
> > > If there are any other ideas on how we can optimise this
> further then
> > > please let me know.
> > >
> > >
> > > Mark
> > >
> > >
> > > DISCLAIMER:
> > > This information and any attachments contained in this
> email message is
> > > intended only for the use of the individual or entity to
> which it is
> > > addressed and may contain information that is privileged,
> confidential, and
> > > exempt from disclosure under applicable law. If the
> reader of this message
> > > is not the intended recipient, or the employee or agent
> responsible for
> > > delivering the message to the intended recipient, you are
> hereby notified
> > > that any dissemination, distribution, forwarding, or
> copying of this
> > > communication is strictly prohibited. If you have received this
> > > communication in error, please notify the sender
> immediately by return
> > > email, and delete the original message immediately.
> > >
> > >
> >
> >
>
>


DISCLAIMER:
This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law.  If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.