Re: [sqlite] 3.7.10 build failure on OS X 10.4: ld: Undefined symbols: _OSAtomicCompareAndSwapPtrBarrier
On Jan 21, 2012, at 06:21, Richard Hipp wrote: > On Sat, Jan 21, 2012 at 3:29 AM, Ryan Schmidt wrote: > >> Hello, I'm writing on behalf of the MacPorts package management system. >> Our users are having trouble compiling sqlite 3.7.10 on OS X 10.4, both on >> PowerPC and on Intel. 3.7.9 compiled fine. The error is: >> >> ld: Undefined symbols: >> _OSAtomicCompareAndSwapPtrBarrier >> >> The complete log is attached to our bug report: >> >> https://trac.macports.org/ticket/32930 >> >> Thanks for any help you can provide. >> > > I think that if you add this patch: > >http://www.sqlite.org/src/info/238e35a441 > > and if you compile with -DSQLITE_WITHOUT_ZONEMALLOC that it might work. > Please give it a try and let me know one way or another. Thank you, that worked for me. https://trac.macports.org/changeset/89249 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Some pieces of the puzzle are coming together
On 21 Jan 2012, at 11:20pm, Alek Paunov wrote: > 3.6.17: 14 seconds > 3.7.9: 10 seconds > > This clearly demonstrates that the newer version of Sqlite is, all things > being equal, superior in performance to the older. However, tests inside > our Delphi application demonstrate that reaching the exact same point of > the database result in the following times: > > Live Application > > 3.6.17: 16 seconds > 3.7.9: 58 seconds Which implies that the fault is in the Delphi bridge to SQLite and any pure examination of SQLite's behaviour isn't going to spot anything, right ? So a better place to query this would be a Delphi list ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Some pieces of the puzzle are coming together
On 21.01.2012 16:00, John Elrick wrote: manifestation. As frustrating as it has been to narrow down the cause, I Frustrating ... ? John Elrick, 2012-01-13: """ I created a logging system which took a specific set of data and converted all of the automatically run queries to an SQL script which I could use in a test application. When testing this particular script using a test program which uses our Delphi wrappers the following times are observed: Test Application Run Batch Script 3.6.17: 14 seconds 3.7.9: 10 seconds This clearly demonstrates that the newer version of Sqlite is, all things being equal, superior in performance to the older. However, tests inside our Delphi application demonstrate that reaching the exact same point of the database result in the following times: Live Application 3.6.17: 16 seconds 3.7.9: 58 seconds """ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic join reordering doesn't seem to work?
On 21/01/2012 2:44 PM, Simon Slavin wrote: On 21 Jan 2012, at 7:23pm, Ryan Johnson wrote: It's a SQL89 join. Sqlite docs say SQL89 and SQL92 joins perform identically [1], which I confirmed before sending the OP. Oh. Okay. If it spits out the same EXPLAIN QUERY PLAN then SQLite is interpreting it the same way. Which brings us back to the original question: why does sqlite spit out a bad query plan when a vastly better one exists? There's no clear reason the better answer should have been hard to find. Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic join reordering doesn't seem to work?
On 21 Jan 2012, at 7:23pm, Ryan Johnson wrote: > It's a SQL89 join. Sqlite docs say SQL89 and SQL92 joins perform identically > [1], which I confirmed before sending the OP. Oh. Okay. If it spits out the same EXPLAIN QUERY PLAN then SQLite is interpreting it the same way. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.10 build failure on OS X 10.4: ld: Undefined symbols:_OSAtomicCompareAndSwapPtrBarrier
Joe Mistachkin wrote: bool success; malloc_zone_t* newzone = malloc_create_zone(4096, 0); malloc_set_zone_name(newzone, "Sqlite_Heap"); do{ +#if MAC_OS_X_VERSION_MIN_REQUIRED >= MAC_OS_X_VERSION_10_5 success = OSAtomicCompareAndSwapPtrBarrier(NULL, newzone, (void * volatile *)&_sqliteZone_); +#else + success = OSAtomicCompareAndSwapPtr(NULL, newzone, + (void * volatile *)&_sqliteZone_); + OSMemoryBarrier(); +#endif }while(!_sqliteZone_); if( !success ){ /* somebody registered a zone first */ malloc_destroy_zone(newzone); } With this algorithm, where there are not multiple memory objects having some defined, coordinated meaning with respect to each other, the barrier not necessary. So this code could be simplified to simply use the non-barrier pointer swap, always. Cheers, -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Linking a "C" program with SQlite3.DLL
On 21/01/2012 1:46 PM, Bruce Steele wrote: I suspect this is a very basic question that is answered somewhere but I have done lots of searches and have been able to find a good answer. I am trying to compile a C program using Sqlite3 APIs. the program contains an include sqlite3.h line. I am using mingw (GCC) compiler on a Win7 PC. What I am looking for is the correct Command line including all the correct switches and in the correct order. I use cygwin on win7, but this should do (works for me): gcc your-prog.c -lsqlite3 Naturally, you probably want to add some flags like '-g -Wall' but that's orthogonal. I either get the WinMain16 linker error or file not found error with all the compile commands I have tried. That means you don't define a main() function in (any of) your source(s), which has nothing to do with sqlite. You might try a general programming help list if that's your "main" problem. Hint: posting small snippets of code that show the problem, and the resulting error messages, helps folks help you. Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic join reordering doesn't seem to work?
On 21/01/2012 1:01 PM, Simon Slavin wrote: On 21 Jan 2012, at 5:49pm, Ryan Johnson wrote: In one case the optimizer seems to make a different decision depending on which order I write the join in; in the other case, the join ordering chosen is bad and compounded by an expensive subquery not being materialized into a transient table as it should be. For the first issue, consider the following query: select count(*) from orders O, Customer C where C.custkey=O.custkey and C.name like '%115'; I see no JOIN. I see a WHERE. And it's not clear to me what you're trying to count: orders or customers. It's a SQL89 join. Sqlite docs say SQL89 and SQL92 joins perform identically [1], which I confirmed before sending the OP. The older syntax just requires less typing, that's all. The query counts orders made by qualifying customers. Ryan [1] http://www.sqlite.org/optoverview.html#joins ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Linking a "C" program with SQlite3.DLL
I suspect this is a very basic question that is answered somewhere but I have done lots of searches and have been able to find a good answer. I am trying to compile a C program using Sqlite3 APIs. the program contains an include sqlite3.h line. I am using mingw (GCC) compiler on a Win7 PC. What I am looking for is the correct Command line including all the correct switches and in the correct order. I have looked at Mingw’s web site, I have looked at the SQLite.org web site, I have even done several web searches. I either get the WinMain16 linker error or file not found error with all the compile commands I have tried. I am a new programmer and am at a complete dead end and would appreciate any help I can get. I could use either a location with a good explanation or the actual command. Thanks in advance for any help, Bruce ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic join reordering doesn't seem to work?
On 21 Jan 2012, at 5:49pm, Ryan Johnson wrote: > In one case the optimizer seems to make a different decision depending on > which order I write the join in; in the other case, the join ordering chosen > is bad and compounded by an expensive subquery not being materialized into a > transient table as it should be. > > For the first issue, consider the following query: > > select count(*) from orders O, Customer C where C.custkey=O.custkey and > C.name like '%115'; I see no JOIN. I see a WHERE. And it's not clear to me what you're trying to count: orders or customers. I don't know if SQLite can work it out so try one of the following: select count(*) from orders O JOIN Customer C ON C.custkey=O.custkey WHERE C.name like '%115'; select count(*) from Customer C JOIN orders O ON C.custkey=O.custkey WHERE C.name like '%115'; Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Automatic join reordering doesn't seem to work?
Hi all, I'm playing around with a small TPC-H dataset (scale factor 100) in sqlite-3.7.3, and have noticed that several of the optimizations described at http://www.sqlite.org/optoverview.html don't seem to take effect, even after running ANALYZE. In one case the optimizer seems to make a different decision depending on which order I write the join in; in the other case, the join ordering chosen is bad and compounded by an expensive subquery not being materialized into a transient table as it should be. For the first issue, consider the following query: select count(*) from orders O, Customer C where C.custkey=O.custkey and C.name like '%115'; Then .stats/explain reports 14 fullscan steps for the query plan: 0|0|TABLE orders AS O 1|1|TABLE Customer AS C USING PRIMARY KEY Putting Customer first in the FROM clause makes the query markedly faster and executes only 14999 fullscan steps. The query plan confirms the change: 0|0|TABLE Customer AS C 1|1|TABLE orders AS O WITH INDEX OrderCustomers Cardinalities of the tables are customer:15k and orders:150k, so I would expect any predicate on Customer to get the optimizer's attention. If the LIKE clause was just confusing the optimizer's cardinality estimates then I would have expected it to always choose the same query plan, but it doesn't. Note that the index referenced above corresponds to a foreign key in the schema CREATE INDEX OrderCustomers on Orders(custKey); A second problem lies with non-flattened nested queries: instead of materializing the result in a transient table, sqlite reruns the query for each tuple in the other relation, even though the query cannot possibly be correlated: select count(*) from (select julianday(O.orderdate) ordered, julianday(L.receiptdate) received from orders O, lineitem L where L.orderkey=O.orderkey and ordered >= julianday('1994-01-01') and received < julianday('1994-04-01') ) X, (select distinct(julianday(orderdate)) d from orders where d >= julianday('1994-01-01') and d < julianday('1994-04-01') ) Y where Y.d between X.ordered and X.received; The first subquery has cardinality 5918 and examines 150k rows, while the second has cardinality 90 and examines 150k rows; the overall query should therefore examine 150k+150k+90*5900 = ~830k rows. Instead, it takes 45s and 13650087 fullscan steps to run, or roughly 90*150k + 150k + 90 (the cost of evaluating Y, iterating over Y, and running X once per row in Y). Reordering the query doesn't help (X really should go first but the optimizer insists on Y). Disabling join optimization (x cross join y) cuts the query's cost to 1.6s and 827k rows. Is there something obvious I'm missing here? The second case, in particular, doesn't seem to depend on cardinalities: the non-correlated subquery should be materialized rather than re-running repeatedly (according to the docs, at least), at which point join ordering wouldn't matter nearly so much. Thanks, Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Some pieces of the puzzle are coming together
I'll send it in a bit. I actually created the complete script a week ago. Richard, I greatly appreciate your time and the attention you've given this matter. I have no doubt this is a very esoteric issue which we just happened to hit the exact combination of events to force its manifestation. As frustrating as it has been to narrow down the cause, I have nothing but respect and gratitude for everyone who has attempted to help. Thank you all. On Jan 20, 2012 3:47 PM, "Richard Hipp"wrote: > On Fri, Jan 20, 2012 at 3:40 PM, John Elrick >wrote: > > > I don't mean to blow anyone off, but there are over 100 prepared queries > > involved in a very interlaced manner. Getting the EXPLAIN data is very > > time consuming and since we've gone way past that point already by > > obtaining call stack information, I am not interested in revisiting the > > territory unless there is a very good reason for it. At least I know we > > can successfully upgrade to 3.7.5 without any performance issues. It > would > > be nice to understand what is going on from that version forward, > however, > > because it is important that we be able to continue upgrades in the > future. > > > > We'd like to understand what SQLite is doing differently to cause your > problem, too. As you have already observed, most applications don't have > this issue. I'm not sure what your application is doing to make SQLite go > crazy will mallocs, but we'd like to know so that we can avoid such > situations in the future. > > Can you try this: Can you use the sqlite3_trace() interface to create a > log of all SQL statements that are evaluated. Then send me (perhaps > privately) the starting database and your log, so that I can run SQLite > through exactly the same set of operations you are running it through? > > > > > > > > > > On Fri, Jan 20, 2012 at 2:55 PM, John Elrick > >wrote: > > > > > The problem is not in the queries. The problem is in a two order of > > > magnitude increase in _mallocs between the versions. The _mallocs are > > > coming from sqlite3Parser. > > > > > > > > > On Fri, Jan 20, 2012 at 2:37 PM, Max Vlasov > > wrote: > > > > > >> On Fri, Jan 20, 2012 at 10:05 PM, John Elrick < > john.elr...@fenestra.com > > >> >wrote: > > >> > > >> > The change which results in a slow down occurred between 3.7.5.0 and > > >> > 3.7.6.0. > > >> > > > >> > > > >> What about EXPLAIN difference? Or just outputs of this prefix from > both > > >> versions? > > >> > > >> Max > > >> ___ > > >> sqlite-users mailing list > > >> sqlite-users@sqlite.org > > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > >> > > > > > > > > > > > > -- > > > John Elrick > > > Fenestra Technologies > > > 540-868-1377 > > > > > > > > > > > > -- > > John Elrick > > Fenestra Technologies > > 540-868-1377 > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Another 3.7.10 question
Dear Simon and Dan, thanks for your valuable information, which we were looking for. Kind regards Alex __ Od: "Simon Slavin" Komu: General Discussion of SQLite Database Datum: 21.01.2012 12:49 Předmět: Re: [sqlite] Another 3.7.10 question On 21 Jan 2012, at 8:28am, Dan Kennedy wrote: You can get the old behavior by issuing a statement like: PRAGMA page_size = 1024; When creating the database. Alternatively, if that data is now fixed and you care about the filesize, do a VACUUM and see if that shrinks the database in a way you like. The actual underlying file format hasn't changed, just the way the functions fill the file. You can swap back and forth between 3.7.x versions and they'll still all be able to read and write the file. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.10 build failure on OS X 10.4: ld: Undefined symbols: _OSAtomicCompareAndSwapPtrBarrier
On Sat, Jan 21, 2012 at 3:29 AM, Ryan Schmidtwrote: > Hello, I'm writing on behalf of the MacPorts package management system. > Our users are having trouble compiling sqlite 3.7.10 on OS X 10.4, both on > PowerPC and on Intel. 3.7.9 compiled fine. The error is: > > ld: Undefined symbols: > _OSAtomicCompareAndSwapPtrBarrier > > The complete log is attached to our bug report: > > https://trac.macports.org/ticket/32930 > > Thanks for any help you can provide. > I think that if you add this patch: http://www.sqlite.org/src/info/238e35a441 and if you compile with -DSQLITE_WITHOUT_ZONEMALLOC that it might work. Please give it a try and let me know one way or another. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Another 3.7.10 question
On 21 Jan 2012, at 8:28am, Dan Kennedy wrote: > You can get the old behavior by issuing a statement like: > > PRAGMA page_size = 1024; > > When creating the database. Alternatively, if that data is now fixed and you care about the filesize, do a VACUUM and see if that shrinks the database in a way you like. The actual underlying file format hasn't changed, just the way the functions fill the file. You can swap back and forth between 3.7.x versions and they'll still all be able to read and write the file. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.10 build failure on OS X 10.4: ld: Undefined symbols:_OSAtomicCompareAndSwapPtrBarrier
DISCLAIMER: I am *NOT* an expert on Mac OS X programming. That being said, I think the following patch might work (this is UNTESTED, as I have no readily available Apple hardware): Index: src/mem1.c == --- src/mem1.c +++ src/mem1.c @@ -72,10 +72,11 @@ ** SQLITE_WITHOUT_ZONEMALLOC symbol is defined. */ #include #include #include +#include static malloc_zone_t* _sqliteZone_; #define SQLITE_MALLOC(x) malloc_zone_malloc(_sqliteZone_, (x)) #define SQLITE_FREE(x) malloc_zone_free(_sqliteZone_, (x)); #define SQLITE_REALLOC(x,y) malloc_zone_realloc(_sqliteZone_, (x), (y)) #define SQLITE_MALLOCSIZE(x) \ @@ -236,12 +237,18 @@ ** e.g. we have our own dedicated locks */ bool success; malloc_zone_t* newzone = malloc_create_zone(4096, 0); malloc_set_zone_name(newzone, "Sqlite_Heap"); do{ +#if MAC_OS_X_VERSION_MIN_REQUIRED >= MAC_OS_X_VERSION_10_5 success = OSAtomicCompareAndSwapPtrBarrier(NULL, newzone, (void * volatile *)&_sqliteZone_); +#else + success = OSAtomicCompareAndSwapPtr(NULL, newzone, + (void * volatile *)&_sqliteZone_); + OSMemoryBarrier(); +#endif }while(!_sqliteZone_); if( !success ){ /* somebody registered a zone first */ malloc_destroy_zone(newzone); } -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 3.7.10 build failure on OS X 10.4: ld: Undefined symbols: _OSAtomicCompareAndSwapPtrBarrier
Ryan Schmidt sqlite-2012a at ryandesign.com wrote: Hello, I'm writing on behalf of the MacPorts package management system. Our users are having trouble compiling sqlite 3.7.10 on OS X 10.4, both on PowerPC and on Intel. 3.7.9 compiled fine. The error is: ld: Undefined symbols: _OSAtomicCompareAndSwapPtrBarrier The complete log is attached to our bug report: https://trac.macports.org/ticket/32930 Thanks for any help you can provide. Here are a few observations and deductions that may help you narrow your search. OSAtomicCompareAndSwapPtrBarrier(...) is implemented in the so-called 'Standard C Library' on BSD, hence on modern Apple platforms. It is not declared or #defined anywhere in the published SQlite3 source, only used, under an #ifdef __APPLE__ protected section of code. This section did not appear in a form calling OSAtomicCompareAndSwapPtrBarrier in SQlite3 v3.7.9, but v3.7.10 has such a call. (You surely surmised this; I just mention it to support some reasoning below.) It appears to be declared once libkern/OSAtomic.h has been #included. Older versions of OSAtomic have varying names for very similar functions, such as OSCompareAndSwapPtr(...) with the same signature. Here is something that I proffer more tentatively: The actual declaration appears in a file listed at http://www.opensource.apple.com/source/CF/CF-635/CoreFoundation_Prefix.h which has a very strange feature. Instead of the usual bracketing reading #ifdef __cplusplus extern "C" { #endif the conditional reads #if DEPLOYMENT_TARGET_WINDOWS && defined(__cplusplus) . This suggests to me that the definition itself, (the one whose address must be found by ld to resolve references), may have a name that is decorated per one of the various C++ schemes, when built for a platform for which DEPLOYMENT_TARGET_WINDOWS does not get #define'd. This would cause the link failure you see. The SQLite code, compiled as C, references the barely decorated name, (with just the leading '_'), producing the reference that is not resolved, whereas the library, if compiled as C++, may have exposed its definition with the more ornate name decoration which encodes the signature. You could do a little work with nm and grep, looking for where 'OSAtomicCompareAndSwapPtrBarrier' is exposed, either as a definition or a reference. As soon as you find such, you will see whether it is C++-decorated or not. Of course, the problem may be as simple as not having enough libraries in your link step. That same "nm | grep" effort will tell you what additional library is needed, (if that will solve the problem). Given the evolving nature of the synchronization primitives on the platform, you may be suffering from linking against too-dated libraries. The "nm $obj | grep OSAtomicCompareAndSwapPtrBarrier" effort should also uncover that. (If you find the library with the non-Barrier forms of those primitives, or the older OSCompareAndSwapPtr, but missing OSAtomicCompareAndSwapPtrBarrier, that would be a strong clue that the missing entry point is truly missing among the set of libraries you had hoped to use.) Good luck, -- Larry Brasfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 3.7.10 build failure on OS X 10.4: ld: Undefined symbols: _OSAtomicCompareAndSwapPtrBarrier
Hello, I'm writing on behalf of the MacPorts package management system. Our users are having trouble compiling sqlite 3.7.10 on OS X 10.4, both on PowerPC and on Intel. 3.7.9 compiled fine. The error is: ld: Undefined symbols: _OSAtomicCompareAndSwapPtrBarrier The complete log is attached to our bug report: https://trac.macports.org/ticket/32930 Thanks for any help you can provide. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Another 3.7.10 question
On 01/21/2012 02:00 PM, Alexandr Němec wrote: Dear all, we have another question regarding the 3.7.10 version. We have a database with cca 10 tables. After creating a new database in 3.7.10 and filling each table with about 10 - 100 data rows, we noticed that the size of the database is 2x - 4x larger compared to 3.7.9 (using exact same table structure and data rows). We think that it has to do with the schema format number, which is set to 4 by default in 3.7.10. So the general question is, does it mean that the new format needs more disk space for the same amount of data? If you compile with SQLITE_POWERSAFE_OVERWRITE=0, then 3.7.10 uses 4096 byte pages by default. 3.7.9 used 1024 byte pages. So for small databases, 3.7.10 might produce bigger dbs by default. But the difference should get lost in the noise once you populate the db. You can get the old behavior by issuing a statement like: PRAGMA page_size = 1024; When creating the database. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users