On Fri, Jul 25, 2014 at 1:58 AM, Tom <t...@meanfox.com> wrote: > This seems like a really major difference to MySQL. >
SQLite solves a completely different problem from MySQL. The two are not comparable. SQLite is striving to be an Application File Format. MySQL wants to be an Enterprise Data Depot. It sounds like your problem is more in the Enterprise Data Depot domain, in which case you should look to MySQL (or better: PostgreSQL) for a solution. Further information: http://www.sqlite.org/appfileformat.html http://www.sqlite.org/whentouse.html > MySQL with InnoDB allows concurrent writes to different rows in the same > table. > SQLite doesn't even allow concurrent writes to the same DB. > So MySQL has write concurrency, SQLite does not. Guess that's what I'm > learning here. Maybe I should consider MySQL before considering NoSQL. > > > On Fri, Jul 25, 2014 at 10:49 AM, Tom Spencer-Smith <t...@meanfox.com> > wrote: > > > > > Thanks guys, I have already tried WAL and it does speed things up, but I > > still need the daily processing to be done asynchronously. The > computations > > are complex, involving around 16 inputs from the player table and > resulting > > in 8 outputs. This is core to the game and not a simple score > leaderboard. > > So my options appear to be: > > > > 1. Hope that someone addresses my other thread about async I/O not > working > > for me > > 2. Create my own mechanism for asynchronous DB transactions > > 3. Deal with writes that fail due to BUSY - just handle the critical > ones, > > drop the others > > 4. Experiment with using a separate DB for the leaderboard results, since > > a separate table won't work > > 5. Switch to NoSQL > > 6. Look at the feasibility of holding results in memory and incrementally > > injecting them into the player table > > 7. Some other ideas? > > > > Cheers > > > > Tom > > > > > > On Fri, Jul 25, 2014 at 7:13 AM, Simon Slavin-3 [via SQLite] < > > ml-node+s1065341n76956...@n5.nabble.com> wrote: > > > >> > >> On 24 Jul 2014, at 3:21pm, Tom <[hidden email] > >> <http://user/SendEmail.jtp?type=node&node=76956&i=0>> wrote: > >> > >> > My application presently does all the DB operations on the main > thread. > >> But > >> > once a day I need it do the player leaderboards, which could be 500K > >> players > >> > or so, hence several minutes. I can't have the main thread blocking > for > >> any > >> > significant length of time - a second or so at most. Hence my desire > to > >> do > >> > that one long operation in a separate thread and not have other DB > >> writes > >> > block. > >> > >> Even 500K players shouldn't be taking SQLite "a few minutes" unless > there > >> are no good indexes to use. Are the scores saved in your player table ? > >> Do you have indexes suitable for the commands you are executing ? > >> > >> > I don't suppose temporary tables would make any difference? I.e. use a > >> > temporary table for the LB results. Would that allow concurrent > writes? > >> (to > >> > temporary table, and to normal table, from different threads). > >> > >> It still won't do what you want. Dr Hipp's suggestion of WAL mode is > >> probably your best bed. With this you can have many readers and one > writer > >> all working at the same time. > >> > >> > What about using 2 databases? > >> > >> It would probably be better to try figuring out good SELECTs and indexes > >> first. > >> > >> Simon. > >> _______________________________________________ > >> sqlite-users mailing list > >> [hidden email] <http://user/SendEmail.jtp?type=node&node=76956&i=1> > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > >> > >> ------------------------------ > >> If you reply to this email, your message will be added to the > >> discussion below: > >> > >> > http://sqlite.1065341.n5.nabble.com/Does-SQLite-lock-the-entire-DB-for-writes-or-lock-by-table-tp76921p76956.html > >> To unsubscribe from Does SQLite lock the entire DB for writes, or lock > >> by table?, click here > >> < > http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=76921&code=dG9tQG1lYW5mb3guY29tfDc2OTIxfDg0MjQ3NTc4OQ== > > > >> . > >> NAML > >> < > http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml > > > >> > > > > > > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/Does-SQLite-lock-the-entire-DB-for-writes-or-lock-by-table-tp76921p76962.html > Sent from the SQLite mailing list archive at Nabble.com. > _______________________________________________ > 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
