Richard,

Your last request for some detailed information about what queries were
being processed did the trick.  Here is a description of the problem (and
yes, it appears to be in SQLite).

In 3.7.5, the number one malloc consumer is

INSERT INTO SEQUENCE_ELEMENTS
(
SEQUENCE_ELEMENT_OID,
SEQUENCE_ELEMENT_NAME,
definition_parent,
instance_parent
)
values
(
:SEQUENCE_ELEMENT_OID,
:SEQUENCE_ELEMENT_NAME,
:definition_parent,
:instance_parent
)

Times called:                              1
Cumulative Allocated Memory:               12,856
Count of _malloc Calls:                    169
Cumulative Reallocated Memory:             13,544
Count of _realloc Calls:                   24
Cumulative Reallocated Memory where nil:   0
Count of _realloc Calls where nil:         0
Count of _free Calls:                      111
Cumulative _mallocs by size
<= 1kb:                                    9,168 bytes (166 count; 55 avg)
1kb to 4kb:                                3,688 bytes (3 count; 1,229 avg)
4bk to 8kb:                                0 bytes (0 count; NAN avg)
8bk to 16kb:                               0 bytes (0 count; NAN avg)
16bk to 32kb:                              0 bytes (0 count; NAN avg)
32bk to 64kb:                              0 bytes (0 count; NAN avg)
64bk to 128kb:                             0 bytes (0 count; NAN avg)
128kb to 256kb:                            0 bytes (0 count; NAN avg)
256kb to 512kb:                            0 bytes (0 count; NAN avg)
512kb to 1024kb:                           0 bytes (0 count; NAN avg)
> 1mb:                                     0 bytes (0 count; NAN avg)
Cumulative _reallocs by size
<= 1kb:                                    1,272 bytes (20 count; 64 avg)
1kb to 4kb:                                12,272 bytes (4 count; 3,068 avg)
4bk to 8kb:                                0 bytes (0 count; NAN avg)
8bk to 16kb:                               0 bytes (0 count; NAN avg)
16bk to 32kb:                              0 bytes (0 count; NAN avg)
32bk to 64kb:                              0 bytes (0 count; NAN avg)
64bk to 128kb:                             0 bytes (0 count; NAN avg)
128kb to 256kb:                            0 bytes (0 count; NAN avg)
256kb to 512kb:                            0 bytes (0 count; NAN avg)
512kb to 1024kb:                           0 bytes (0 count; NAN avg)
> 1mb:                                     0 bytes (0 count; NAN avg)
No reallocs of nil pointers
---

However, in 3.7.6, the following is the number one consumer of mallocs:

CREATE TRIGGER sequence_elements_after_insert after insert on
sequence_elements
begin
update sequence_elements set sort_key = sequence_element_oid where
sequence_element_oid  = new.sequence_element_oid and sort_key is null;
insert into responses (definition_parent, instance_parent, response_name,
prelisted_value) select de.data_element_oid, new.sequence_element_oid,
ag.initial_value, '' from data_elements de, attribute_groups ag where
de.definition_parent = new.definition_parent and de.attribute_group =
ag.attribute_group_name;
insert or ignore into altered_sequences values(new.definition_parent);
end
Times called:                              30502
Cumulative Allocated Memory:               255,240,736
Count of _malloc Calls:                    3,080,702
Cumulative Reallocated Memory:             3,904,256
Count of _realloc Calls:                   30,502
Cumulative Reallocated Memory where nil:   0
Count of _realloc Calls where nil:         0
Count of _free Calls:                      1,799,618
Cumulative _mallocs by size
<= 1kb:                                    174,227,424 bytes (3,019,698
count; 58 avg)
1kb to 4kb:                                81,013,312 bytes (61,004 count;
1,328 avg)
4bk to 8kb:                                0 bytes (0 count; NAN avg)
8bk to 16kb:                               0 bytes (0 count; NAN avg)
16bk to 32kb:                              0 bytes (0 count; NAN avg)
32bk to 64kb:                              0 bytes (0 count; NAN avg)
64bk to 128kb:                             0 bytes (0 count; NAN avg)
128kb to 256kb:                            0 bytes (0 count; NAN avg)
256kb to 512kb:                            0 bytes (0 count; NAN avg)
512kb to 1024kb:                           0 bytes (0 count; NAN avg)
> 1mb:                                     0 bytes (0 count; NAN avg)
Cumulative _reallocs by size
<= 1kb:                                    3,904,256 bytes (30,502 count;
128 avg)
1kb to 4kb:                                0 bytes (0 count; NAN avg)
4bk to 8kb:                                0 bytes (0 count; NAN avg)
8bk to 16kb:                               0 bytes (0 count; NAN avg)
16bk to 32kb:                              0 bytes (0 count; NAN avg)
32bk to 64kb:                              0 bytes (0 count; NAN avg)
64bk to 128kb:                             0 bytes (0 count; NAN avg)
128kb to 256kb:                            0 bytes (0 count; NAN avg)
256kb to 512kb:                            0 bytes (0 count; NAN avg)
512kb to 1024kb:                           0 bytes (0 count; NAN avg)
> 1mb:                                     0 bytes (0 count; NAN avg)
No reallocs of nil pointers
---

What appears to be happening is that all of the TRIGGERS (and VIEWS for
that matter) are being reparsed every time they are triggered.  Please note
that these triggers are NOT being DROPPED by the application.  This issue
did not turn up in my earlier testing because I was looking at the SQL
commands that our application was passing to SQLite, not commands which may
have been processed internally.

What now?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to