[sqlite] Segmentation fault on database with 2.2 Gb

[Hubertus]

Hi all,
very strange. Today I tried to open a database I created about half a year ago
(version 3.?) and I get a segmentation fault. I run a current debian unstable
with sqlite3.5.7 and have absolutely no clue what the problem might be! It works
with other databases and it worked with that one as well... I would be very 
glad for some help. At the bottom you find the strace output.

Thanks a lot

Hubertus

execve("/usr/bin/sqlite3", ["sqlite3", "data.db", "1"], [/* 34 vars */]) = 0
brk(0)  = 0x805
access("/etc/ld.so.nohwcap", F_OK)  = -1 ENOENT (No such file or directory)
mmap2(NULL, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 
0xb7fdf000
access("/etc/ld.so.preload", R_OK)  = -1 ENOENT (No such file or directory)
open("/usr/lib/tls/i686/sse2/cmov/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No 
such file or directory)
stat64("/usr/lib/tls/i686/sse2/cmov", 0xbf80e510) = -1 ENOENT (No such file or 
directory)
open("/usr/lib/tls/i686/sse2/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such 
file or directory)
stat64("/usr/lib/tls/i686/sse2", 0xbf80e510) = -1 ENOENT (No such file or 
directory)
open("/usr/lib/tls/i686/cmov/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such 
file or directory)
stat64("/usr/lib/tls/i686/cmov", 0xbf80e510) = -1 ENOENT (No such file or 
directory)
open("/usr/lib/tls/i686/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such file 
or directory)
stat64("/usr/lib/tls/i686", 0xbf80e510) = -1 ENOENT (No such file or directory)
open("/usr/lib/tls/sse2/cmov/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such 
file or directory)
stat64("/usr/lib/tls/sse2/cmov", 0xbf80e510) = -1 ENOENT (No such file or 
directory)
open("/usr/lib/tls/sse2/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such file 
or directory)
stat64("/usr/lib/tls/sse2", 0xbf80e510) = -1 ENOENT (No such file or directory)
open("/usr/lib/tls/cmov/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such file 
or directory)
stat64("/usr/lib/tls/cmov", 0xbf80e510) = -1 ENOENT (No such file or directory)
open("/usr/lib/tls/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such file or 
directory)
stat64("/usr/lib/tls", 0xbf80e510)  = -1 ENOENT (No such file or directory)
open("/usr/lib/i686/sse2/cmov/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such 
file or directory)
stat64("/usr/lib/i686/sse2/cmov", 0xbf80e510) = -1 ENOENT (No such file or 
directory)
open("/usr/lib/i686/sse2/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such file 
or directory)
stat64("/usr/lib/i686/sse2", 0xbf80e510) = -1 ENOENT (No such file or directory)
open("/usr/lib/i686/cmov/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such file 
or directory)
stat64("/usr/lib/i686/cmov", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
open("/usr/lib/i686/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such file or 
directory)
stat64("/usr/lib/i686", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
open("/usr/lib/sse2/cmov/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such file 
or directory)
stat64("/usr/lib/sse2/cmov", 0xbf80e510) = -1 ENOENT (No such file or directory)
open("/usr/lib/sse2/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such file or 
directory)
stat64("/usr/lib/sse2", 0xbf80e510) = -1 ENOENT (No such file or directory)
open("/usr/lib/cmov/libsqlite3.so.0", O_RDONLY) = -1 ENOENT (No such file or 
directory)
stat64("/usr/lib/cmov", 0xbf80e510) = -1 ENOENT (No such file or directory)
open("/usr/lib/libsqlite3.so.0", O_RDONLY) = 3
read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0p>\0\000"..., 512) = 512
fstat64(3, {st_mode=S_IFREG|0644, st_size=356132, ...}) = 0
mmap2(NULL, 355428, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 
0xb7f88000
mmap2(0xb7fde000, 4096, PROT_READ|PROT_WRITE, 
MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x56) = 0xb7fde000
close(3)= 0
open("/usr/lib/i686/cmov/libreadline.so.5", O_RDONLY) = -1 ENOENT (No such file 
or directory)
open("/usr/lib/i686/libreadline.so.5", O_RDONLY) = -1 ENOENT (No such file or 
directory)
open("/usr/lib/libreadline.so.5", O_RDONLY) = -1 ENOENT (No such file or 
directory)
open("/etc/ld.so.cache", O_RDONLY)  = 3
fstat64(3, {st_mode=S_IFREG|0644, st_size=79936, ...}) = 0
mmap2(NULL, 79936, PROT_READ, MAP_PRIVATE, 3, 0) = 0xb7f74000
close(3)= 0
access("/etc/ld.so.nohwcap", F_OK)  = -1 ENOENT (No such file or directory)
open("/lib/libreadline.so.5", O_RDONLY) = 3
read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\260\316"..., 512) = 512
fstat64

Re: [sqlite] Strange performance behavior

[Hubertus]

Dear John,
> You might also look at using the Sqlite date format rather than seperate 
> columns for year, month etc.
That was what I considered first. The problem was, that other people
are also supposed to use this datbase. Some of them use Fortran and they
said that it's easier to compile the data in this format but in the
prefered current_date. I couldn't find a way to do the splitting in day,
month, year with sqlite, so I choose this, admittedly not very nice,
implementation. I think even Fortran should know something like subset or split,
but...
Can this splitting be done by sqlite?

Thank

Hubertus

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Strange performance behavior

[Hubertus]

Well now, if this isn't something!
fist I wann thank you for your quick reply. Finally I have to wait for my result
satisfying 0.6 sec. This is great. Now I can add lots more data.
What I did:
- I added a second index for the column "campId" and did the analyze trick
  (I had this column in the index before, but that time it was quicker without 
  that column).
  This already improved the waiting time from the former best 3 sec to 1 sec
- I increased the page_size to 4096, this decresed the size of the
  database from 650 Mb to 450.
- And at last I replaced the -.99 values with NULL (don't think about it. I 
  was asked to do this as missing value, now I found the .nullvalue...)
  This again decreased the size to stunning 165 Mb!! and improved the
  query time to even better 0.6 sec.

To Dennis:
I'm afraid I haven't quite understood the quote thing. 
First how can I do a query like
select "42" from data where campId='stream94' and "14">-;
from my shell?
Secondondly usually I use python or R to access the data where I do somthing 
like
INSERT = 'SELECT "%i" FROM data where campId="%s"' % col, campId

query <- paste('SELECT "34" AS "N2O_TDL", "29" AS "O3"',
  'FROM data where campId="polstar97"')
rs <- dbSendQuery(con, statement = query)

How is this done correctly?

Thanks a lot

Hubertus

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Strange performance behavior

[Hubertus]

Dear list,
sorry to just come up with another performance question. I build a yet small 
database with one table. It has about 650.000 rows, 75 columns and
has at the moment about 650 Mb. It runs on a Intel Pentium M with 2 GHz. The 
Laptop runs Suse 10.2 and does basicly nothing but this database.
sqlite3 -version is 3.3.8

This is the schema:
CREATE TABLE 'data'('nr' INTEGER PRIMARY KEY,
  'year' INTEGER,
  'month' INTEGER,
  'day' INTEGER,
  'sec' REAL,
  'campId' TEXT,
  'flightNr' INTEGER,
  '1' REAL,
  ...
  '71' REAL
  );
CREATE INDEX sec on data(year,month,day,sec);
  
I experience a big variability of time a query needs:
~database> time sqlite3  data.db 'select "14" from data where 
campId="polstar98" and "14">-;' >/dev/null
real0m3.115s
user0m1.748s
sys 0m1.368s
~/database> time sqlite3  data.db 'select "14" from data where 
campId="polstar98" and "14">-;' >/dev/null
real0m3.139s
user0m1.756s
sys 0m1.380s
~/database> time sqlite3  data.db 'select "42" from data where 
campId="stream94" and "14">-;' >/dev/null
real0m50.227s
user0m4.692s
sys 0m3.028s

I've tried it with and without index and at different times. Most of the
time such simple queries take about 35 sec. Why is that so and what can be done?
I can live with 3 sec of response but not up to one minute and the
database is still not complete. Would pytables with hdf5 be an alternative? 
Knowing that this is probably not the right place to ask...

Tips, suggestions, recommendation are gratefuly appreciated! 
Thanks in advance

Hubertus

-
To unsubscribe, send email to [EMAIL PROTECTED]
-