(SOLVED) any idea what went wrong on this 4TB db to cause "database or disk is full" ?
(1.2) By hanshenrik on 2022-05-04 14:14:05 edited from 1.1 [source]
Edit: good job guys, mystery solved, the problem was, indeed, running out of PRAGMA max_page_count :)
any idea what went wrong here? because i don't see the trigger
sqlite3 --version - 3.38.1 2022-03-12 13:37:29 38c210fdd258658321c85ec9c01a072fda3ada94540e3239d29b34dc547a8cbc
df -h .:
Filesystem Size Used Avail Use% Mounted on /dev/sda3 15T 13T 1.5T 90% /
/def/sda3: /dev/sda3 / ext4 rw,relatime 0 0
tune2fs 1.46.3 (27-Jul-2021)
Filesystem volume name: <none>
Last mounted on: /
Filesystem UUID: 7c04d7fe-d3c0-4315-8c3b-2ad95ae35bb3
Filesystem magic number: 0xEF53
Filesystem revision #: 1 (dynamic)
Filesystem features: has_journal ext_attr resize_inode dir_index filetype needs_recovery extent 64bit flex_bg sparse_super large_file huge_file dir_nlink extra_isize metadata_csum
Filesystem flags: signed_directory_hash
Default mount options: user_xattr acl
Filesystem state: clean
Errors behavior: Continue
Filesystem OS type: Linux
Inode count: 487227392
Block count: 3897818619
Reserved block count: 194890930
Overhead clusters: 31002985
Free blocks: 3866166813
Free inodes: 487168398
First block: 0
Block size: 4096
Fragment size: 4096
Group descriptor size: 64
Reserved GDT blocks: 189
Blocks per group: 32768
Fragments per group: 32768
Inodes per group: 4096
Inode blocks per group: 256
Flex block group size: 16
Filesystem created: Wed Mar 16 19:08:05 2022
Last mount time: Wed Mar 16 20:30:49 2022
Last write time: Wed Mar 16 20:30:47 2022
Mount count: 3
Maximum mount count: -1
Last checked: Wed Mar 16 19:08:05 2022
Check interval: 0 (<none>)
Lifetime writes: 7956 MB
Reserved blocks uid: 0 (user root)
Reserved blocks gid: 0 (group root)
First inode: 11
Inode size: 256
Required extra isize: 32
Desired extra isize: 32
Journal inode: 8
First orphan inode: 140575613
Default directory hash: half_md4
Directory Hash Seed: 038a70d8-efbb-42bf-82b0-fbec5af36583
Journal backup: inode blocks
Checksum type: crc32c
Checksum: 0x61c95667
strace:
pread64(5, "\0\0\0\1", 4, 512) = 4
pread64(5, "SQLite format 3\0\20\0\1\1\0@ \0\0\0\7>\3202x"..., 4096, 516) = 4096
pread64(5, "\307+N\315", 4, 4612) = 4
pwrite64(3, "SQLite format 3\0\20\0\1\1\0@ \0\0\0\7>\3202x"..., 4096, 0) = 4096
pread64(5, ">\320.\233", 4, 4616) = 4
pread64(5, "\5\fO\1T\2\365\0>\3202v\fF\f=\f4\f+\f\"\f\31\f\20\f\7\v\376\v\365"..., 4096, 4620) = 4096
pread64(5, "\307+U\270", 4, 8716) = 4
pwrite64(3, "\5\fO\1T\2\365\0>\3202v\fF\f=\f4\f+\f\"\f\31\f\20\f\7\v\376\v\365"..., 4096, 4316490997760) = 4096
pread64(5, ">\313\22\10", 4, 8720) = 4
pread64(5, "\5\r\212\1]\2\376\0>\320.\233\r\201\rx\ro\rf\r]\rT\rK\rB\r9\r0"..., 4096, 8724) = 4096
pread64(5, "\307+V/", 4, 12820) = 4
pwrite64(3, "\5\r\212\1]\2\376\0>\320.\233\r\201\rx\ro\rf\r]\rT\rK\rB\r9\r0"..., 4096, 4315118858240) = 4096
pread64(5, ">\320$\346", 4, 12824) = 4
pread64(5, "\5\0\0\1b\3\216\0>\320( \17\367\17\356\17\345\17\334\17\323\17\312\17\301\17\270\17\257\17\246"..., 4096, 12828) = 4096
pread64(5, "\307+V\31", 4, 16924) = 4
pwrite64(3, "\5\0\0\1b\3\216\0>\320( \17\367\17\356\17\345\17\334\17\323\17\312\17\301\17\270\17\257\17\246"..., 4096, 4316480819200) = 4096
pread64(5, ">\320)\263", 4, 16928) = 4
pread64(5, "\5\0\0\1b\3\216\0>\320-r\16\362\16\351\16\340\16\327\16\316\16\305\16\274\16\263\16\252\16\241"..., 4096, 16932) = 4096
pread64(5, "\307+V\366", 4, 21028) = 4
pwrite64(3, "\5\0\0\1b\3\216\0>\320-r\16\362\16\351\16\340\16\327\16\316\16\305\16\274\16\263\16\252\16\241"..., 4096, 4316485853184) = 4096
pread64(5, "\0\0\0\0", 4, 21512) = 4
pread64(5, "\263\242\353\242", 4, 21516) = 4
pread64(5, ">\3202x", 4, 21520) = 4
pread64(5, ";\r\243~", 4, 22016) = 4
pread64(5, "\5\0\0\1A\4\267\0>\313\22\10\17\367\17\356\17\345\17\334\17\323\17\312\17\301\17\270\17\257\17\246"..., 4096, 22020) = 4096
pread64(5, "\263\242\363\243", 4, 26116) = 4
pread64(5, ">\274\331\233", 4, 26120) = 4
pread64(5, "\5\0\0\1Q\4'\0>\3017\346\17\367\17\356\17\345\17\334\17\323\17\312\17\301\17\270\17\257\17\246"..., 4096, 26124) = 4096
pread64(5, "\263\242\364>", 4, 30220) = 4
pread64(5, ">\303\364\24", 4, 30224) = 4
pread64(5, "\5\0\0\1P\0040\0>\310\202\302\17\213\17\202\17y\17p\17g\17^\17U\17L\17C\17:"..., 4096, 30228) = 4096
pread64(5, "\263\242\364_", 4, 34324) = 4
newfstatat(5, "", {st_mode=S_IFREG|0644, st_size=34328, ...}, AT_EMPTY_PATH) = 0
pread64(5, "\306\3704>", 4, 34312) = 4
fdatasync(3) = 0
close(5) = 0
unlink("/path/to/database.db-journal") = 0
fcntl(3, F_SETLK, {l_type=F_RDLCK, l_whence=SEEK_SET, l_start=1073741826, l_len=510}) = 0
fcntl(3, F_SETLK, {l_type=F_UNLCK, l_whence=SEEK_SET, l_start=1073741824, l_len=2}) = 0
fcntl(3, F_SETLK, {l_type=F_UNLCK, l_whence=SEEK_SET, l_start=0, l_len=0}) = 0
fcntl(4, F_SETLK, {l_type=F_UNLCK, l_whence=SEEK_SET, l_start=0, l_len=0}) = 0
write(2, "Error: near line 1: database or "..., 45Error: near line 1: database or disk is full
) = 45
read(0, "", 4096) = 0
newfstatat(AT_FDCWD, "/path/to/database.db", {st_mode=S_IFREG|0644, st_size=4316495052800, ...}, 0) = 0
newfstatat(3, "", {st_mode=S_IFREG|0644, st_size=4316495052800, ...}, AT_EMPTY_PATH) = 0
newfstatat(AT_FDCWD, "/path/to/database.db", {st_mode=S_IFREG|0644, st_size=4316495052800, ...}, 0) = 0
close(3) = 0
newfstatat(AT_FDCWD, "/path/to/database.db", {st_mode=S_IFREG|0644, st_size=1217739448320, ...}, 0) = 0
newfstatat(4, "", {st_mode=S_IFREG|0644, st_size=1217739448320, ...}, AT_EMPTY_PATH) = 0
newfstatat(AT_FDCWD, "/path/to/database.db", {st_mode=S_IFREG|0644, st_size=1217739448320, ...}, 0) = 0
close(4) = 0
exit_group(1) = ?
+++ exited with 1 +++
(2) By MBL (UserMBL) on 2022-05-03 09:38:59 in reply to 1.0 [link] [source]
4TB and disk full
looks to me as if the file system is doing a restriction like FAT32. What is your storage system?
(3) By Dan Kennedy (dan) on 2022-05-03 10:51:26 in reply to 2 [link] [source]
4TB and disk full
What's the database page size?
4TB would be the maximum db size if your page size was 1024 bytes.
Dan.
(4) By Gunter Hick (gunter_hick) on 2022-05-03 11:09:17 in reply to 3 [link] [source]
Guessing from the strace showing 4K file operations I would venture to state page size as 4K
(6) By anonymous on 2022-05-03 11:57:47 in reply to 4 [link] [source]
The SQLITE_MAX_PAGE_COUNT parameter, which is normally set to 1073741823, is the maximum number of pages allowed in a single database file.
(7) By ddevienne on 2022-05-03 12:14:35 in reply to 6 [link] [source]
See paragraph 12 from the limits doc.
(8) By hanshenrik on 2022-05-03 16:58:15 in reply to 2 [link] [source]
added to top post now, its ext4 with 4KiB block size
(10) By Warren Young (wyoung) on 2022-05-03 22:20:08 in reply to 8 [link] [source]
Mystery solved: 1073741823 × 4096 ≈ 4 TiB
Increase the max page count or increase the page size.
(13) By hanshenrik on 2022-05-04 14:20:16 in reply to 10 [link] [source]
perfect thanks, running out of "PRAGMA max_page_count" was the issue indeed :)
(5) By anonymous on 2022-05-03 11:55:27 in reply to 1.0 [link] [source]
It would help if you can add more info, e.g.:
What's your OS and filesystem? What's the amount of the available space reported by the partition? Your page size? (Can be inferred from the trace but just to confirm) Are you able to create and append to files on the same partition outside of SQLite?
(9) By hanshenrik on 2022-05-03 17:00:23 in reply to 5 [link] [source]
added more info to top post
Are you able to create and append to files on the same partition outside of SQLite?
yes
(11.1) By ddevienne on 2022-05-04 14:25:07 edited from 11.0 in reply to 1.1 [link] [source]
Just to be clear, you've added info about the file-system, but not the DB.
And even though we infered the page_size of the database from the strace,
you can double-check with pragma page_size and pragma page_count that
the limit you are hitting is really what we (the forum community) diagnosed.
If you are indeed using the default max_page_count, you have a 4x headroom
by simply increasing it. I think the effect is immediate. OTHO, if you also
want to increase the page_size, you need to VACUUM for it to take effect.
With the max page_size of 64KB, and max max_page_count of 4B, you can reach
218TB according to the doc (and math).
(12) By hanshenrik on 2022-05-04 14:15:11 in reply to 11.0 [link] [source]
good job, running out of "PRAGMA max_page_count" was indeed the issue here :) mystery solved.