Re: [sqlite] Fwd: sqlite3_busy_timeout() on NetBSD
I've not had any problems with sqlite3_busy_timeout on Linux. And I do not have NetBSD handy for testing. Not sure what the problem might be. Hello, Anything we can do to help? Give you access to a NetBSD machine, put in some debug somewhere? -- Tobias
[sqlite] Fwd: sqlite3_busy_timeout() on NetBSD
Hello List,
Got this email from this a developer of XMMS2 Sounds a bit scary,
anyone have seen this before?
-- Tobias
Begin forwarded message:
From: Alexander Botero-Lowry <[EMAIL PROTECTED]>
Date: måndag 3 jul 2006 16.13.29 GMT-04:00
To: [EMAIL PROTECTED]
Subject: Fw: sqlite3_busy_timeout() on NetBSD
Begin forwarded message:
Date: Sun, 2 Jul 2006 15:34:40 -0500
From: Alexander Botero-Lowry <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Subject: sqlite3_busy_timeout() on NetBSD
Hi,
I'm an xmms2 developer who does most of the work of getting xmms2
working on the various BSDs. I've currently run into a strange problem
with sqlite3_busy_timeout on NetBSD. It seems that it doesn't timeout
at all. Though sqlite3_busy_timeout() is set to 6, when:
sqlite3_exec (sql, "PRAGMA user_version",
xmms_sqlite_version_cb, &version, NULL)
is called, we immediately get back SQLITE_BUSY instead of having it
wait for the lock to end. I was able to very very hackishly work
around
this problem with:
if (sqlite3_exec (sql, "PRAGMA user_version",
xmms_sqlite_version_cb, &version, NULL)
== SQLITE_ BUSY) {
xmms_log_debug("busy...");
sleep(1);
sqlite3_exec(sql, "PRAGMA
user_version",
xmms_sqlite_version_cb, &version,
NULL);
}
Obviously this isn't a real solution, but it does solve the problem...
Is there something wrong with sqlite3_bus_timeout on NetBSD?
One can find the more complete source of out sqlite wrapper at:
http://git.xmms.se/?p=xmms2-
devel.git;a=blob;h=d0ee1489ad19aba2a4b72e569effd143417bcdda;hb=6732ab9
98047e684a99e558c99edb62466511df8;f=src/xmms/sqlite.c
Thanks in advance, Alex
Please CC, off list.
!DSPAM:44a97a9f190355315134984!
Re: [sqlite] Join on same table and performance.
I downloaded your database and the query above was indeed slow. But then I ran "ANALYZE" so that SQLite can gather statistics on the various indices, then reran the query. This time, SQLite was able to use the ANALYZE results to make better index choices and the query is quite speedy. The results of ANALYZE are stored in a special table named "sqlite_stat1". So you only have to run it once and the result will be used for all subsequent queries. Thank, you. This helps of course. How often should I run analyze? Trying to figure out how to make this programaticly for new installations. I guess there has to be a bit of data in the table before I can run analyze with the outcome that I want? -- Tobias
Re: [sqlite] Join on same table and performance.
El 24-03-2006, a las 16:08, [EMAIL PROTECTED] escribió: Elcin Recebli <[EMAIL PROTECTED]> wrote: Hi. You're joining the table with itself using 'id'. However, there's no index just on that field. I'm not sure how exactly SQLite utilises indices, but it might be unable to use index on (id,key,source) triple to optimise calculation of "m1.id = m2.id". SQLite is able to use the prefix of an index. So in this case, the index on (id,key,source) would be used to optimize m1.id=m2.id. Hello, Well it definitly did something. The query execution time was down by a factor of at least 100. Thanks Elcin. -- Tobias
[sqlite] Join on same table and performance.
Hello, I guess this subject is a bit worn out. But I am having scalabillity problems with SQLite in XMMS2. We have dimensioned XMMS2 to handle insanely huge media libraries, playlists and clients. Our goal is to be able to run medialibs with 50.000 files without problem. Our backend is SQLite3. When we get somewhere around 16 rows (10k entries) we're starting to see problems with scalabillity of SQLite3. I would like some views on how we could speed up the storage backend and also comments on our structure. Today we save all data in one table called Media. Each entry has a id number and each id number can have a indefinitive number of properties. To keep this flexible and clean we choose to add one row per property. A property can be "artist", "album", "url" and more. The schema is this: create table Media (id integer, key, value, source integer); and the indexes: create unique index key_idx on Media (id,key,source); create index prop_idx on Media (key,value); create index source_idx on Media (key,source); create index key_source_val_idx on Media (key,source,value); The most common query is something like this: select value from Media where id=5 and key='url' and source=1; This query remains very fast no matter how many entries I have in my database also things like: select key, value from Media where id=5 and source=1; is still very fast. But more advanced queries like "show me all albums and artists that are not compilations" are very slow: select distinct m1.value as artist, ifnull(m2.value,'[unknown]') as album from Media m1 left join Media m2 on m1.id = m2.id and m2.key='album' left join Media m3 on m1.id = m3.id and m3.key='compilation' where m1.key='artist' and m3.value is null; In fact, whenever I join with myself and try to extract a big number of values it can take forever to get the result. I have tried to increase the cache_size to somewhere around 32000 to see if it made any difference, it didn't. idxchk tells me that the good indexes are in use. Any comments, help or blame is welcome to try to solve this issue of scalabillity. You can download a medialib here: http://debian.as/~skid/medialib.db.gz this contains almost 20 rows and 14000 songs and is a real user library. Looking forward to getting your input. Thanks Tobias
Re: [sqlite] Vacuum slow
El 02-03-2006, a las 13:35, [EMAIL PROTECTED] escribió: The VACUUM command does something very much like this: sqlite3 olddb .dump | sqlite3 newdb; mv newdb olddb I say "much like" the above because there are some important differences. The VACUUM command transfers the data from the old and new database in binary without having to convert it into text. And the whole operation is protected by a rollback journal so that if a power failure occurs in the middle, the operation will rollback. But the point is this: VACUUM recreates the whole database from scratch. The time required is proportional to the amount of data that is in the database. I have noticed that when I import a lot of data (40-50k rows) into my application and run a select (fairly complex with joins) afterwards it will be a bit slow (fragmented indexes or something), but rerunning the select after a vacuum; will *GREATLY* speed up matters. This doesn't seem to happen if I set the autovacuum flag, vacuum still seems to do something different. Any ideas? -- Tobias
Re: [sqlite] Problems with threadsafe opt correction #2623
Kiel W. wrote: Ian Monroe wrote: I do not see how such a major change can be justified in a minor point release. For instance, currently amaroK does not work when using a sqlite database on Debian Sid since they package it with sqlite 3.2.5. Just my two cents, but if this change is breaking things in amaroK, XMMS2 and others then more testing should've been done on _those_ products before they were released to the wild. Regression/ unit tests should be run on most any change but definately when you upgrade a major sub-component of your system. Well in this case it broke all the currently installed applications that was installed and where MISSUSING the API. We have corrected this now but it will take a while before we can make a release out of it. In the meantime distributions that ship a newer sqlite3 will not be able to use amarok and xmms2. But it was our fault for not reading the docs correctly. -- Tobias
Re: [sqlite] Problems with threadsafe opt correction #2623
Ian Monroe wrote: I do not see how such a major change can be justified in a minor point release. For instance, currently amaroK does not work when using a sqlite database on Debian Sid since they package it with sqlite 3.2.5. Shouldn't this have waited for 4.0? I agree, XMMS2 also stoped working for sid and gentoo users and we got a shitload of bugreports. On the other hand I was forced to rewrite the horrible sqlite3 support. While on the subject I have a few questions. How "expensive" is it to open a new sqlite3 handler? My new method doesn't really cache the handlers, could this be a problem? My new internal api looks something like this: each part of the code that would like to change something in the db needs to aquire a session: session = xmms_medialib_begin(); this will open a sqlite3 handler and do some checking of the db. then make some changes: xmms_medialib_entry_property_set_int (session, entry, key, value); and then end it: xmms_medialib_end (session); and this will close it. At first I used a transaction that automaticly begun when you did medialib_begin(). But I got a lot of strange "Database is locked" errors. This seems to occur when this happens: thread1: begin thread2: begin thread1: insert () thread2: select () -> EBUSY thread1: commit -> EBUSY to solve this you have to rollback thread2 before you can commit thread1. It was a strange behaviour that didn't fit my code at all so I reverted to not using transactions, this works well but probably hurt my preformance. Is there a better way to handle this? -- Tobias
Re: [sqlite] Performances problem with multi-table query ?
Pierre D. wrote: Hi I'm currently developing a package manager (for linux) (yes I know, yet another, useless...) and I'm using XML files for the database. But the problem of that way is the slowdown and the memory cost of xml files + XPath query So I'm exploring other ways to store the database. The first other way I want to try is sqlite, because it has perfect bindings for python (my favorite scripting language) allowing the quick creation (less than one hour, including tests, RTFM...) of a convertion tool XML => sqlite After the convertion, I get a 8,6MB database, with a table files containing about 14 records, a packages table with about 440 records... The draft of the database is here (a picture showing the relations between tables) : http://pinaraf.robertlan.eu.org/schemadb.png (you'll notice some differences) I'm trying some "simple" query. The first query is "Whose file is it ?" Here is my first SQL query for that (ran with the sqlite3 command) : sqlite> SELECT p.name FROM packages p, files f WHERE f.pkgid=p.pkgid AND f.filename="/usr/bin/gcc"; gcc-core try a join instead. select p.name from packages p join files f on f.pkgid = p.pkgid where f.filename="/usr/bin/gcc"; -- Tobias
Re: [sqlite] Wierd problems with 3.2.1 under macosx 10.3.9
Thanks for your reply, D. Richard Hipp wrote: On Fri, 2005-04-22 at 09:10 +0200, Tobias RundstrÃm wrote: First of all performance is TERRIBLE, without syncronous=off I see insert times of up to 1 second for one row?! This is the F_FULLFSYNC issue. It's a sad hardware story and there is nothing that software can do to fix it. Google for the details. If you disable the F_FULLFSYNC, it will go much faster. Compiletime change or some pragma? Secondly after holding the DB open and not sending any querys to it for quite some time (3-4 hours) I often get a corrupted database (error from SQLite is "database file is damaged or encrypted"). What do you mean "holding the DB open"? If you you do not send it any queries, you can you get an error? Please explain the problem in more detail. Sorry I was unclear. I open the database, do some inserts(). idle for 3-4 hours without closing the handle and then do some selects it will give me that error. -- Tobias
[sqlite] Wierd problems with 3.2.1 under macosx 10.3.9
Hello, We (XMMS2 Team) have been using SQLite for quite some time now, it's used to cache metainformation about songs played. It can also be used to be searched and store addtional information. SQLite fits us very well and have been a good complement to xmms2. My primary platform is MacOSX and when we recently switched to sqlite 3.2.1 from sqlite 2.8.x I've started to note strange problems. First of all performance is TERRIBLE, without syncronous=off I see insert times of up to 1 second for one row?! Secondly after holding the DB open and not sending any querys to it for quite some time (3-4 hours) I often get a corrupted database (error from SQLite is "database file is damaged or encrypted"). The performance problems has also been spotted by another macosx user. My sqlite installation is vanilla complied from source with macosx shipped gcc. Anyone expericing the same? Greetings Tobias
