Re: [sqlite] sqlite-users Digest, Vol 126, Issue 13
Hi folks, I already posted this message but I believe there was some error or issue with nabble and I am not sure it was received, if you already got it, please sorry a lot for the noise. == Hi All, I am facing a quite interesting problem. I am trying to implement a new virtual table, everything seems alright, I receive SQLITE_OK code on the registration (sqlite3_create_module_v2) but as soon as I try to use the module that I just created I only get an error: "no such module: $NAME_MODULE" It seems to me that I followed the documentation quite closely, moreover, I am not doing weird cast or particular alchemy with the code, with the exception that it is rust code and not plain C. What I do is quite simple, I just call `sqlite3_create_module_v2` passing all the parameter. Then I try to create a new virtual table doing something like: `CREATE VIRTUAL TABLE foo USING REDISQL_TABLES_BRUTE_HASH();` where `REDISQL_TABLES_BRUTE_HASH` is the same name that I pass to `sqlite3_create_module_v2` and it just doesn't work. I really don't understand what I am doing wrong. Is there any step I am missing? For reference the code is here: https://gist.github.com/siscia/c6c8c6637f93e56a70a226e1ab643ab4 Cheers, Simone ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite between forks
Hi all, it is suggested in several place to don't share a connection between forks. However I fail to see how this can be a problem for purely in-memory database. If my understanding of fork and sqlite are correct I don't see issues in having a forked child reading a database connection opened before the fork itself. The address space of the two database are different and, at this point, we are already talking about two completely different database. Of course each write in the child (parent) won't be visible by the parent (child), but this is not my concern. Is my reasoning sound and correct? Different point if the database is backed by a regular file, however, in such case, couldn't I simply close the connection in the child and reopen it (maybe in read only mode)? Sorry for this question that may be trivial! Best Regards, Simone ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 111, Issue 2
Hi Dan, actually I was using the version 3.15.1 so at first I didn't investigate too carefully. However, the TCL script should be using the 3.15.2. To dispel every doubt I updated to the latest 3.17.0 and I am seeing performance way more reasonable, and the bottleneck that I am seeing are from redis and not SQLite now. It is worth to investigate further, I will keep the mail list updated and I will update the github issue. Thanks for now. Best Simone On 02/03/2017 13:00, sqlite-users-requ...@mailinglists.sqlite.org wrote: Message: 10 Date: Wed, 1 Mar 2017 23:08:06 +0700 From: Dan Kennedy <danielk1...@gmail.com> To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Performance difference in running SQLite embed or in a TCL script Message-ID: <f1aa3f53-c600-5fbd-3d59-101127b61...@gmail.com> Content-Type: text/plain; charset=utf-8; format=flowed On 03/01/2017 09:53 PM, Simone Mosciatti wrote: Hi all, tl;dr: What are the difference between running SQLite inside TCL and running it embed in a shared object module? Why I am seeing such big difference in performance? https://github.com/RedBeardLab/rediSQL/issues/13#issuecomment-283309641 I finally got some time to work again on my redis module rediSQL, which basically embed SQLite inside redis. I already asked in this same mail list help about performance, and it was showed to me that the poor performance of the modules are not because of SQLite and that I should look at something else. I have a couple of benchmark where I insert triple of integers inside a single table, using straight TCL my machine reach a stable 240k insert per second. Using the redis module and doing the same kind of operation I got way worse performance that degrade with time. The insert per second start at roughly 24k and then decrease down to 1k. What I did to investigate the different behaviour was to use perf on both the script TCL and the redis server running the module. Since the SQLite performances are order of magnitude better than the redis module performances I was expecting that something redis related was throttling down the insert per second. I was wrong. The perf of the TCL script show that most of the time is spent in libpthread, libc, libtcl and only after in libsqlite in the symbol sqlite3_randomness. The perf ot the redis module, instead show that most of the time is spent in |sqlite3PcacheTruncate | (52%) and then on |sqlite3PcacheClearWritable |(30%) I must be doing something totally wrong, but what? I forget exactly when it was introduced, but there was a bug causing those routines to use excessive CPU on systems with really big page caches. Fixed for 3.14.1. Are you using something earlier than 3.14.1? If so, it's worth trying a newer version. http://sqlite.org/releaselog/3_14_1.html Dan. You can find more details on the issues I opened here: https://github.com/RedBeardLab/rediSQL/issues/13#issuecomment-283309641 To sum up I am asking what are the difference in running redis from TCL and from running it embed in a shared object. Thanks for your time. Simone ___ 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] Performance difference in running SQLite embed or in a TCL script
Hi all, tl;dr: What are the difference between running SQLite inside TCL and running it embed in a shared object module? Why I am seeing such big difference in performance? https://github.com/RedBeardLab/rediSQL/issues/13#issuecomment-283309641 I finally got some time to work again on my redis module rediSQL, which basically embed SQLite inside redis. I already asked in this same mail list help about performance, and it was showed to me that the poor performance of the modules are not because of SQLite and that I should look at something else. I have a couple of benchmark where I insert triple of integers inside a single table, using straight TCL my machine reach a stable 240k insert per second. Using the redis module and doing the same kind of operation I got way worse performance that degrade with time. The insert per second start at roughly 24k and then decrease down to 1k. What I did to investigate the different behaviour was to use perf on both the script TCL and the redis server running the module. Since the SQLite performances are order of magnitude better than the redis module performances I was expecting that something redis related was throttling down the insert per second. I was wrong. The perf of the TCL script show that most of the time is spent in libpthread, libc, libtcl and only after in libsqlite in the symbol sqlite3_randomness. The perf ot the redis module, instead show that most of the time is spent in |sqlite3PcacheTruncate | (52%) and then on |sqlite3PcacheClearWritable |(30%) I must be doing something totally wrong, but what? You can find more details on the issues I opened here: https://github.com/RedBeardLab/rediSQL/issues/13#issuecomment-283309641 To sum up I am asking what are the difference in running redis from TCL and from running it embed in a shared object. Thanks for your time. Simone ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 109, Issue 7
Hi James, sorry I didn't see you email earlier... No idea why... You are completely right on your question. However to answer those question I would need to know how rediSQL would be used, which I can't. Right now I can only deference those choice to whoever is using it. /2) I need some way to indicate if an error occurs during a write />>/operation, which is a problem I am not able to solve in a reasonable />>/bound of complexity. /> I'm not sure I understand. SQLite will commit the transaction atomically. If the transaction fails for logical or physical reasons, the error is indicated at commit time. How is indicating that error to the user in any way difficult? Suppose I have a empty buffer. Now it comes a write statement, something like: `INSERT INTO ...` I push that statement in a buffer and I ack to the client that is request is been processed. Now it comes another write statement, `UPDATE ...` I push also that statement into the buffer and I ack the client. Now I decide to write all into disk, so I start a single transaction, run the insert, run the update, and then commit. Unfortunately either the INSERT or the UPDATE fails. The whole transaction is aborted and I should indicate something to the client. Clearly the problem is that I am doing a single transaction, but if that is necessary for performance... It is my problem a little clearer ? Best, Simone /I could store in some buffer some SQL statement and I could execute />>/all of them in one single transaction. /> That's a good idea, all things being equal. But are they equal? Your problem description is entirely technical. It doesn't address how transactions map onto the problem domain. Question you should ask yourself: 1. What is the unit of work? What combinations of operations constitute a logically consistent change to the database? 2. How many units of work are you prepared to lose in the event of system failure? That determines how often you commit. /2) I need some way to indicate if an error occurs during a write />>/operation, which is a problem I am not able to solve in a reasonable />>/bound of complexity. /> I'm not sure I understand. SQLite will commit the transaction atomically. If the transaction fails for logical or physical reasons, the error is indicated at commit time. How is indicating that error to the user in any way difficult? --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 109, Issue 7
Hi Simon, yes, of course I am doing the sequence prepare(), step()*, finalize() Starts from here: https://github.com/RedBeardLab/rediSQL/blob/master/rediSQL.c#L174 However, even if I wasn't do it, I should just see an increase in memory, not a so steep decrease in performance, correct? Thanks, Simone On 07/01/2017 13:00, sqlite-users-requ...@mailinglists.sqlite.org wrote: Message: 6 Date: Fri, 6 Jan 2017 15:35:02 + From: Simon Slavin<slav...@bigfraud.org> To: SQLite mailing list<sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] sqlite-users Digest, Vol 109, Issue 6 Message-ID:<3b949654-42c0-4253-8965-f0edf1a89...@bigfraud.org> Content-Type: text/plain; charset=us-ascii On 6 Jan 2017, at 3:20pm, Simone Mosciatti<sisciamir...@yahoo.com> wrote: 1. Retrieve the connection object inside Redis 2. Parse the SQL statement (sqlite3_prepare_v2) 3. Execute the parsed statement 4. Return an "OK" to the client Are you using the sequence sqlite3_prepare_v2(), sqlite3_step(), sqlite3_finalize() for each statement ? If not you are asking your program to do a lot of extra memory handling. Alternatively you could just use sqlite3_exec(), which does all three of the above things. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 109, Issue 6
Hi Dan,
I run your TCL script and I can confirm that I am seeing values
extremely different, in my machine, the same that I used for the
previous test, I got a rather stable 240k insert per second.
In my understanding my code is not doing anything different.
The only difference is the presence of another layer (Redis), however
this should not give us such decrease of performance (it is going more
than 100x slower), nor it should show such degradation function of the
row count.
What I am doing in the module is simply to:
1. Retrieve the connection object inside Redis
2. Parse the SQL statement (sqlite3_prepare_v2)
3. Execute the parsed statement
4. Return an "OK" to the client
Of course with all the error check in the middle.
I do not see why, it should go SO slower, I can understand a little
slower, but this is too much.
I don't think it is SQLite faults, however if you see something that I
am clearly doing wrong I do appreciate any help.
Best,
Simone
On 06/01/2017 13:00, sqlite-users-requ...@mailinglists.sqlite.org wrote:
Can you describe the performance test you are running?
If I create a table in an in-memory database with:
CREATE TABLE test (a INT, b INT, c INT);
Then run this:
INSERT INTO test VALUES(random(), random(), random())
10,000,000 times, I get a fairly stable 330,000 inserts per second or
so. Tcl code to do this below. What is your test doing differently?
Dan.
## START TCL CODE
package require sqlite3
sqlite3 db :memory:
db eval { CREATE TABLE test (a INT, b INT, c INT); }
proc insert_n_rows {n} {
for {set i 0} {$i<$n} {incr i} {
db eval { INSERT INTO test VALUES(random(), random(), random()) }
}
}
set nStep 10
for {set i 0} {$i < 100} {incr i} {
set us [lindex [time { insert_n_rows $nStep }] 0]
puts "[expr $i*$nStep] [format %.2f [expr (100.0 * $nStep) /
$us]]/sec"
}
## END TCL CODE
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance degradation of in-memory database with high row count and overall design discussion of my project
Hi all, I am writing a small Redis module that embeds SQLite [1] I am facing some performance issues and I wonder if the behaviour I am seeing is expected or if I am doing something completely wrong. I would provide some context on what I am doing, hoping that you may see some flaw in my reasoning and point me in a better direction, I am sorry for the long email though. The main problem I am trying to solve is that SQLite when writing on disk is slow; this completely expected, I am using a spinning disk and we need to hit the disk for every commit (at least with the default PRAGMA setting). However I am trying to figure out how to make it faster. The very first thing that comes to mind is to avoid doing a lot of transaction, I could store in some buffer some SQL statement and I could execute all of them in one single transaction. This is perfect from the performance point of view, but two problem arise: 1) I need to differentiate between SELECT operation and DELETE/INSERT/UPDATE operations, I could manage this part. 2) I need some way to indicate if an error occurs during a write operation, which is a problem I am not able to solve in a reasonable bound of complexity. The second option I am considering is to let Redis takes care of the persistence. Use SQLite as in-memory and periodically write a snapshot on the RDB file (the RDB file is a snapshot of the whole internal state of Redis). This approach would works fine because I would be fast enough to COMMIT every operation, so I could provide immediate feedback in the case of an error, but at the same time I could provide enough throughput and the data will be written on disk "reasonably" often. However I am finding some issues on the performance of the in memory database, and I see the insert performance decrease, quite fast, as more and more tuple are added. I run some test and I would like to understand if this behaviour is expected. I collect the result of my benchmark on a github issues [2] where you can see that the performance decrease in a more than linear way from roughly 24.000 inserts per second to stabilize at roughly 4.000 inserts per second. The actual number of inserts per second is pretty pointless, it depends on the hardware, on the resource of the machine, etc... what leaves me wonder is the fact that I was expecting a logarithmic decrease in performance and not a (at least) linear decrease. Can somebody confirm that this is normal? Given the background I provide, could you suggest a better way to achieve reasonable performance and reasonable data safeness? [1]: https://github.com/RedBeardLab/rediSQL [2]: https://github.com/RedBeardLab/rediSQL/issues/13#issuecomment-270449852 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
