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!
[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] 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
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] 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] Performances problem with multi-table query ?
[EMAIL PROTECTED] wrote: Le Samedi 23 Avril 2005 14:34, Tobias Rundström a écrit : select p.name from packages p join files f on f.pkgid = p.pkgid where f.filename=/usr/bin/gcc; It is as slow as the previous query :( Poor indexes? make sure that you have a index on pkgid in both tables and one filename. -- 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
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 Rundstrm 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
