Dear all,
SQLite is wonderful. Thank you for this piece of software.
I have a problem however with large tables > 1M rows.
I am using the latest of everything:
SQLite 3.2.2
DBI 1.48
DBD::SQLite 1.09
AIX 4.3.3
Native C compiler.
The Perl is one rev behind at 5.8.6 but I get an identical issue on
5.8.7 on another machine running AIX 5.1.
---
With 32-bit compile, I get exactly the same problem whether I do this
from the sqlite command line or from with Perl (which is what I need to
be doing).
In the following, select count(kp) from answers_read
gives:
1338605
- yes, there is a lot of data here.
The first query runs fine:
sqlite>
select
r.kp,
substr(r.kp,1,13) as records,
r.result2,
r.result4,
r.result12,
min(1,(r.arecords2/100)) as ap2,
min(1,(r.arecords4/100)) as ap4,
min(1,(r.arecords12/100)) as ap12,
min(1,(r.arecords2/100))*d.dhj as ad2,
min(1,(r.arecords4/100))*d.dhj as ad4,
min(1,(r.arecords12/100))*d.dhj as ad12,
d.ourgrps,
d.dhj
from
answers_read r,
dhj_pp d
where
r.kp = d.kp and
r.kp like 'UNC788Y%'
order by
r.kp,
r.cost
limit
10;
UNC788YKJLMNOQQQ|UNC788YKJLMNO|0|0|30|0.3|0.3|0.3|1.8350297805|1.8350297805|1.8350297805|1|6.116765935
UNC788YKJLMNOQQQ|UNC788YKJLMNO|0|30|30|0.3|0.3|0.3|1.8350297805|1.8350297805|1.8350297805|1|6.116765935
UNC788YKJLMNOQQQ|UNC788YKJLMNO|30|30|30|0.3|0.3|0.3|1.8350297805|1.8350297805|1.8350297805|1|6.116765935
UNC788YKJLMNOQQQ|UNC788YKJLMNO|0|0|50|0.4999|0.5|0.5|3.0577712909065|3.0583829675|3.0583829675|1|6.116765935
UNC788YKJLMNOQQQ|UNC788YKJLMNO|0|50|50|0.4999|0.5|0.5|3.0577712909065|3.0583829675|3.0583829675|1|6.116765935
UNC788YKJLMNOQQQ|UNC788YKJLMNO|30|50|50|0.4999|0.5|0.5|3.0577712909065|3.0583829675|3.0583829675|1|6.116765935
UNC788YKJLMNOQQQ|UNC788YKJLMNO|50|50|50|0.5|0.5001|0.5001|3.0583829675|3.0589946440935|3.0589946440935|1|6.116765935
UNC788YKJLMNOQQQ|UNC788YKJLMNO|0|0|55|0.5499|0.55|0.55|3.3636095876565|3.36422126425|3.36422126425|1|6.116765935
UNC788YKJLMNOQQQ|UNC788YKJLMNO|0|55|55|0.5499|0.55|0.55|3.3636095876565|3.36422126425|3.36422126425|1|6.116765935
UNC788YKJLMNOQQQ|UNC788YKJLMNO|30|55|55|0.5499|0.55|0.55|3.3636095876565|3.36422126425|3.36422126425|1|6.116765935
However, when building the "total" query (which is what is desired), I
get a core dump.
sqlite>
select
r.kp,
substr(r.kp,1,13) as records,
r.result2,
r.result4,
r.result12,
min(1,(r.arecords2/100)) as ap2,
min(1,(r.arecords4/100)) as ap4,
min(1,(r.arecords12/100)) as ap12,
min(1,(r.arecords2/100))*d.dhj as ad2,
min(1,(r.arecords4/100))*d.dhj as ad4,
min(1,(r.arecords12/100))*d.dhj as ad12,
d.ourgrps,
d.dhj
from
answers_read r,
dhj_pp d
where
r.kp = d.kp
order by
r.kp,
r.cost
limit
10;
Illegal instruction (core dumped)
(The difference is that the kp field isn't restricted by a like clause)
I have built with debugging on, and can't do anything with the core
dump:
dbx
Type 'help' for help.
enter object file name (default is `a.out', ^D to exit): sqlite3
reading symbolic information ...
[using memory image in core]
Illegal instruction (reserved addressing fault) in . at 0x0 ($t1)
warning: Unable to access address 0x0 from core
0x00000000 warning: Unable to access address 0x0 from core
ffffffff warning: Unable to access address 0x0 from core
fnmadd. fr31,fr31,fr31,fr31
I *did* get a trace on one other occasion which indicated that the
problem might be in btree.c, but I can't reproduce that for the above
query now - been making so many changes to things. I have one for a
different query to the same DB (different table) below.
I have tried every possible alternative of everything that I can think
of: changed cachec_size up and down, changed page_size up and down, -
turned synchronous on and off etc.
I *can* make the query work if I select fewer columns, (but again, this
isn't an option for the table being built - too much messing around and
too slow to recoalesce later.)
This looks like a memory problem in the returned select producing too
much data somewhere and overrunning. I suspected the page_size - hoiked
that up to 32768 in pager.h - no dice.
---
A 64-bit version of sqlite3 *does* appear to work (at the command line),
on this query, but I can't build a 64-bit DBD driver that will also
work.
I mean, - I can *build* it with the same compiler flags and run with it
but it bombs out at a different point (i.e. a different SQL statement
than above) for reasons that I have not been able to track down (and
I've been seeking to figure this out for 2 full days now!)
Same db file, different table:
select
*
from
(
select
*
from
flow_read
order by
fl1, bikes, miles, bo, fac
)
group by
fl1
order by
fl1
Here's the dbx o/p of that (with the DBD::SQLite driver at least
compiled with
-g):
dbx
Type 'help' for help.
enter object file name (default is `a.out', ^D to exit):
/home/derjones/perl/local/bin/perl
reading symbolic information ...warning: stab entry unrecognized: name
ch_table1,type 9ff, class ff, value 9ffffff'
[using memory image in core]
Segmentation fault in moveeq.memcpy
[/home/derjones/perl/local/lib/site_perl/5.8.6/aix-thread-multi-64all/auto/DBD/SQLite/SQLite.so]
at 0x9000000015debf8 ($t1)
0x9000000015debf8 (memcpy+0x78) f8e60009 stdu r7,0x8(r6)
(dbx) where
moveeq.memcpy() at 0x9000000015debf8
balance_nonroot(??), line 3938 in "btree.c"
balance_deeper(??), line 4457 in "btree.c"
balance(??, ??), line 4472 in "btree.c"
sqlite3BtreeInsert(??, ??, ??, ??, ??), line 4583 in "btree.c"
sqlite3VdbeExec(??), line 3151 in "vdbe.c"
sqlite3_step(??), line 209 in "vdbeapi.c"
sqlite_st_execute(??, ??), line 408 in "dbdimp.c"
unnamed block $b27, line 548 in "SQLite.xsi"
XS_DBD__SQLite__st_execute(??, ??), line 548 in "SQLite.xsi"
XS_DBI_dispatch() at 0x9000000015484d8
Perl_pp_entersub() at 0x10007234c
Perl_runops_standard() at 0x1000ecdf8
S_run_body() at 0x10003646c
perl_run() at 0x10003d030
perlmain.main() at 0x100000568
and here's one sample compile line of the 64-bit driver:
cc_r -c -I.
-I/home/derjones/perl/local/lib/site_perl/5.8.6/aix-thread-multi-64all/auto/DBI/
-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=-1 -qnoansialias
-DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -I/usr/local/include -q64
-DUSE_64_BIT_ALL -q64 -qlonglong -D_LARGE_FILE=1 -D_LARGE_FILES=1 -O2
-DVERSION=\"1.09\" -DXS_VERSION=\"1.09\"
"-I/home/derjones/perl/local/lib/5.8.6/aix-thread-multi-64all/CORE" -DNDEBUG=1
-DSQLITE_PTR_SZ=8 -DHAVE_USLEEP=1 SQLite.c
....
Here's the perl -V information
ummary of my perl5 (revision 5 version 8 subversion 6) configuration:
Platform:
osname=aix, osvers=4.3.3.0, archname=aix-thread-multi-64all
uname='aix portia 3 4 00002f0e4c00 '
config_args=''
hint=recommended, useposix=true, d_sigaction=define
usethreads=define use5005threads=undef useithreads=define
usemultiplicity=define
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=define use64bitall=define uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='cc_r', ccflags ='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE
-qmaxmem=-1 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT
-I/usr/local/include -q64 -DUSE_64_BIT_ALL -q64',
optimize='-O',
cppflags='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE
-DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -I/usr/local/include'
ccversion='6.0.0.0', gccversion='', gccosandvers=''
intsize=4, longsize=8, ptrsize=8, doublesize=8, byteorder=87654321
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=8
ivtype='long long', ivsize=8, nvtype='double', nvsize=8,
Off_t='off_t', lseeksize=8
alignbytes=8, prototype=define
Linker and Libraries:
ld='ld', ldflags ='-brtl -L/usr/local/lib -q64 -b64'
libpth=/usr/local/lib /lib /usr/lib /usr/ccs/lib
libs=-lbind -lnsl -ldbm -ldl -lld -lm -lcrypt -lpthreads -lc_r -lbsd
perllibs=-lbind -lnsl -ldl -lld -lm -lcrypt -lpthreads -lc_r -lbsd
libc=/lib/libc.a, so=a, useshrplib=false, libperl=libperl.a
gnulibc_version=''
Dynamic Linking:
dlsrc=dl_aix.xs, dlext=so, d_dlsymun=undef, ccdlflags='
-bE:/home/derjones/perl/local/lib/5.8.6/aix-thread-multi-64all/CORE/perl.exp'
cccdlflags=' ', lddlflags='-b64 -bhalt:4 -bexpall -G -bnoentry
-lpthreads -lc_r -L/usr/local/lib'
Characteristics of this binary (from libperl):
Compile-time options: MULTIPLICITY USE_ITHREADS USE_64_BIT_INT
USE_64_BIT_ALL USE_LARGE_FILES PERL_IMPLICIT_CONTEXT
Built under aix
Help!
If I have not supplied enough information - please ask for more and I'll
see if I can obtain it - anything left out that might help is done so in
ignorance.
Yes, I *did* do $dbh->trace(15)'s and attempted driver traces as well,
nothing obvious turned up. The problem appears to be in the btree.c
code. I didn't include them here because there was nothing obvious that
they added as far as I could see. I will gladly do so if it seems useful
to anyone who can help.
I did see some stuff on the site regarding the page_size and the stack,
and then noted that something was said about this being replaced by
mallocs, but it still seems to be on the stack (don't want to chase
red-herrings but I am fishing here - if you'll pardon the mixed
metaphors ;-)
I need to get this working. An intermediate solution is for me to select
fewer rows with the 'like' clause and catenate them to a single o/p file
from the Perl script, but it's cumbersome. Not only that, the result
isn't actually going to a file, but being inserted into another table,
so it would mean writing out then re-reading again...
Kind regards
Derek Jones.
