Re: [sqlite] Doing math in sqlite
For the cost of a single table scan, you may be better with: select max(foo) - min(foo) where etc. S. -- Anyone using floating point for financial computations needs their head examined. On 12/20/18 4:32 PM, Jens Alfke wrote: On Dec 20, 2018, at 4:21 PM, Jungle Boogie wrote: select od_reading from mileage where car='foo' limit 1 select od_reading from mileage where car='bar' order by od_reading desc limit 1 Note: the first query should use “order by od_reading”, otherwise the order is undefined. A clearer way to specify these is select min(od_reading) from mileage where car=‘foo’ select max(od_reading) from mileage where car=‘foo’ How do I subtract the last query from the first query to determine how many miles were driven? It’s super simple: (select od_reading from mileage where car='foo' limit 1) - (select od_reading from mileage where car='bar' order by od_reading desc limit 1) —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Either bad constant or bad comment in os_unix.c
In the nolock IOMETHODS: https://github.com/mackyle/sqlite/blob/7bd4fc81a71bdc777151c747b2e6d3ee58994251/src/os_unix.c#L5203 Either the constant should be '1' or the comment should permit mmap. I suspect the constant should be 1? S. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] mmap, madvise, mlock and performance
On 08/03/2018 12:55 PM, Simon Slavin wrote: On 3 Aug 2018, at 8:36pm, Shevek wrote: We are running a 100Gb sqlite database, which we mmap entirely into RAM. We are having trouble with parts of the disk file being evicted from RAM during periods of low activity causing slow responses, particularly before 9am. Has anybody played with mlock and/or madvise within the sqlite mmap subsystem to improve this behaviour? Is this a genuine Linux machine running on physical hardware, or is it a virtual machine ? Yes, it's a genuine physical, we have Xeon and Epyc CPUs available. Sometimes we have to run in VMs (up to 50Gb), but the bigger stuff is all physical. We typically have 256Gb+ of RAM, so we aren't under particular pressure to mmap a 100Gb database. Are you intentionally doing anything that would contend for this memory ? In other words, when a memory-mapped portion gets swapped out, does it make sense what replaced it, or is it pointless and weird ? Sometimes, Linux just seems to get unfriendly with a set of pages and just maps them out. I've been watching it all weekend - right now the system I'm watching has 165Gb free, and I watched Linux just dump 40Gb out of RAM. :-( There are other jobs running on the system, and doing I/O, but nothing that should put any real memory pressure on the system, aside from disk I/O, backup, etc. We're about to try mlockall(MCL_FUTURE) along with MAP_SHARED. It might also be worth trying fadvise(), but I think kernel only honours a few megabytes based on that. We did think of a page-toucher thread but that risks thrashing as much as anything, but might be interesting for monitoring page faults performance. Later note: mlockall() failed because of JVM heap; we're going to have to do something much more specific, like holding a secondary map and mlocking that. Warren: Is the copy-everything-into-memory strategy not prohibitively expensive at the 100+Gb scale? Is it worth sinking the time into implementing that? Our rows are very small, only a few bytes each, so the per-row overhead may be significant. Also, it would be nice to have a shared mmap, rather than entirely private RAM, so we can run experiments over the shared (readonly) store. S. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] mmap, madvise, mlock and performance
Hi, We are running a 100Gb sqlite database, which we mmap entirely into RAM. We are having trouble with parts of the disk file being evicted from RAM during periods of low activity causing slow responses, particularly before 9am. Has anybody played with mlock and/or madvise within the sqlite mmap subsystem to improve this behaviour? The system has a few hundred gig of RAM, no swap, the database is read-only, and we would prefer a page-out to a process crash, so mlock might not be ideal, but madvise might not be strong enough? Thank you. S. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] random rows
You may find it faster to do: select c from t where rowid in (list-of-constants) and generate the list of constants using something like a blackrock permutation generator. That takes linear time, whereas all the order-by variants are n.log(n). You need some sort of row-id generator function, which you can do with windows/olap. Beware in Oracle, some of the rowid variant functions only increment if you return the row. sqlite seems to have a trick where copying the table creates sequential rowids, by spec, if you don't have one. S. On 05/31/2018 08:28 PM, Jay Kreibich wrote: I’m not entirely sure your solution will have an even distribution. It depends a lot on how many times random() is called (once per row vs once per sort operation), and how the sort algorithm works. I might do this instead: SELECT * FROM (SELECT random(), t.* FROM t) ORDER BY 1 LIMIT 200; As this makes sure random() is only called once per row. I’m pretty sure this is actually equivalent to yours, but it makes things a bit more explicit. If you only needed one, I’d do something like: SELECT * FROM table ORDER BY rowid LIMIT 1 OFFSET abs(random()) % (SELECT count(*) FROM table); Or even just call that multiple times if you need a moderate number. The ORDER BY should be free, but it could also be eliminated. -j On May 31, 2018, at 7:12 PM, Torsten Curdt wrote: I need to get some random rows from a large(ish) table. The following seems to be the most straight forward - but not the fastest. SELECT * FROM table ORDER BY random() limit 200 Is there a faster/better approach? cheers, Torsten ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"
SQL overall is phenomenally ambiguous about group-by: 1) select a0 + 1 as a0 from a group by a0 has a different behaviour in different SQL implementations, depending on whether the group-by prefers the underlying column or the projection alias. 2) As you rightly point out, group by integer has a different behaviour in different implementations, depending on how they range-check for "is a projection reference" - is a valid integer, is out of range, ... 3) group by expression has several of the above ambiguities, when it's legal. 4) Everything else is AFU'd too. S. On 05/31/2018 09:49 AM, Mark Brand wrote: At first I wasn't sure what you meant by "column identifier". It's what the documentation calls "column-alias". But this also qualifies as an "other expression" doesn't it? A column alias can appear pretty much anywhere any expression can, except in the same column list where it is defined. Mark On 31/05/18 18:27, Hick Gunter wrote: Ponder the following select fragment SELECT name, year * 100 + month as period, day + hour / 24.0 from some_table Then you can have ORDER BY 1,2,3 -- integer output column numbers which is equivalent to ORDER BY name, period, day + hour / 24.0 -- output column identifier x2 + "any other expression" -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Mark Brand Sent: Mittwoch, 30. Mai 2018 16:22 To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0" Thanks for the clarification. You have constant integers, output column identifiers and "any other expression" as terms for GROUP BY. Just to make sure I'm not missing something subtle: I understand the "constant integer" is what gets interpreted as a result column number. What is an "output column identifier" then? Isn't it already covered by the broader category "any other expression"? It's still a pretty astonishing language feature(!?) that an integer numeric-literal, which in every other column-like context represents its integer value, gets interpreted after GROUP BY or ORDER BY as a result column number. Fortunately, SQLite isn't to blame for designing this. By the way, this feature is documented for ORDER BY, but I don't see it for GROUP BY. Mark On 30/05/18 13:28, Hick Gunter wrote: You have constant integers, output column identifiers and "any other expression" as terms for GROUP BY. If the expression evalutes to a constant value, you will have only one output row. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Mark Brand Gesendet: Mittwoch, 30. Mai 2018 12:11 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0" Thanks. I had forgotten that GROUP BY considers a literal integer in this context to be a column number, a feature I don't use. These, on the other hand, work as I would have expected: sqlite> select 0 group by cast (0 as int); 0 sqlite> select 0 group by (select 0); 0 Mark On 30/05/18 12:00, Hick Gunter wrote: Yes. If the expression is a constant integer K, then it is considered an alias for the K-th column of the result set. Columns are ordered from left to right starting with 1. There is no 0-th column, so GROUP BY 0 is "out of range", just the same as "SELECT 0 GROUP BY 31" would be. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Mark Brand Gesendet: Mittwoch, 30. Mai 2018 11:32 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0" Hi, Is there a good reason for this error: sqlite> SELECT 0 GROUP BY 0; Error: 1st GROUP BY term out of range - should be between 1 and 1 sqlite> SELECT 0 GROUP BY 1; 0 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not
Re: [sqlite] [EXTERNAL] Window functions
About storing the whole result set: Note that in postgresql-derivatives (and Oracle? maybe Teradata?), this is valid: select lag(a0, a0) over () from a; whereas many other servers (which I won't name) require the second argument of lag() to be constant. If it is constant (even in postgresql-derivatives), a better window bound than the implicit "unbounded preceding" can be computed by the optimizer, and this may be essential for an efficient implementation of the most common case, because then the window buffer (per-group) can be guaranteed to fit into (pre-allocated) RAM. S. On 04/26/2018 07:44 AM, Hick Gunter wrote: "Window functions" aka "scrollable cursors" require that the whole result set ist stored somewhere. Or at least the current position of all of the tables/indices involved in producing it, provided that such positions are settable. You then need to keep track of how many records need to be produced to satisfy the window position. If you really need scrollable cursors, you are free to implement them, maybe by CREATE TEMP TABLE query_results AS SELECT... Followed by SELECT * FROM query_results WHERE rowid BETWEEN ?start AND ?end; And cleaning up with DROP TABLE query_results; Note that you will be producing the complete result set before you are able to return even only the first row. This defeats any gain from having the query return results in the desired order without requiring a sort, i.e. choosing the indices that makes the desired order coincide with the natural order. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Charles Leifer Gesendet: Mittwoch, 25. April 2018 21:05 An: General Discussion of SQLite DatabaseBetreff: [EXTERNAL] [sqlite] Window functions Hi, I'm sure this has been asked before, but are window functions on the roadmap? Is it the authors' experience that the implementation would significantly complicate sqlite? Just curious. Thanks so much for a fantastic library. Charlie ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT available in pre-release
Opinion: Of all the DBMS's UPSERT/MERGE semantics, postgresql's is the least useful because it's very limited: It can only do a check against a constraint, and the cost of evaluating that constraint has to be carried by all other statements which mutate the table. Oracle/Teradata MERGE is a far more useful semantics because it's defined more like a self-join, where the constraint is specified in the statement, not the DBMS. On 04/19/2018 11:29 AM, Richard Hipp wrote: The latest pre-release snapshot [1] contains support for UPSERT following the PostgreSQL syntax. The documentation is still pending. Nevertheless, early feedback is welcomed. You can respond either to this mailing list, or directly to me. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Are you getting spam when you post to sqlite-users ?
It has to be a subscriber. I just got spam in the form of an almost-immediate reply to my last message to the list, including message-ids. Web wouldn't bother including that metadata, and is unlikely to be quite so real-time. On 04/18/2018 11:35 PM, Simon Slavin wrote: On 18 Apr 2018, at 11:19pm, davewrote: Wouldn't it be as simple as subscribing to the mailing list and harvesting the emails directly from the inbound content? No need to subscribe. This mailing list is gated to web fora. Just scrape some web pages. I don't think there's a solution short of each one of us tuning our junkmail filters by telling them to reject all of those spam messages. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JDBC driver experience
1. Xerial 2. A year or so, relatively heavily. 3. * MAX_MMAP_SIZE is too small by a few hundred megabytes. It's safe to set to around a terabyte. * No way to effectively use multiple threads, even on a read-only mmap'd database. * Planner sometimes misses plans - does it need better STAT features enabled? 4. What immediately comes to mind: For writing: * JournalMode MEMORY * SynchronousMode OFF * A couple of hundred meg of page cache * Use quite big transactions, but it doesn't really matter where the boundaries are. You can do 50K+ writes per transaction. * Use Java-side locks. * Any data which doesn't need to be accessible to the relational engine should be serialized into a BLOB with Kryo and Snappy. Even simple text fields. For reading: * mmap EVERYTHING * JournalMode OFF * It's MUCH faster to join in Java than in sqlite. If you're loading data into memory, do an application-side hash-join. * Wrap all your queries in a performance rig and log slow queries. * Force the planner using the CROSS keyword for any query which is ever slow. * Sometimes the planner changes the query plan based on e.g. the size of an IN-list. * Use covering indexes, and use sqlite_analyzer to figure out which ones are winning a space-time trade-off. * The partial covering index optimizer doesn't always account for constants, and we lose a bunch of space because we have to include the constant values in the index. Overall: * Does anyone respond to issues? We actually use nondeterministic transaction boundaries for writing because it really doesn't matter where they are, it only matters that you have one. S. On 04/18/2018 08:34 PM, Richard Hipp wrote: Are you using SQLite with JDBC? If so, can you please answer a few questions below? You can post on this mailing list or send your reply directly to me. 1. Which JDBC are you using? 2. For how long have you been using it? 3. What issues you had with this driver? 4. What advise do you have for avoiding problems in this driver? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] High performance and concurrency
On 03/01/2018 01:24 AM, Hick Gunter wrote: Use 1 connection for each thread. Sharing a connections between threads may cause transactions to be larger than each thread thinks. Why would I have a transaction of non-zero size on a read-only connection? It looks from the source as if having bCoreMutex=true and bFullMutex=false will allow us the concurrency we need. I'm going to try again in a couple of days. Our journal_mode is OFF. We use HikariCP, so a connection is in use by one thread at a time with JMM-safe handoff, and they all share the mmap region. S. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Shevek Gesendet: Donnerstag, 01. März 2018 09:10 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>; Simon Slavin <slav...@bigfraud.org> Betreff: [EXTERNAL] Re: [sqlite] High performance and concurrency On 02/28/2018 11:45 PM, Simon Slavin wrote: On 1 Mar 2018, at 7:24am, Shevek <sql...@anarres.org> wrote: What I think is happening is that either a pthread mutex or a database lock is serializing the accesses, so each thread blocks the others. To be specific, I'm concerned about is the line sqlite3_mutex_enter(db->mutex) at the top of sqlite3_step(). Since my queries are spending all their time in sqliteVdbeExec(), which is reached through that path, I assume db->mutex is preventing concurrency. Our main hotspots in the query are sqliteVdbeExec() and updating the btree pointer to point to a new page (I forget the call name). We can't do much about the cost of execution; we've mmap'd everything to avoid the I/O, we're using covering indexes to help with locality, we've sorted our query keys to attempt to reduce index page seeks, and now we want to use concurrency and splitting the logic in our query to exploit memory bandwidth. Now I've traced this again, I'm looking warily at SQLITE_OPEN_NOMUTEX because we need thread-safety, as in, sqlite's internal data structures must be handled correctly in the presence of multiple threads or passing a connection between threads (safely in the JMM); we just don't need serialization of database reads and writes, because nothing we do has a serializable side-effect. Is SQLITE_OPEN_NOMUTEX the answer? What journal mode are you using ? I'm fairly sure journal mode is NONE for our readonly database. Anyway, readonly shouldn't write to a journal. We have confirmed that the md5sum of the database file is unchanged during and after the execution of our application. S. <https://sqlite.org/pragma.html#pragma_journal_mode> If it's not WAL, try WAL. If it's currently WAL, try DELETE. Once you've changed it see if this changes how your program behaves. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] High performance and concurrency
On 02/28/2018 11:45 PM, Simon Slavin wrote: On 1 Mar 2018, at 7:24am, Shevek <sql...@anarres.org> wrote: What I think is happening is that either a pthread mutex or a database lock is serializing the accesses, so each thread blocks the others. To be specific, I'm concerned about is the line sqlite3_mutex_enter(db->mutex) at the top of sqlite3_step(). Since my queries are spending all their time in sqliteVdbeExec(), which is reached through that path, I assume db->mutex is preventing concurrency. Our main hotspots in the query are sqliteVdbeExec() and updating the btree pointer to point to a new page (I forget the call name). We can't do much about the cost of execution; we've mmap'd everything to avoid the I/O, we're using covering indexes to help with locality, we've sorted our query keys to attempt to reduce index page seeks, and now we want to use concurrency and splitting the logic in our query to exploit memory bandwidth. Now I've traced this again, I'm looking warily at SQLITE_OPEN_NOMUTEX because we need thread-safety, as in, sqlite's internal data structures must be handled correctly in the presence of multiple threads or passing a connection between threads (safely in the JMM); we just don't need serialization of database reads and writes, because nothing we do has a serializable side-effect. Is SQLITE_OPEN_NOMUTEX the answer? What journal mode are you using ? I'm fairly sure journal mode is NONE for our readonly database. Anyway, readonly shouldn't write to a journal. We have confirmed that the md5sum of the database file is unchanged during and after the execution of our application. S. <https://sqlite.org/pragma.html#pragma_journal_mode> If it's not WAL, try WAL. If it's currently WAL, try DELETE. Once you've changed it see if this changes how your program behaves. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] High performance and concurrency
Hi, I would like to have truly concurrent access to an sqlite database, that is, the ability for multiple connections to read from the database simultaneously. I'm using Java with xerial's sqlite-jdbc, customized to let me mmap the entire database into RAM, and with additional debugging symbols for perf. The database is about 30Gb, fully read-only, and the connections are opened as such. What I think is happening is that either a pthread mutex or a database lock is serializing the accesses, so each thread blocks the others. Queries are taking a few seconds, even with covering indexes, and I have the RAM bandwidth available, so I'd really like to use it. Any pointers? Thank you. S. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Missing several important tricks in partial indexes
If I create a partial index: create table a (a0, a1) create index idx on a (a0) where a1 is null; Then we have several issues: 1) This should be a covering index for select a0, a1 from a where a1 is null; It isn't. It's a great index, but we still defer to the table to look up the (always null) values of a1. 2) Actually, it's slightly worse than that: select a0, null as a1 from a where a1 is null; will select the index, because the condition a1 matches, but it seems that it's STILL not considered a covering index, (guessing now) presumably because it's not SUFFICIENT that the condition on the query is a superset of the query on the index. Including a1 in the index makes it a covering index, despite the fact that a1 is always null. I care more about this than most algebraic manipulations because it's a whole extra order of I/O in a lookup, and it's turning a 1-second query into a 51-second query, which is now ineligible for use in a web page. Right now, it's more worthwhile to pay the space penalty for the additional constant value at the end of the index. 3) The third issue is almost more major than the preceding two, and has nothing to do with covering, but with partial index selection AT ALL: If we do a select "WHERE a1 IS ?" and pass a null for the bind-value of ? it will never select the partial index, even though it's eligible. S. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users