[sqlite] how to unsubscribe (was: Size of the SQLite library)

[Hick Gunter]

Use the links supplied at the bottom of each and every message to unsubscribe 
yourself

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dianne Dunn
Gesendet: Donnerstag, 07. Juni 2018 06:25
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Size of the SQLite library

Hey there do you know how I can get off this list.??

Sent from my iPad

> On Jun 5, 2018, at 3:50 AM, Robert M. Münch  
> wrote:
>
>> On 31 May 2018, at 19:15, Richard Hipp wrote:
>>
>> But more recently, mobile phone designers are telling me things like
>> "try to keep the size under 5 megabytes, if you can, please."
>>
>> Based on those more recent conversations, I'm thinking that we have
>> more headroom that we have had historically, and so I have recently
>> been allowing new features to start creeping into the core.
>
> Size matters IMO, it’s a sign of good design and less is more WRT errors etc.
>
>
>> Size is still important.  But having useful features is important too.
>
> True, and we all know that most features are not used. Do you have an idea 
> what features are used by ratio? Maybe adding a „report back feature 
> collector“ might be an idea, for those wanting to support the feature 
> selection process.
>
>
>> I'm continuing to work to find the right balance between these
>> competing goals.
>
> Keeping things configurable as it is, is a very good approach, please keep it.
>
> --
>
> Robert M. Münch, CEO
> M: +41 79 65 11 49 6
>
> Saphirion AG
> smarter | better | faster
>
> http://www.saphirion.com
> http://www.nlpp.ch
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Keith Medcalf]

Have you tried the link at the end of every message?


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Dianne Dunn
>Sent: Wednesday, 6 June, 2018 22:25
>To: SQLite mailing list
>Subject: Re: [sqlite] Size of the SQLite library
>
>Hey there do you know how I can get off this list.??
>
>Sent from my iPad
>
>> On Jun 5, 2018, at 3:50 AM, Robert M. Münch
> wrote:
>>
>>> On 31 May 2018, at 19:15, Richard Hipp wrote:
>>>
>>> But more recently, mobile phone designers are telling me things
>like
>>> "try to keep the size under 5 megabytes, if you can, please."
>>>
>>> Based on those more recent conversations, I'm thinking that we
>have
>>> more headroom that we have had historically, and so I have
>recently
>>> been allowing new features to start creeping into the core.
>>
>> Size matters IMO, it’s a sign of good design and less is more WRT
>errors etc.
>>
>>
>>> Size is still important.  But having useful features is important
>too.
>>
>> True, and we all know that most features are not used. Do you have
>an idea what features are used by ratio? Maybe adding a „report back
>feature collector“ might be an idea, for those wanting to support the
>feature selection process.
>>
>>
>>> I'm continuing to work to find the right balance between these
>>> competing goals.
>>
>> Keeping things configurable as it is, is a very good approach,
>please keep it.
>>
>> --
>>
>> Robert M. Münch, CEO
>> M: +41 79 65 11 49 6
>>
>> Saphirion AG
>> smarter | better | faster
>>
>> http://www.saphirion.com
>> http://www.nlpp.ch
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Dianne Dunn]

Hey there do you know how I can get off this list.??

Sent from my iPad

> On Jun 5, 2018, at 3:50 AM, Robert M. Münch  
> wrote:
> 
>> On 31 May 2018, at 19:15, Richard Hipp wrote:
>> 
>> But more recently, mobile phone designers are telling me things like
>> "try to keep the size under 5 megabytes, if you can, please."
>> 
>> Based on those more recent conversations, I'm thinking that we have
>> more headroom that we have had historically, and so I have recently
>> been allowing new features to start creeping into the core.
> 
> Size matters IMO, it’s a sign of good design and less is more WRT errors etc.
> 
> 
>> Size is still important.  But having useful features is important too.
> 
> True, and we all know that most features are not used. Do you have an idea 
> what features are used by ratio? Maybe adding a „report back feature 
> collector“ might be an idea, for those wanting to support the feature 
> selection process.
> 
> 
>> I'm continuing to work to find the right balance between these
>> competing goals.
> 
> Keeping things configurable as it is, is a very good approach, please keep it.
> 
> -- 
> 
> Robert M. Münch, CEO
> M: +41 79 65 11 49 6
> 
> Saphirion AG
> smarter | better | faster
> 
> http://www.saphirion.com
> http://www.nlpp.ch
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Roger Binns]

On 06/06/18 09:24, Bob Friesenhahn wrote:
> A local tool which makes it easy to configure sqlite from local files
> sounds useful ...

It already exists.  It is what the SQLite team uses to produce the
amalgamations etc, and is part of the SQLite code base.

> but depending on a "web site" (baby-bird model) ...

Note that behind the scenes the existing tools would be used with the
relevant results zipped up and downloadable.  No one is advocating
getting rid of the command line tools, just a web front end.

> There is already far too much dependence on what what
> happens to get served up at the time and too much dependence on a live
> connection to the "Internet" ...

You and Warren comprehensively describe best practises and why.  You are
both right.  It is what developers *should* do for repeatable reliable
builds.

But it is a lot of friction.  And not every developer follows best
practise.  And developers start out investigating and playing around
with potential solutions, and then adopt the appropriate ones.

If you are trying out a "hello world" quick test, then the best
practises are a lot of friction, and a few web page tickboxes are the least.

The more friction there is, the fewer people will try non-default
configurations.  But that also locks SQLite into a pessimistic legacy
configuration going forward.  For example default enabling STAT4 or
disabling deprecated API could not be done, ever.

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3BtreeBeginTrans commit breaks sessions modules ...

[Keith Medcalf]

sqlite3BtreeBeginTrans() now has an extra parameter.  sessions do not like this.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Simon Slavin]

I recommend a mixture of the following two solutions:

On 6 Jun 2018, at 5:05pm, Roger Binns  wrote:

> That is why I advocate a web site where the user (un)ticks what they
> want, and the web site provides a correctly configured download.

6 Jun 2018, at 5:24pm, Bob Friesenhahn  wrote:

> A local tool which makes it easy to configure sqlite from local files sounds 
> useful but depending on a "web site" (baby-bird model) does not sound good to 
> me.  There is already far too much dependence on what what happens to get 
> served up at the time and too much dependence on a live connection to the 
> "Internet" with a naive expectation what what was produced yesterday will 
> continue to be produced tomorrow.

To mix them, you put one configuration on the web.  The current one (call it 
"most useful for most people") is fine.  This configuration can be changed by 
editing one file.  For a C project this would be a "sqlite3config.h" file with 
lots of "#define" lines.  This file has nothing in except for configuration 
settings and some (but not long and exhaustive) comments on what they do.  No 
macros or function definitions.

Experts can read the documentation in the file and set the definitions 
themselves.

But you also put up an online configuration web page, which works using 
JavaScript.  The web page has GUI features up top: popup menus, checkboxes, 
radio-buttons, whatever.  At the bottom of the page is a text field containing 
the entire contents for an ".h" file which configures the compilation according 
to those settings.  You can change the GUI settings and see how that changes 
the file in real time.  It's up to the user to copy that text and past it into 
a new "sqlite3config.h" file or whatever it is.

Here's the good part: because the web page works using just JavaScript (rather 
than PHP, ASP, node, whatever) you can include a copy of it with the 
distribution and any user can run it in their favourite browser without needing 
internet access, or perhaps on a smartphone.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Keith Medcalf]

On Wednesday, 6 June, 2018 10:24, Bob Friesenhahn wrote:

> The build strategy for the Python APSW extension is an 
> example of unwanted dependency and loss of control.

> Building of software from source code should always be 
> under the complete control of the person who is performing 
> the build and should inherently support use of local files 
> which may contain local changes.

I build APSW this way and it uses a completely customized
version of the sqlite3.c amalgamation that I also build into
its own sqlite3 executables and DLLs, it is really not that 
difficult.  Mind you, I extract the latest APSW sources 
from the ZIP archive and have built my own build-scripts
to do this.  Because I use the mingw-w64 compiler toolchain
I also have to slightly modify the default Python library
cygwincompiler.py and have made my own customized APSW
setup.py with a different name (based on the distributed 
source setup.py) that automates the whole process.

Fossil is used to manage the local repositories that are 
created from the distribution source (I have another 
Linux machine that builds the amalgamation source from
the full sources and I copy that to use as my base 
amalgamation for generating the executables, DLLs, and 
APSW).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Bob Friesenhahn]

On Wed, 6 Jun 2018, Roger Binns wrote:


That is why I advocate a web site where the user (un)ticks what they
want, and the web site provides a correctly configured download.  This
will also tell the SQLite developers what features are configured.  (eg
if everyone turns off virtual tables that is useful feedback, as would
the opposite.)


A local tool which makes it easy to configure sqlite from local files 
sounds useful but depending on a "web site" (baby-bird model) does not 
sound good to me.  There is already far too much dependence on what 
what happens to get served up at the time and too much dependence on a 
live connection to the "Internet" with a naive expectation what what 
was produced yesterday will continue to be produced tomorrow.  The 
build strategy for the Python APSW extension is an example of unwanted 
dependency and loss of control.


Building of software from source code should always be under the 
complete control of the person who is performing the build and should 
inherently support use of local files which may contain local changes.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Roger Binns]

On 05/06/18 15:07, Warren Young wrote:
> All right, so include [multi-component source control and build process] ...

I'm still not sure what point you are trying to make.  Yes *you* can do
that.  Should *every* SQLite user who wants non-default options *have*
to go through a similar amount of friction?

SQLite currently only has one distribution.  This distribution has to
fit most user needs regarding backwards and forwards compatibility
(including query plans), functionality, size etc.

*If* SQLite wants to step away from one size/configuration fits most,
then there needs to be way less friction in getting the alternate
configurations.  One solution is a small number of alternate downloads
("presets"), although it is hard to know what configurations they should
have.

That is why I advocate a web site where the user (un)ticks what they
want, and the web site provides a correctly configured download.  This
will also tell the SQLite developers what features are configured.  (eg
if everyone turns off virtual tables that is useful feedback, as would
the opposite.)

> Thus the need for curated collections of build options, since a jQuery UI 
> like tool that assumes the options are all orthogonal would frequently 
> produce unbuildable output.

Huh?  No one is advocating a SQLite web tool that produces unbuildable
output, or offers every possible combination of options.  It would need
to be useful, and can start simple.

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite delete slow in 4GB

[Eduardo]

On Wed, 6 Jun 2018 14:33:12 +0300
Aydin Ozgur Yagmur  escribió:

> I was applying the changes. After moving blob to last column suggested by
> Hick and eduardo, 500 MB delete took 40-45 seconds.
> I was already attempted to change page_size & cache size but i had not seen
> any significant improvement, so i didnt apply this now.

When you change the page_size you must vacuum the db. If not, the change in
page_size is noop.

The change in cache size will speed up the access to blobs, in a basic way.
There are better ways to do so but bit more complex. 

For example, a 4GB cache_size will put, lazily, all db file in memory.

> could you please explain positive effects of "autovacuum=incremental"
> solution? I did not see too much positive comment on it.

Autovacumm incremental, adds sqlite metadata in pages, performing better read
access to blobs bigger than your page_size. The trick is never call the pragma
incremental_autovacuum. That's, you set incremental but never use it.

> Thanks for your help,

-- 
Eduardo 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite delete slow in 4GB

[Aydin Ozgur Yagmur]

I was applying the changes. After moving blob to last column suggested by
Hick and eduardo, 500 MB delete took 40-45 seconds.
I was already attempted to change page_size & cache size but i had not seen
any significant improvement, so i didnt apply this now.
could you please explain positive effects of "autovacuum=incremental"
solution? I did not see too much positive comment on it.

Thanks for your help,



On Wed, Jun 6, 2018 at 1:10 PM, Eduardo 
wrote:

> On Wed, 6 Jun 2018 12:06:15 +0300
> Aydin Ozgur Yagmur  escribió:
>
> > Thank you very quick response.
> >
> > We have already planned to change file system. But this problem seem not
> > related with the file size, because we did not hit the limit and when i
> try
> > to delete same data in ,for example, 2 GB-sized db, i encounter with the
> > same problem. This is my table and index definitions.
>
> Why didn't apply the changes suggested by others on the other thread?
>
> > Thanks for your help.
> >
> > CREATE TABLE "ANHXT" (
> >   "id" integer primary key autoincrement,
> >   "ANH_AD" text,
> >   "ANH_DBGMHWID" text,
> >   "ANH_TYPE" integer,
> >   "ANH_INDEXNO" int64_t)
> > CREATE TABLE "PRCXT" (
> >   "id" integer primary key autoincrement,
> >   "ANP_SEGMENTNO" integer not null,
> >   "ANP_VALUE" blob,
> >   "ANH_PRC_id" bigint,
> >   constraint "fk_ANHPRC_ANH_PRC" foreign key ("ANH_PRC_id") references
> >  "ANHXT" ("id") on update cascade on delete cascade deferrable
> > initially deferred)
> > CREATE UNIQUE INDEX UQC_ANH_TYPE on ANHXT( ANH_TYPE)CREATE UNIQUE
> > INDEX UQC_ANP_SEGMENTNO_ANAHTARID on PRCXT(
> > ANP_SEGMENTNO,ANH_PRC_id)CREATE INDEX findex on PRCXT( ANH_PRC_id)
>
>
> Move the blob to the last column
>
>  CREATE TABLE "PRCXT" (
>"id" integer primary key autoincrement,
>"ANP_SEGMENTNO" integer not null,
>"ANH_PRC_id" bigint,
>constraint "fk_ANHPRC_ANH_PRC" foreign key ("ANH_PRC_id") references
>   "ANHXT" ("id") on update cascade on delete cascade deferrable
>  initially deferred),
>"ANP_VALUE" blob
>
>
> even better, use a table specific for the blob:
>
>   CREATE TABLE the_blob (
> id integer primary key,
> content blob
>   )
>
> the best for it, use 2 databases, one for metadata (ANHXT PRCXT tables) and
> other for the blobs. Open the metadata and attach the blob.
>
> If you use foreign key constraint to point to blob table, don't use wal
> mode.
> If you want/need wal mode, use an integer column to the blob id table and
> manage updates and deletes yourself.
>
> Set these pragmas for the blob schema before creation:
>
> pragma the_blob.autovacuum = incremental;
> pragma the_blob.page_size = 65536;
> pragma the_blob.cache_size = 1024; -- must set on open/attach too. 64MB
>
> Depending on your insert/update/delete ratios, there are other patterns
> that may fit better.
>
> > On Wed, Jun 6, 2018 at 11:42 AM, Karl Billeter 
> wrote:
> >
> > > On Wed, Jun 06, 2018 at 09:27:57AM +0300, Aydin Ozgur Yagmur wrote:
> > >
> > > > I have been using fat32 file system. I have a database which has 4 GB
> > > size.
> > >
> > > Er... I'm surprised there aren't more problems due to 4GB being the max
> > > file
> > > size supported by fat32.  Any chance to change it to exFAT?
> > >
>
> Don't use FAT32. Use your native fs, ntfs, ufs2, hfs+, hammer2, ext4,
>
> > > K
>
> --
> Eduardo 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Sqlite delete slow in 4GB

[Paul Sanderson]

The structure of a record is shown in the graphic at this link which is
from my book SQLite Forensics:



www.sqliteforensics.co.uk/pics/table_leaf_format.png



As long as ALL of the serial types (i.e. all of the cell pointer array) is
held in the main B-tree (i.e. not an overflow page) which unless you have a
huge number of columns in a table will pretty much always be the case, then
finding out the offset to a given field in the payload is a case of just
reading and determining the size of each serial type until you get to the
entry you want. If the payload data you want is in the same page, before or
after a blob, then accessing it is just a case of reading from the current
page buffer. It is only when the blob is so large that the data you want is
in an overflow page that things slow down, it shouldn't matter if some of
the record overflows, as long as the data you want is on the b-tree leaf
page.



If the blob is very big then you may need to read multiple overflow pages
to get to the data you want.



It is also worth bearing in mind that this does not just apply to blobs, if
you have a large string field, or multiple string fields that cause a
record to overflow, then you have exactly the same problem with records
that come after the string fields.


Paul
www.sandersonforensics.com
SQLite Forensics Book 

On 6 June 2018 at 10:15, Hick Gunter  wrote:

> Your schema has a major flaw that is addressed in SQLite documentation:
>
> Do not put any fields after blob fields, especially if the content is
> typically large. SQLite accesses fields in the order of defintion, so to
> access your ANH_PRC_id field, it has to retrieve the 1MB ANP_VALUE blob.
>
> Putting small and frequently acessed fields at the beginning of the
> definition allows SQLite to retrieve these fields without expanding the
> whole row.
>
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Aydin Ozgur Yagmur
> Gesendet: Mittwoch, 06. Juni 2018 11:06
> An: SQLite mailing list 
> Betreff: [EXTERNAL] Re: [sqlite] Sqlite delete slow in 4GB
>
> Thank you very quick response.
>
> We have already planned to change file system. But this problem seem not
> related with the file size, because we did not hit the limit and when i try
> to delete same data in ,for example, 2 GB-sized db, i encounter with the
> same problem. This is my table and index definitions.
>
> Thanks for your help.
>
> CREATE TABLE "ANHXT" (
>   "id" integer primary key autoincrement,
>   "ANH_AD" text,
>   "ANH_DBGMHWID" text,
>   "ANH_TYPE" integer,
>   "ANH_INDEXNO" int64_t)
> CREATE TABLE "PRCXT" (
>   "id" integer primary key autoincrement,
>   "ANP_SEGMENTNO" integer not null,
>   "ANP_VALUE" blob,
> ==>  "ANH_PRC_id" bigint,
>   constraint "fk_ANHPRC_ANH_PRC" foreign key ("ANH_PRC_id") references
> "ANHXT" ("id") on update cascade on delete cascade deferrable initially
> deferred) CREATE UNIQUE INDEX UQC_ANH_TYPE on ANHXT( ANH_TYPE)CREATE UNIQUE
> INDEX UQC_ANP_SEGMENTNO_ANAHTARID on PRCXT( ANP_SEGMENTNO,ANH_PRC_id)CREATE
> INDEX findex on PRCXT( ANH_PRC_id)
>
>
>
> On Wed, Jun 6, 2018 at 11:42 AM, Karl Billeter 
> wrote:
>
> > On Wed, Jun 06, 2018 at 09:27:57AM +0300, Aydin Ozgur Yagmur wrote:
> >
> > > I have been using fat32 file system. I have a database which has 4
> > > GB
> > size.
> >
> > Er... I'm surprised there aren't more problems due to 4GB being the
> > max file size supported by fat32.  Any chance to change it to exFAT?
> >
> >
> > K
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite delete slow in 4GB

[Eduardo]

On Wed, 6 Jun 2018 12:06:15 +0300
Aydin Ozgur Yagmur  escribió:

> Thank you very quick response.
> 
> We have already planned to change file system. But this problem seem not
> related with the file size, because we did not hit the limit and when i try
> to delete same data in ,for example, 2 GB-sized db, i encounter with the
> same problem. This is my table and index definitions.

Why didn't apply the changes suggested by others on the other thread?

> Thanks for your help.
> 
> CREATE TABLE "ANHXT" (
>   "id" integer primary key autoincrement,
>   "ANH_AD" text,
>   "ANH_DBGMHWID" text,
>   "ANH_TYPE" integer,
>   "ANH_INDEXNO" int64_t)
> CREATE TABLE "PRCXT" (
>   "id" integer primary key autoincrement,
>   "ANP_SEGMENTNO" integer not null,
>   "ANP_VALUE" blob,
>   "ANH_PRC_id" bigint,
>   constraint "fk_ANHPRC_ANH_PRC" foreign key ("ANH_PRC_id") references
>  "ANHXT" ("id") on update cascade on delete cascade deferrable
> initially deferred)
> CREATE UNIQUE INDEX UQC_ANH_TYPE on ANHXT( ANH_TYPE)CREATE UNIQUE
> INDEX UQC_ANP_SEGMENTNO_ANAHTARID on PRCXT(
> ANP_SEGMENTNO,ANH_PRC_id)CREATE INDEX findex on PRCXT( ANH_PRC_id)


Move the blob to the last column

 CREATE TABLE "PRCXT" (
   "id" integer primary key autoincrement,
   "ANP_SEGMENTNO" integer not null,
   "ANH_PRC_id" bigint,
   constraint "fk_ANHPRC_ANH_PRC" foreign key ("ANH_PRC_id") references
  "ANHXT" ("id") on update cascade on delete cascade deferrable
 initially deferred),
   "ANP_VALUE" blob


even better, use a table specific for the blob:

  CREATE TABLE the_blob (
id integer primary key,
content blob
  )

the best for it, use 2 databases, one for metadata (ANHXT PRCXT tables) and
other for the blobs. Open the metadata and attach the blob. 

If you use foreign key constraint to point to blob table, don't use wal mode.
If you want/need wal mode, use an integer column to the blob id table and 
manage updates and deletes yourself.

Set these pragmas for the blob schema before creation:

pragma the_blob.autovacuum = incremental;
pragma the_blob.page_size = 65536;
pragma the_blob.cache_size = 1024; -- must set on open/attach too. 64MB

Depending on your insert/update/delete ratios, there are other patterns that 
may fit better.
 
> On Wed, Jun 6, 2018 at 11:42 AM, Karl Billeter  wrote:
> 
> > On Wed, Jun 06, 2018 at 09:27:57AM +0300, Aydin Ozgur Yagmur wrote:
> >
> > > I have been using fat32 file system. I have a database which has 4 GB
> > size.
> >
> > Er... I'm surprised there aren't more problems due to 4GB being the max
> > file
> > size supported by fat32.  Any chance to change it to exFAT?
> >

Don't use FAT32. Use your native fs, ntfs, ufs2, hfs+, hammer2, ext4,

> > K

-- 
Eduardo 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Sqlite delete slow in 4GB

[Hick Gunter]

Your schema has a major flaw that is addressed in SQLite documentation:

Do not put any fields after blob fields, especially if the content is typically 
large. SQLite accesses fields in the order of defintion, so to access your 
ANH_PRC_id field, it has to retrieve the 1MB ANP_VALUE blob.

Putting small and frequently acessed fields at the beginning of the definition 
allows SQLite to retrieve these fields without expanding the whole row.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Aydin Ozgur Yagmur
Gesendet: Mittwoch, 06. Juni 2018 11:06
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Sqlite delete slow in 4GB

Thank you very quick response.

We have already planned to change file system. But this problem seem not 
related with the file size, because we did not hit the limit and when i try to 
delete same data in ,for example, 2 GB-sized db, i encounter with the same 
problem. This is my table and index definitions.

Thanks for your help.

CREATE TABLE "ANHXT" (
  "id" integer primary key autoincrement,
  "ANH_AD" text,
  "ANH_DBGMHWID" text,
  "ANH_TYPE" integer,
  "ANH_INDEXNO" int64_t)
CREATE TABLE "PRCXT" (
  "id" integer primary key autoincrement,
  "ANP_SEGMENTNO" integer not null,
  "ANP_VALUE" blob,
==>  "ANH_PRC_id" bigint,
  constraint "fk_ANHPRC_ANH_PRC" foreign key ("ANH_PRC_id") references  "ANHXT" 
("id") on update cascade on delete cascade deferrable initially deferred) 
CREATE UNIQUE INDEX UQC_ANH_TYPE on ANHXT( ANH_TYPE)CREATE UNIQUE INDEX 
UQC_ANP_SEGMENTNO_ANAHTARID on PRCXT( ANP_SEGMENTNO,ANH_PRC_id)CREATE INDEX 
findex on PRCXT( ANH_PRC_id)



On Wed, Jun 6, 2018 at 11:42 AM, Karl Billeter  wrote:

> On Wed, Jun 06, 2018 at 09:27:57AM +0300, Aydin Ozgur Yagmur wrote:
>
> > I have been using fat32 file system. I have a database which has 4
> > GB
> size.
>
> Er... I'm surprised there aren't more problems due to 4GB being the
> max file size supported by fat32.  Any chance to change it to exFAT?
>
>
> K
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite delete slow in 4GB

[Aydin Ozgur Yagmur]

Thank you very quick response.

We have already planned to change file system. But this problem seem not
related with the file size, because we did not hit the limit and when i try
to delete same data in ,for example, 2 GB-sized db, i encounter with the
same problem. This is my table and index definitions.

Thanks for your help.

CREATE TABLE "ANHXT" (
  "id" integer primary key autoincrement,
  "ANH_AD" text,
  "ANH_DBGMHWID" text,
  "ANH_TYPE" integer,
  "ANH_INDEXNO" int64_t)
CREATE TABLE "PRCXT" (
  "id" integer primary key autoincrement,
  "ANP_SEGMENTNO" integer not null,
  "ANP_VALUE" blob,
  "ANH_PRC_id" bigint,
  constraint "fk_ANHPRC_ANH_PRC" foreign key ("ANH_PRC_id") references
 "ANHXT" ("id") on update cascade on delete cascade deferrable
initially deferred)
CREATE UNIQUE INDEX UQC_ANH_TYPE on ANHXT( ANH_TYPE)CREATE UNIQUE
INDEX UQC_ANP_SEGMENTNO_ANAHTARID on PRCXT(
ANP_SEGMENTNO,ANH_PRC_id)CREATE INDEX findex on PRCXT( ANH_PRC_id)



On Wed, Jun 6, 2018 at 11:42 AM, Karl Billeter  wrote:

> On Wed, Jun 06, 2018 at 09:27:57AM +0300, Aydin Ozgur Yagmur wrote:
>
> > I have been using fat32 file system. I have a database which has 4 GB
> size.
>
> Er... I'm surprised there aren't more problems due to 4GB being the max
> file
> size supported by fat32.  Any chance to change it to exFAT?
>
>
> K
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite delete slow in 4GB

[Karl Billeter]

On Wed, Jun 06, 2018 at 09:27:57AM +0300, Aydin Ozgur Yagmur wrote:

> I have been using fat32 file system. I have a database which has 4 GB size.

Er... I'm surprised there aren't more problems due to 4GB being the max file
size supported by fat32.  Any chance to change it to exFAT?


K
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite delete slow in 4GB

[Aydin Ozgur Yagmur]

I have been using fat32 file system. I have a database which has 4 GB size.
Database consist of 1 parent table and 1 child table. Parent table has 10
rows and child table has 4000 rows. 1 row of child table has 1 MB size.
There are unique indexes on the tables.

When I delete a row in parent table, deletion cascades 1MB-sized child
records. (pragma foreign_keys is on) When I try to delete 100 MB data by
cascade (1 parent record - 100 child records) it takes too long time
(almost 1-10 minute) to complete, and the duration increase/decrease by
size of data (100 Mb: 1-10 minute, 300 MB: 3-30 minute,etc).

I tried some pragma commands (synchronous, temp_store, journal_mode)
suggested by others posts and i also tried to add index on foreign key, but
those does not help solve my problem.(Actually, after adding index on
foreign key, 1 MB data deletion became faster/st, but 100 MB data deletion
duration did not change) Can you give me please any suggestion to increase
deletion performance?


Thanx in advance,
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users