[sqlite] Changing the default page_size in 3.12.0
On 2016/03/06 10:00 AM, Keith Medcalf wrote: > On Saturday, 5 March, 2016 12:12, Richard Hipp said: > >> A key point of this email thread is that SQLite performance seems to >> be maximized when the database page size matches the CPU memory page >> size and the disk sector size - currently 4096 bytes on most hardware. > This does not seem to be entirely true, at least not on Windows 10 1511 Pro > with a running on a very fast SSD. > I suspect it is more related to the size and shape of the data being stored > and the operations being performed. > Clearly the best thing that can be done to improve performance is increase > the internal pagecache. > > These are benchmark times for --size 500 on Windows 10 ... > > Time= 66.81, Cachesize= 65536, Pagesize= 8192, Cacheram=536870912 > Time= 67.10, Cachesize= 32768, Pagesize= 8192, Cacheram=268435456 > Time= 67.37, Cachesize= 4096, Pagesize= 8192, Cacheram=33554432 > Time= 67.92, Cachesize= 8192, Pagesize= 8192, Cacheram=67108864 > Time= 68.00, Cachesize= 16384, Pagesize= 8192, Cacheram=134217728 > Time= 68.31, Cachesize= 131072, Pagesize= 8192, Cacheram=1073741824 etc.. Fascinating stuff, thanks Keith - mind posting the DB or schema and the query(ies) used during the benchmark tests? Seems from here the 8k pagesize with 64k cache makes sense, unless that schema turns out to be wildly non-standard. Thanks, Ryan
[sqlite] Changing the default page_size in 3.12.0
On 3/6/16, R Smith wrote: > > On 2016/03/06 10:00 AM, Keith Medcalf wrote: > > mind posting the DB or schema and the > query(ies) used during the benchmark tests? > He said "using --size 500" which implies that he was running the speedtest1.exe utility program, which is part of the SQLite source tree. "make speedtest1". It starts with an empty database file, creates the schema, the populates the file, and performs many common operations. I tried to design the sequence of operations to be similar to the way I see SQLite being used in typical applications. You can get a complete listing of the SQL by running "speedtest1 --sqlonly --size 500" command. Use --help to get a complete list of options. PRAGMA threads=0; /* 100 - 25 INSERTs into table with no index. */ BEGIN; CREATE TABLE t1(a INTEGER , b INTEGER , c TEXT ); INSERT INTO t1 VALUES(?1,?2,?3); -- 25 times; COMMIT; /* 110 - 25 ordered INSERTS with one index/PK */ BEGIN; CREATE TABLE t2(a INTEGER UNIQUE, b INTEGER , c TEXT ); INSERT INTO t2 VALUES(?1,?2,?3); -- 25 times; COMMIT; /* 120 - 25 unordered INSERTS with one index/PK.. */ BEGIN; CREATE TABLE t3(a INTEGER UNIQUE, b INTEGER , c TEXT ); INSERT INTO t3 VALUES(?1,?2,?3); -- 25 times; COMMIT; /* 130 - 25 SELECTS, numeric BETWEEN, unindexed.. */ BEGIN; SELECT count(*), avg(b), sum(length(c)) FROM t1 WHERE b BETWEEN ?1 AND ?2; -- 25 times; COMMIT; /* 140 - 10 SELECTS, LIKE, unindexed. */ BEGIN; SELECT count(*), avg(b), sum(length(c)) FROM t1 WHERE c LIKE ?1; -- 10 times; COMMIT; /* 142 - 10 SELECTS w/ORDER BY, unindexed */ BEGIN; SELECT a, b, c FROM t1 WHERE c LIKE ?1 ORDER BY a; -- 10 times; COMMIT; /* 145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed.. */ BEGIN; SELECT a, b, c FROM t1 WHERE c LIKE ?1 ORDER BY a LIMIT 10; -- 10 times; COMMIT; /* 150 - CREATE INDEX five times. */ BEGIN; CREATE UNIQUE INDEX t1b ON t1(b); CREATE INDEX t1c ON t1(c); CREATE UNIQUE INDEX t2b ON t2(b); CREATE INDEX t2c ON t2(c DESC); CREATE INDEX t3bc ON t3(b,c); COMMIT; /* 160 - 5 SELECTS, numeric BETWEEN, indexed. */ BEGIN; SELECT count(*), avg(b), sum(length(c)) FROM t1 WHERE b BETWEEN ?1 AND ?2; -- 5 times; COMMIT; /* 161 - 5 SELECTS, numeric BETWEEN, PK.. */ BEGIN; SELECT count(*), avg(b), sum(length(c)) FROM t2 WHERE a BETWEEN ?1 AND ?2; -- 5 times; COMMIT; /* 170 - 5 SELECTS, text BETWEEN, indexed */ BEGIN; SELECT count(*), avg(b), sum(length(c)) FROM t1 WHERE c BETWEEN ?1 AND (?1||'~'); -- 5 times; COMMIT; /* 180 - 25 INSERTS with three indexes... */ BEGIN; CREATE TABLE t4( a INTEGER UNIQUE, b INTEGER , c TEXT ); CREATE INDEX t4b ON t4(b); CREATE INDEX t4c ON t4(c); INSERT INTO t4 SELECT * FROM t1; COMMIT; /* 190 - DELETE and REFILL one table. */ DELETE FROM t2; INSERT INTO t2 SELECT * FROM t1; /* 200 - VACUUM.. */ VACUUM; /* 210 - ALTER TABLE ADD COLUMN, and query... */ ALTER TABLE t2 ADD COLUMN d DEFAULT 123; SELECT sum(d) FROM t2; /* 230 - 5 UPDATES, numeric BETWEEN, indexed. */ BEGIN; UPDATE t2 SET d=b*2 WHERE b BETWEEN ?1 AND ?2; -- 5 times; COMMIT; /* 240 - 25 UPDATES of individual rows... */ BEGIN; UPDATE t2 SET d=b*3 WHERE a=?1; -- 25 times; COMMIT; /* 250 - One big UPDATE of the whole 25-row table */ UPDATE t2 SET d=b*4; /* 260 - Query added column after filling */ SELECT sum(d) FROM t2; /* 270 - 5 DELETEs, numeric BETWEEN, indexed. */ BEGIN; DELETE FROM t2 WHERE b BETWEEN ?1 AND ?2; -- 5 times; COMMIT; /* 280 - 25 DELETEs of individual rows... */ BEGIN; DELETE FROM t3 WHERE a=?1; -- 25 times; COMMIT; /* 290 - Refill two 25-row tables using REPLACE.. */ REPLACE INTO t2(a,b,c) SELECT a,b,c FROM t1; REPLACE INTO t3(a,b,c) SELECT a,b,c FROM t1; /* 300 - Refill a 25-row table using (b&1)==(a&1) */ DELETE FROM t2; INSERT INTO t2(a,b,c) SELECT a,b,c FROM t1 WHERE (b&1)==(a&1); INSERT INTO t2(a,b,c) SELECT a,b,c FROM t1 WHERE (b&1)<>(a&1); /* 310 - 5 four-ways joins... */ BEGIN; SELECT t1.c FROM t1, t2, t3, t4 WHERE t4.a BETWEEN ?1 AND ?2 AND t3.a=t4.b AND t2.a=t3.b AND t1.c=t2.c; COMMIT; /* 320 - subquery in result set.. */ SELECT sum(a), max(c), avg((SELECT a FROM t2 WHERE 5+t2.b=t1.b) AND rowid
[sqlite] Changing the default page_size in 3.12.0
On Saturday, 5 March, 2016 12:12, Richard Hipp said: > A key point of this email thread is that SQLite performance seems to > be maximized when the database page size matches the CPU memory page > size and the disk sector size - currently 4096 bytes on most hardware. This does not seem to be entirely true, at least not on Windows 10 1511 Pro with a running on a very fast SSD. I suspect it is more related to the size and shape of the data being stored and the operations being performed. Clearly the best thing that can be done to improve performance is increase the internal pagecache. These are benchmark times for --size 500 on Windows 10 ... Time= 66.81, Cachesize= 65536, Pagesize= 8192, Cacheram=536870912 Time= 67.10, Cachesize= 32768, Pagesize= 8192, Cacheram=268435456 Time= 67.37, Cachesize= 4096, Pagesize= 8192, Cacheram=33554432 Time= 67.92, Cachesize= 8192, Pagesize= 8192, Cacheram=67108864 Time= 68.00, Cachesize= 16384, Pagesize= 8192, Cacheram=134217728 Time= 68.31, Cachesize= 131072, Pagesize= 8192, Cacheram=1073741824 Time= 69.08, Cachesize= 16384, Pagesize=16384, Cacheram=268435456 Time= 69.71, Cachesize= 131072, Pagesize= 4096, Cacheram=536870912 Time= 69.82, Cachesize= 16384, Pagesize= 4096, Cacheram=67108864 Time= 69.90, Cachesize= 8192, Pagesize=16384, Cacheram=134217728 Time= 70.00, Cachesize= 32768, Pagesize=16384, Cacheram=536870912 Time= 70.04, Cachesize= 4096, Pagesize=16384, Cacheram=67108864 Time= 70.85, Cachesize= 65536, Pagesize=16384, Cacheram=1073741824 Time= 70.91, Cachesize= 2048, Pagesize=16384, Cacheram=33554432 Time= 70.98, Cachesize= 8192, Pagesize= 4096, Cacheram=33554432 Time= 71.32, Cachesize= 32768, Pagesize= 4096, Cacheram=134217728 Time= 71.34, Cachesize= 65536, Pagesize= 4096, Cacheram=268435456 Time= 71.73, Cachesize= 262144, Pagesize= 4096, Cacheram=1073741824 Time= 73.90, Cachesize= 1024, Pagesize=16384, Cacheram=16777216 Time= 74.24, Cachesize= 65536, Pagesize= 2048, Cacheram=134217728 Time= 74.25, Cachesize= 262144, Pagesize= 2048, Cacheram=536870912 Time= 74.32, Cachesize= 32768, Pagesize= 2048, Cacheram=67108864 Time= 74.37, Cachesize= 131072, Pagesize= 2048, Cacheram=268435456 Time= 74.94, Cachesize= 4096, Pagesize= 4096, Cacheram=16777216 Time= 74.95, Cachesize= 32768, Pagesize=32768, Cacheram=1073741824 Time= 75.13, Cachesize= 16384, Pagesize= 2048, Cacheram=33554432 Time= 75.15, Cachesize= 2048, Pagesize= 8192, Cacheram=16777216 Time= 75.68, Cachesize= 524288, Pagesize= 2048, Cacheram=1073741824 Time= 76.14, Cachesize= 4096, Pagesize=32768, Cacheram=134217728 Time= 76.46, Cachesize= 1024, Pagesize=32768, Cacheram=33554432 Time= 76.87, Cachesize= 2048, Pagesize=32768, Cacheram=67108864 Time= 77.37, Cachesize= 16384, Pagesize=32768, Cacheram=536870912 Time= 79.96, Cachesize= 8192, Pagesize=32768, Cacheram=268435456 Time= 81.68, Cachesize= 8192, Pagesize= 2048, Cacheram=16777216 Time= 82.30, Cachesize= 1024, Pagesize= 8192, Cacheram=8388608 Time= 82.41, Cachesize= 2048, Pagesize= 4096, Cacheram=8388608 Time= 83.95, Cachesize= 524288, Pagesize= 1024, Cacheram=536870912 Time= 84.06, Cachesize=1048576, Pagesize= 1024, Cacheram=1073741824 Time= 84.19, Cachesize=512, Pagesize=32768, Cacheram=16777216 Time= 84.57, Cachesize= 131072, Pagesize= 1024, Cacheram=134217728 Time= 84.59, Cachesize= 262144, Pagesize= 1024, Cacheram=268435456 Time= 84.90, Cachesize= 65536, Pagesize= 1024, Cacheram=67108864 Time= 85.62, Cachesize= 32768, Pagesize= 1024, Cacheram=33554432 Time= 88.04, Cachesize=512, Pagesize=16384, Cacheram=8388608 Time= 89.12, Cachesize= 4096, Pagesize= 2048, Cacheram=8388608 Time= 90.95, Cachesize= 16384, Pagesize= 1024, Cacheram=16777216 Time= 92.78, Cachesize=512, Pagesize= 8192, Cacheram=4194304 Time= 92.83, Cachesize= 16384, Pagesize=65536, Cacheram=1073741824 Time= 93.09, Cachesize= 1024, Pagesize= 4096, Cacheram=4194304 Time= 93.96, Cachesize= 4096, Pagesize=65536, Cacheram=268435456 Time= 96.18, Cachesize= 8192, Pagesize=65536, Cacheram=536870912 Time= 96.24, Cachesize=512, Pagesize=65536, Cacheram=33554432 Time= 96.67, Cachesize= 2048, Pagesize=65536, Cacheram=134217728 Time= 97.21, Cachesize=256, Pagesize=16384, Cacheram=4194304 Time= 98.09, Cachesize= 1024, Pagesize=65536, Cacheram=67108864 Time= 99.55, Cachesize=256, Pagesize=32768, Cacheram=8388608 Time=100.18, Cachesize= 2048, Pagesize= 2048, Cacheram=4194304 Time=102.00, Cachesize=256, Pagesize= 8192, Cacheram=2097152 Time=102.25, Cachesize=512, Pagesize= 4096, Cacheram=2097152 Time=103.90, Cachesize= 524288, Pagesize= 512, Cacheram=268435456 Time=104.04, Cachesize=2097152, Pagesize= 512, Cacheram=1073741824 Time=104.36, Cachesize=1048576, Pagesize= 512, Cacheram=536870912 Time=105.46, Cachesize= 131072, Pagesize= 512, Cacheram=67108864 Time=106.14, Cachesize= 65536, Pagesize= 512, Cacheram=33554432 Time=106.17, Cachesize= 262144, Pagesize= 512, Cacheram=134217728 Time=107.06, Cachesize=
[sqlite] Changing the default page_size in 3.12.0
On 5 Mar 2016, at 7:10pm, Paul Sanderson wrote: > The savings as mentioned earlier are IO related due to matching the > page size to the underlying hardwares block size and for larger > payloads ensuring less IO due to no or lesss overflow pages. An important point. Because of the way I design my schema, most of my tables have short rows. I rarely have more than 8 columns in a table. Because SQLite fetches/writes a whole page for each access (?) having large pages does not generally benefit me. Simon.
[sqlite] Changing the default page_size in 3.12.0
Records can span multiple pages and the record itself is unrelated to the block size. For smaller tables indexes an increase in page size can increase the DB size - consider a DB with 1024 byte pages and one table that occupies 6 pages - i.e. 6K. in a new 4096 page size DB it would take up two pages i.e. 8K. Without further investigation I suspect that savings would come in when you have larger tables with relatively large record payloads, I would think that the free space in a page would be half the average record length and so by multiplying the page size by 4 would decrease the free (wasted) space (due to not enough space for another complete record) also by a factor of four. This is grossly over simplified though and takes no account of presumably less overflow pages due the larger page size, pointer maps, and free space due to deleted records. In short (no testing done) I am not sure that for most implementations that increasing page size would make any significant space savings and may likely as mentioned above increase the DB size.. The savings as mentioned earlier are IO related due to matching the page size to the underlying hardwares block size and for larger payloads ensuring less IO due to no or lesss overflow pages. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 5 March 2016 at 18:43, Jim Callahan wrote: > > >> From: Domingo Alvarez Duarte >> I have a question, is it enough to vacuum a database to update to the new >> page size ? >> > > Apparently all you need to is "pragma page_size=4096; vacuum;" using the >> appropriate page size. >> This makes very easy to convert any(all) database(s) with a single >> t >> command from the command-line, like so (Win7 example): >> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;" > > > I am confused. > The "page" is an in-memory structure; it is how large a chunk the program > reads from the file (analogous to how large a scoop or shovel), So, unless > one is using an index, how would the on disk structure be impacted? How > does SQLite handle the last block (does it expect the file to be an even > multiple of the block size, or does it accept that the last read might be > less than a full block?). > > For example, if one encountered an old file, would it be enough to rebuild > the indices? > > Or is it simply a matter of closing the file? (close the file with the old > version and open the file with the new?). > > I haven't read the source code so I don't know what assertions, checks or > assumptions SQLite > uses. > > Jim > > > > On Sat, Mar 5, 2016 at 11:04 AM, wrote: > >> From: Domingo Alvarez Duarte >>> I have a question, is it enough to vacuum a database to update to the new >>> page size ? >>> >> >> Apparently all you need to is "pragma page_size=4096; vacuum;" using the >> appropriate page size. >> This makes very easy to convert any(all) database(s) with a single command >> from the command-line, like so (Win7 example): >> >> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;" >> >> >> ___ >> 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] Changing the default page_size in 3.12.0
On 5 Mar 2016, at 4:21pm, Domingo Alvarez Duarte wrote: > Also could the commands that take time to complete have an option to show the > completion stats ? You can do this using the SQLite shell tool: .timer ON VACUUM; .timer OFF Simon.
[sqlite] Changing the default page_size in 3.12.0
>From: Domingo Alvarez Duarte >I have a question, is it enough to vacuum a database to update to the new >page size ? Apparently all you need to is "pragma page_size=4096; vacuum;" using the appropriate page size. This makes very easy to convert any(all) database(s) with a single command from the command-line, like so (Win7 example): for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;"
[sqlite] Changing the default page_size in 3.12.0
Sorry I mean resurrect it ? >Is it too hard to survive it ? > > >>SQLite version 2 supported COPY That was dropped when we moved to SQLite >> 3. >> > > > Also could the commands that take time to complete have an option to show the completion stats ? Cheers !
[sqlite] Changing the default page_size in 3.12.0
Is it too hard to survive it ? > > SQLite version 2 supported COPY That was dropped when we moved to SQLite >3. > > > >
[sqlite] Changing the default page_size in 3.12.0
Jim Callahan wrote: > Is 4096 bytes a large enough page size? > > Apparently the disk drive industry has shifted from 512 byte sectors to > 4096 byte sectors. > http://tilt.lib.tsinghua.edu.cn/docs/tech/tp613_transition_to_4k_sectors.pdf > > Should SQLite maintain a 1:1 ratio between page size and sector size? > or should the page size be a multiple of the sector size? Say 2:1 (8096 or > 8K)? or 4:1 (16K). > > What sizes do other databases use? (SQL Server and Postgres both use 8096 > default) You mean 8192. > For years, virtual machines (VM) have used 4K pages (I think this started > with IBM VM/370); > while disk drives had 512 byte sectors (an 8:1 ratio). > > With a 2:1 ratio, in terms of seek time, one gets the second page for free. > > Would 8096 bytes (8K) be too much for a multi-tasking embedded device (such > as a smart phone?). You shouldn't even be discussing a hardcoded number. The page size should be equal to the page size of the underlying memory management system. 4K on common x86 systems, 8K on SPARC, etc. Choosing a number smaller than this will cost you in RMW ops whenever the filesystem tries to do an update. Choosing a number larger than this is generally going to waste memory. > > Are there any benchmarks? > > Jim > > > > > > > > On Fri, Mar 4, 2016 at 10:48 AM, Richard Hipp wrote: > >> The tip of trunk (3.12.0 alpha) changes the default page size for new >> database file from 1024 to 4096 bytes. >> >> https://www.sqlite.org/draft/releaselog/3_12_0.html >> https://www.sqlite.org/draft/pgszchng2016.html >> >> This seems like a potentially disruptive change, so I want to give >> you, the user community, plenty of time to consider the consequences >> and potentially talk me out of it. >> >> The "Pre-release Snapshot" on the download page >> (https://www.sqlite.org/download.html) contains this change, if you >> want to actually evaluate the latest changes in your application. >> >> We hope to release 3.12.0 in early April, or maybe even sooner, so if >> you want to provide feedback, you should do so without unnecessary >> delay. >> -- >> 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 > -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/
[sqlite] Changing the default page_size in 3.12.0
Hello ! I have a question, is it enough to vacuum a database to update to the new page size ? Or need a complete backup restore ? Also I have a database not too big 6GB but only 12GB free space on that disk, if I try a dump/restore with the actual sqlite3 maybe I'll run out of disk space, there is any chance to have compressed backup/restore ? Also PostgreSQL have different options for backup/restore like using the "COPY" command (databases dumped using COPY have smaller dumps). Cheers !
[sqlite] Changing the default page_size in 3.12.0
On 3/5/16, Jim Callahan wrote: > > ?I am confused. > The "page" is an in-memory structure; i?t is how large a chunk the program > reads from the file (analogous to how large a scoop or shovel), That is but one of many definitions of "page". That word "page" gets used (and misused) for a lot of things in computer programming. Like "virtual", its meaning can shift depending on context. An SQLite database file (see https://www.sqlite.org/fileformat2.html) consists of one or more "pages" of content. A "page" in an SQLite database file is not the same thing as a "page" in the virtual memory design of your CPU. But they are often roughly the same size. A key point of this email thread is that SQLite performance seems to be maximized when the database page size matches the CPU memory page size and the disk sector size - currently 4096 bytes on most hardware. > So, unless > one is using an index, how would the on disk structure be impacted? How > does SQLite handle the last block (does it expect the file to be an even > multiple of the block size, or does it accept that the last read might be > less than a full block?). > > For example, if one encountered an old file, would it be enough to rebuild > the indices? > ? > Or is it simply a matter of closing the file? (close the file with the old > version and open the file with the new?). > > I haven't read the source code so I don't know what assertions, checks or > assumptions SQLite > uses. > > Jim? > > > > On Sat, Mar 5, 2016 at 11:04 AM, wrote: > >> From: Domingo Alvarez Duarte >>> I have a question, is it enough to vacuum a database to update to the new >>> page size ? >>> >> >> Apparently all you need to is "pragma page_size=4096; vacuum;" using the >> appropriate page size. >> This makes very easy to convert any(all) database(s) with a single command >> from the command-line, like so (Win7 example): >> >> for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;" >> >> >> ___ >> 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] Changing the default page_size in 3.12.0
?? > From: Domingo Alvarez Duarte > I have a question, is it enough to vacuum a database to update to the new > page size ? > Apparently all you need to is "pragma page_size=4096; vacuum;" using the > appropriate page size. > This makes very easy to convert any(all) database(s) with a single > ?t? > command from the command-line, like so (Win7 example): > for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;" ?I am confused. The "page" is an in-memory structure; i?t is how large a chunk the program reads from the file (analogous to how large a scoop or shovel), So, unless one is using an index, how would the on disk structure be impacted? How does SQLite handle the last block (does it expect the file to be an even multiple of the block size, or does it accept that the last read might be less than a full block?). For example, if one encountered an old file, would it be enough to rebuild the indices? ? Or is it simply a matter of closing the file? (close the file with the old version and open the file with the new?). I haven't read the source code so I don't know what assertions, checks or assumptions SQLite uses. Jim? On Sat, Mar 5, 2016 at 11:04 AM, wrote: > From: Domingo Alvarez Duarte >> I have a question, is it enough to vacuum a database to update to the new >> page size ? >> > > Apparently all you need to is "pragma page_size=4096; vacuum;" using the > appropriate page size. > This makes very easy to convert any(all) database(s) with a single command > from the command-line, like so (Win7 example): > > for %i in (*.db) do sqlite3 %i "pragma page_size=4096; vacuum;" > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Changing the default page_size in 3.12.0
>From: Richard Hipp >Can you run sqlite3_analyzer on some of your databases and send me the >output? I will as soon as I can manage to build it under Windows. Although I regularly build sqlite3, lemon, and sqldiff, with sqlite3_analyzer I get a lot of errors like these: ... sqlite3_analyzer.obj : error LNK2019: unresolved external symbol __imp__Tcl_NRCallObjProc referenced in function _DbObjCmdAdaptor sqlite3_analyzer.exe : fatal error LNK1120: 59 unresolved externals
[sqlite] Changing the default page_size in 3.12.0
On 3/5/16, Domingo Alvarez Duarte wrote: > Is it too hard to survive it ? >> >> SQLite version 2 supported COPY That was dropped when we moved to SQLite >>3. >> Yes. -- D. Richard Hipp drh at sqlite.org
[sqlite] Changing the default page_size in 3.12.0
On 3/5/16, Domingo Alvarez Duarte wrote: > > I have a question, is it enough to vacuum a database to update to the new > page size ? The new page size only applies to newly created database files. VACUUM-ing an existing database file will not change its page size. However, if you say "PRAGMA page_size=N; VACUUM;" (for some N) that will change the page size if you are not in WAL mode. > > Also I have a database not too big 6GB but only 12GB free space on that > disk, > if I try a dump/restore with the actual sqlite3 maybe I'll run out of disk > space, there is any chance to have compressed backup/restore ? You'll probably run out of space if you try to VACUUM. 12GB isn't very much. USB thumb-drives are usually bigger than that. You can't get better hardware? > > Also PostgreSQL have different options for backup/restore like using the > "COPY" command (databases dumped using COPY have smaller dumps). > SQLite version 2 supported COPY. That was dropped when we moved to SQLite 3. -- D. Richard Hipp drh at sqlite.org
[sqlite] Changing the default page_size in 3.12.0
On 5 Mar 2016, at 10:07am, tonyp at acm.org wrote: >> From: Richard Hipp >> Can you run sqlite3_analyzer on some of your databases and send me the >> output? > > I will as soon as I can manage to build it under Windows. Can download pre-built binaries for Windows and other popular platforms. Simon.
[sqlite] Changing the default page_size in 3.12.0
>From: tonyp at acm.org >To: SQLite mailing list >Subject: Re: [sqlite] Changing the default page_size in 3.12.0 >I ran some tests and almost all of my databases (about 100 of them with >different content mix and with the biggest one being around 500MB) inflated >by a lot while only a couple or so shrunk in size by not much. Really?
[sqlite] Changing the default page_size in 3.12.0
On 3/5/16, Jim Callahan wrote: > Is 4096 bytes a large enough page size? > > Are there any benchmarks? > https://www.sqlite.org/tmp/small-v-large-cache.jpg Results from running "rm -f x.db; time ./speedtest1 x.db --size 400 --release-memory --pagesize N" (after "make speedtest1") off of the tip of trunk yesterday, on a fast Ubuntu machine. -- D. Richard Hipp drh at sqlite.org
[sqlite] Changing the default page_size in 3.12.0
Is 4096 bytes a large enough page size? Apparently the disk drive industry has shifted from 512 byte sectors to 4096 byte sectors. http://tilt.lib.tsinghua.edu.cn/docs/tech/tp613_transition_to_4k_sectors.pdf Should SQLite maintain a 1:1 ratio between page size and sector size? or should the page size be a multiple of the sector size? Say 2:1 (8096 or 8K)? or 4:1 (16K). What sizes do other databases use? (SQL Server and Postgres both use 8096 default) For years, virtual machines (VM) have used 4K pages (I think this started with IBM VM/370); while disk drives had 512 byte sectors (an 8:1 ratio). With a 2:1 ratio, in terms of seek time, one gets the second page for free. Would 8096 bytes (8K) be too much for a multi-tasking embedded device (such as a smart phone?). Are there any benchmarks? Jim On Fri, Mar 4, 2016 at 10:48 AM, Richard Hipp wrote: > The tip of trunk (3.12.0 alpha) changes the default page size for new > database file from 1024 to 4096 bytes. > > https://www.sqlite.org/draft/releaselog/3_12_0.html > https://www.sqlite.org/draft/pgszchng2016.html > > This seems like a potentially disruptive change, so I want to give > you, the user community, plenty of time to consider the consequences > and potentially talk me out of it. > > The "Pre-release Snapshot" on the download page > (https://www.sqlite.org/download.html) contains this change, if you > want to actually evaluate the latest changes in your application. > > We hope to release 3.12.0 in early April, or maybe even sooner, so if > you want to provide feedback, you should do so without unnecessary > delay. > -- > 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] Changing the default page_size in 3.12.0
On 3/5/16, tonyp at acm.org wrote: >>From: Richard Hipp >>Can you run sqlite3_analyzer on some of your databases and send me the >>output? > > I will as soon as I can manage to build it under Windows. Although I > regularly build sqlite3, lemon, and sqldiff, with sqlite3_analyzer I get a > lot of errors like these: > > ... > sqlite3_analyzer.obj : error LNK2019: unresolved external symbol > __imp__Tcl_NRCallObjProc referenced in function _DbObjCmdAdaptor > sqlite3_analyzer.exe : fatal error LNK1120: 59 unresolved externals > The sqlite3_analyzer.exe links against libtcl.dll. Either you don't have that, or else the version you have is really old, or maybe you have the 32-bit version and you are trying to link it into a 64-bit build. But if you download the pre-release snapshot and type "nmake /f Makefile.msc sqlite3.dll" or "nmake /f Makefile.msc sqlite3.exe" those should work just fine. -- D. Richard Hipp drh at sqlite.org
[sqlite] Changing the default page_size in 3.12.0
AFAIK, 4, 8, 16 KB sized I/O based on flash memory shows better performance. (1.x ~ 3.x) As a result, Android uses 4KB page size. It's good to embedded devices using flash memory. 2016? 3? 5? (?) 11:22, Donald Shepherd ?? ??: > On Sat, 5 Mar 2016 at 09:19 Roger Binns wrote: > > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > On 04/03/16 07:48, Richard Hipp wrote: > > > The tip of trunk (3.12.0 alpha) changes the default page size for > > > new database file from 1024 to 4096 bytes. ... This seems like a > > > potentially disruptive change, so I want to give you, the user > > > community, plenty of time to consider the consequences and > > > potentially talk me out of it. > > > > Can I talk you into it instead :-) My standard boilerplate for new > > databases is to set the page size to 4,096 bytes, and to turn on WAL. > > > > Roger > > > > We've headed the same way. 4,096 significantly reduced the size of our > databases when we switched to it some time ago. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Changing the default page_size in 3.12.0
On Sat, 5 Mar 2016 at 09:19 Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 04/03/16 07:48, Richard Hipp wrote: > > The tip of trunk (3.12.0 alpha) changes the default page size for > > new database file from 1024 to 4096 bytes. ... This seems like a > > potentially disruptive change, so I want to give you, the user > > community, plenty of time to consider the consequences and > > potentially talk me out of it. > > Can I talk you into it instead :-) My standard boilerplate for new > databases is to set the page size to 4,096 bytes, and to turn on WAL. > > Roger > We've headed the same way. 4,096 significantly reduced the size of our databases when we switched to it some time ago.
[sqlite] Changing the default page_size in 3.12.0
I ran some tests and almost all of my databases (about 100 of them with different content mix and with the biggest one being around 500MB) inflated by a lot while only a couple or so shrunk in size by not much. (It could be that I'm just unlucky with my database contents and this change simply happens to affect me in a negative way while for most users the results would be different.) For me, this effect alone is enough reason to not wish a change of the default size as I would always have to manually change it back. Database size is more important (to me) than access times. What is the expected gain of this change supposed to be (not only in terms of database size but in general)? Is it just for hypothetical access speed improvements? And, if so, for what size databases? (I would suspect only for the very large ones for which usually one wouldn't normally choose SQLite3.) (BTW, will this change also affect FOSSIL repos?) -Original Message- From: Richard Hipp Sent: Friday, March 04, 2016 5:48 PM To: General Discussion of SQLite Database ; sqlite-dev Subject: [sqlite] Changing the default page_size in 3.12.0 The tip of trunk (3.12.0 alpha) changes the default page size for new database file from 1024 to 4096 bytes. https://www.sqlite.org/draft/releaselog/3_12_0.html https://www.sqlite.org/draft/pgszchng2016.html This seems like a potentially disruptive change, so I want to give you, the user community, plenty of time to consider the consequences and potentially talk me out of it. The "Pre-release Snapshot" on the download page (https://www.sqlite.org/download.html) contains this change, if you want to actually evaluate the latest changes in your application. We hope to release 3.12.0 in early April, or maybe even sooner, so if you want to provide feedback, you should do so without unnecessary delay. -- 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] Changing the default page_size in 3.12.0
On 03/04/2016 11:03 PM, Dominique Devienne wrote: > On Fri, Mar 4, 2016 at 4:48 PM, Richard Hipp wrote: > >> https://www.sqlite.org/draft/releaselog/3_12_0.html > > - from 2000 to -2000 > + from 2000 to 500 > > [OT] The PRAGMA defer_foreign_keys=ON statement now also disables RESTRICT > actions on foreign key. > > I've confused by that Richard. Does that mean handling of the RESTRICT is > also postponed to commit-time, > or that RESTRICT is not honored in deferred FK mode (and thus violating > referential integrity). --DD Technically SQLite does the latter, but I don't think that opens up any new opportunities to violate referential integrity. But as far as I can tell "the RESTRICT is also postponed to commit-time" and "RESTRICT is not honored" are indistinguishable from the users point of view. See the notes on RESTRICT here: http://sqlite.org/foreignkeys.html#fk_actions Dan.
[sqlite] Changing the default page_size in 3.12.0
Ever-since you suggested to me to change the default page size to 4096, a few years back, I have always set my default to that size. jos? -Original Message- From: Richard Hipp Sent: Friday, March 04, 2016 10:48 AM To: General Discussion of SQLite Database ; sqlite-dev Subject: [sqlite] Changing the default page_size in 3.12.0 The tip of trunk (3.12.0 alpha) changes the default page size for new database file from 1024 to 4096 bytes. https://www.sqlite.org/draft/releaselog/3_12_0.html https://www.sqlite.org/draft/pgszchng2016.html This seems like a potentially disruptive change, so I want to give you, the user community, plenty of time to consider the consequences and potentially talk me out of it. The "Pre-release Snapshot" on the download page (https://www.sqlite.org/download.html) contains this change, if you want to actually evaluate the latest changes in your application. We hope to release 3.12.0 in early April, or maybe even sooner, so if you want to provide feedback, you should do so without unnecessary delay. -- 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] Changing the default page_size in 3.12.0
On 3/4/16, tonyp at acm.org wrote: > I ran some tests and almost all of my databases (about 100 of them with > different content mix and with the biggest one being around 500MB) inflated Can you run sqlite3_analyzer on some of your databases and send me the output? > > by a lot while only a couple or so shrunk in size by not much. (It could be > > that I'm just unlucky with my database contents and this change simply > happens to affect me in a negative way while for most users the results > would be different.) > > For me, this effect alone is enough reason to not wish a change of the > default size as I would always have to manually change it back. Database > size is more important (to me) than access times. > > What is the expected gain of this change supposed to be (not only in terms > of database size but in general)? Is it just for hypothetical access speed In my tests, a 4096-byte page runs about 5% faster (measured using cachegrind) than a 1024-byte page. Real-world timings are harder to make, because on a modern machine, the same program will vary in performance by 5% from one run to the next, but I think I'm seeing closer to 10% faster. Nothing about this change forces you to use a larger page size. The "PRAGMA page_size=1024" command still works fine, as does the -DSQLITE_DEFAULT_PAGE_SIZE=1024 compile-time option. > > improvements? And, if so, for what size databases? (I would suspect only > for the very large ones for which usually one wouldn't normally choose > SQLite3.) > > (BTW, will this change also affect FOSSIL repos?) > > -Original Message- > From: Richard Hipp > Sent: Friday, March 04, 2016 5:48 PM > To: General Discussion of SQLite Database ; sqlite-dev > Subject: [sqlite] Changing the default page_size in 3.12.0 > > The tip of trunk (3.12.0 alpha) changes the default page size for new > database file from 1024 to 4096 bytes. > > https://www.sqlite.org/draft/releaselog/3_12_0.html > https://www.sqlite.org/draft/pgszchng2016.html > > This seems like a potentially disruptive change, so I want to give > you, the user community, plenty of time to consider the consequences > and potentially talk me out of it. > > The "Pre-release Snapshot" on the download page > (https://www.sqlite.org/download.html) contains this change, if you > want to actually evaluate the latest changes in your application. > > We hope to release 3.12.0 in early April, or maybe even sooner, so if > you want to provide feedback, you should do so without unnecessary > delay. > -- > 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] Changing the default page_size in 3.12.0
On Fri, Mar 4, 2016 at 4:48 PM, Richard Hipp wrote: > https://www.sqlite.org/draft/releaselog/3_12_0.html - from 2000 to -2000 + from 2000 to 500 [OT] The PRAGMA defer_foreign_keys=ON statement now also disables RESTRICT actions on foreign key. I've confused by that Richard. Does that mean handling of the RESTRICT is also postponed to commit-time, or that RESTRICT is not honored in deferred FK mode (and thus violating referential integrity). --DD
[sqlite] Changing the default page_size in 3.12.0
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/03/16 07:48, Richard Hipp wrote: > The tip of trunk (3.12.0 alpha) changes the default page size for > new database file from 1024 to 4096 bytes. ... This seems like a > potentially disruptive change, so I want to give you, the user > community, plenty of time to consider the consequences and > potentially talk me out of it. Can I talk you into it instead :-) My standard boilerplate for new databases is to set the page size to 4,096 bytes, and to turn on WAL. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlbaCegACgkQmOOfHg372QRaLQCg1jC4d3iCqSTLDqLD4Eqsfh4y SIEAnizgfhlyyFasZng8QpsSrVo6OpD0 =8zgy -END PGP SIGNATURE-
[sqlite] Changing the default page_size in 3.12.0
On 3/4/16, Dominique Devienne wrote: > > [OT] The PRAGMA defer_foreign_keys=ON statement now also disables RESTRICT > actions on foreign key. > > Does that mean handling of the RESTRICT is > also postponed to commit-time, > or that RESTRICT is not honored in deferred FK mode (and thus violating > referential integrity). The latter. -- D. Richard Hipp drh at sqlite.org
[sqlite] Changing the default page_size in 3.12.0
The tip of trunk (3.12.0 alpha) changes the default page size for new database file from 1024 to 4096 bytes. https://www.sqlite.org/draft/releaselog/3_12_0.html https://www.sqlite.org/draft/pgszchng2016.html This seems like a potentially disruptive change, so I want to give you, the user community, plenty of time to consider the consequences and potentially talk me out of it. The "Pre-release Snapshot" on the download page (https://www.sqlite.org/download.html) contains this change, if you want to actually evaluate the latest changes in your application. We hope to release 3.12.0 in early April, or maybe even sooner, so if you want to provide feedback, you should do so without unnecessary delay. -- D. Richard Hipp drh at sqlite.org
