Re: [sqlite] Do I need to migrate to MySQL?

Darren Duncan писал(а) в своём письме Mon, 06 Jun 2011 05:43:47 +0600: > What I'm saying is analogous to saying people should default to wearing > helmets > when riding bikes and only not wear helmets on bikes when they can justify it. > You'll still get where you're

Re: [sqlite] Do I need to migrate to MySQL?

On 6 Jun 2011, at 12:20am, Dagdamor wrote: > Darren Duncan писал(а) в своём письме Mon, 06 Jun > 2011 05:08:45 +0600: > >> MySQL should not be considered as the default choice of a non-lite SQL DBMS, >> for >> projects not currently using it, when you have a choice

Re: [sqlite] Do I need to migrate to MySQL?

Dagdamor wrote: > Darren Duncan писал(а) в своём письме Mon, 06 Jun > 2011 05:08:45 +0600: >> MySQL should not be considered as the default choice of a non-lite SQL >> DBMS, for projects not currently using it, when you have a choice between >> multiple SQL DBMSs;

Re: [sqlite] Do I need to migrate to MySQL?

Darren Duncan писал(а) в своём письме Mon, 06 Jun 2011 05:08:45 +0600: > MySQL should not be considered as the default choice of a non-lite SQL DBMS, > for > projects not currently using it, when you have a choice between multiple SQL > DBMSs; instead, the default

Re: [sqlite] Do I need to migrate to MySQL?

Joe D писал(а) в своём письме Mon, 06 Jun 2011 00:57:51 +0600: > Non-transactional is by definition not atomic. > > With the single exception of something that is strictly read-only, I > have never, ever, seen any database application that did not need > transactions. Ever. > >

Re: [sqlite] Do I need to migrate to MySQL?

Darren Duncan wrote: > MySQL should be avoided like the plague. I hereby retract my above-quoted statement as I realize that it is too severe a statement to be making. Instead I will say the following in its place: MySQL should not be considered as the default choice of a non-lite SQL DBMS,

Re: [sqlite] Do I need to migrate to MySQL?

On 2011-06-05 12:26, Dagdamor wrote: > If you need a non-transactional (atomic)... > If you need transactions (although in most of the web cases you don't need > them)... Non-transactional is by definition not atomic. With the single exception of something that is strictly read-only, I have

Re: [sqlite] Do I need to migrate to MySQL?

Matthew L. Creech писал(а) в своём письме Sat, 04 Jun 2011 02:26:09 +0600: > Coincidentally, I happened to be reading over this page just earlier today: > > http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 > > Obviously a bit biased toward PostgreSQL

Re: [sqlite] Do I need to migrate to MySQL?

Mr. Puneet Kishor wrote: > On Jun 3, 2011, at 6:16 PM, Darren Duncan wrote: >> a. MySQL silently ignores all CHECK constraints in all engines, so for >> example you can't even tell it you want a column to only hold values >> between 1 and 10. Its in the MySQL docs: "The CHECK clause is parsed

Re: [sqlite] Do I need to migrate to MySQL?

Tom, thank you so much for the extensive advice. Ian On 04/06/2011 15:27, BareFeetWare wrote: > On 03/06/2011, at 11:40 PM, Ian Hardingham wrote: > >> Hey guys, thank you very much for the help so far. >> >> The list of calls which I make during the "end match section", which can >> take 2

Re: [sqlite] Do I need to migrate to MySQL?

On Jun 3, 2011, at 6:16 PM, Darren Duncan wrote: > Mr. Puneet Kishor wrote: >> On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote: >>> MySQL should be avoided like the plague. >> why? >> This is a long standing (un)conventional wisdom to which I too have hewed. >> Now, it so happens, I will be

Re: [sqlite] Do I need to migrate to MySQL?

On 03/06/2011, at 11:40 PM, Ian Hardingham wrote: > Hey guys, thank you very much for the help so far. > > The list of calls which I make during the "end match section", which can > take 2 seconds, is: > > SELECT * FROM multiturnTable WHERE id=? LIMIT 1 > > UPDATE multiturnTable SET

Re: [sqlite] Do I need to migrate to MySQL?

Thank you Igor, I'll do some more thorough profiling. When I run the query: UPDATE multiturnTable SET complete=1 WHERE id=-5 This takes ~45ms (as reported by SQLite's profile) - is this in the right ballpark? I'm running a fairly fast modern intel chip here. Thanks, Ian

Re: [sqlite] Do I need to migrate to MySQL?

On 04-06-2011 01:16, Darren Duncan wrote: > b. That's just an example of how MySQL silently ignores lots of errors or > silently changes data on you, such as silently truncating text values that > are > too long for a field when you insert them, so you've lost data without even > knowing it.

Re: [sqlite] Do I need to migrate to MySQL?

Mr. Puneet Kishor wrote: > On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote: >> MySQL should be avoided like the plague. > > why? > > This is a long standing (un)conventional wisdom to which I too have hewed. > Now, it so happens, I will be starting work on a project that uses MySQL >

Re: [sqlite] Do I need to migrate to MySQL?

On Fri, Jun 3, 2011 at 2:22 PM, Simon Slavin wrote: > > On 3 Jun 2011, at 7:19pm, Darren Duncan wrote: > >> MySQL should be avoided like the plague. > > Why ? > Coincidentally, I happened to be reading over this page just earlier today:

Re: [sqlite] Do I need to migrate to MySQL?

We use MySQL on a fairly high end PC server running Linux. The database is about 300-400 gigs. Front end of PHP and apache. It's been reliable for a couple years now and seems to have a bunch of headroom. I wrote a wrapper so my SQLite code (with minor modification) can feed MySQL instead of

Re: [sqlite] Do I need to migrate to MySQL?

On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote: > MySQL should be avoided like the plague. why? This is a long standing (un)conventional wisdom to which I too have hewed. Now, it so happens, I will be starting work on a project that uses MySQL exclusively, and has done so for years. They

Re: [sqlite] Do I need to migrate to MySQL?

On 3 Jun 2011, at 7:19pm, Darren Duncan wrote: > MySQL should be avoided like the plague. Why ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

MySQL should be avoided like the plague. Use Postgres instead if you have to switch to a larger SQL DBMS. But hopefully the help you've gotten so far will extend your mileage with SQLite and you won't have to switch to anything yet. -- Darren Duncan Ian Hardingham wrote: > Guys, the server

Re: [sqlite] Do I need to migrate to MySQL?

Thanks Nico, and everyone else. A 3x speed bump is still good, so I've set journal_mode to WAL. The docs seem to suggest I only need to do this once, but I presume that doing it once every time I open the database is acceptable? Thanks, Ian On 03/06/2011 15:42, Nico Williams wrote: > > On

Re: [sqlite] Do I need to migrate to MySQL?

On 3 Jun 2011, at 3:31pm, Ian Hardingham wrote: > Should really every single INSERT/UPDATE section have a begin/end > transaction around it? That would be the slowest way to do it. It would make each section an independent transaction, and at the end of the transaction all your changes must

Re: [sqlite] Do I need to migrate to MySQL?

On Jun 3, 2011 10:31 AM, "Ian Hardingham" wrote: > > Hey guys, once again thanks for the help. > > Should really every single INSERT/UPDATE section have a begin/end > transaction around it? There's an implied begin/commit if you don't put them there. It's when you can batch

Re: [sqlite] Do I need to migrate to MySQL?

Indexes slow down writes somewhat, true, but it sounds like the OP's issue is with commit latency, the average minimum bound for which is given by the storage hardware's capabilities. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Do I need to migrate to MySQL?

On Jun 3, 2011 10:04 AM, "Ian Hardingham" wrote: > > Thank you Igor, I'll do some more thorough profiling. > > When I run the query: > > UPDATE multiturnTable SET complete=1 WHERE id=-5 > > This takes ~45ms (as reported by SQLite's profile) - is this in the > right ballpark?

Re: [sqlite] Do I need to migrate to MySQL?

[sqlite-users-boun...@sqlite.org] on behalf of Ian Hardingham [i...@omroth.com] Sent: Friday, June 03, 2011 9:31 AM Cc: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Do I need to migrate to MySQL? Hey guys, once again thanks for the help. Should really every single INSERT/UPDATE

Re: [sqlite] Do I need to migrate to MySQL?

Database Subject: EXT :Re: [sqlite] Do I need to migrate to MySQL? Hey guys, once again thanks for the help. Should really every single INSERT/UPDATE section have a begin/end transaction around it? I have posted this code before, so apologies for doing it again - here is how my scripting language

Re: [sqlite] Do I need to migrate to MySQL?

Hey guys, once again thanks for the help. Should really every single INSERT/UPDATE section have a begin/end transaction around it? I have posted this code before, so apologies for doing it again - here is how my scripting language calls a query: int SQLiteObject::ExecuteSQL(const char* sql,

Re: [sqlite] Do I need to migrate to MySQL?

...@omroth.com] Sent: Friday, June 03, 2011 8:40 AM Cc: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Do I need to migrate to MySQL? Hey guys, thank you very much for the help so far. The list of calls which I make during the "end match section", which can take 2 seconds, i

Re: [sqlite] Do I need to migrate to MySQL?

I think it's very slow. Update of non-indexed fiels may be faster. Do you create a new db connection for each update?.. Or may be you have a lot of unused indicies? 2011/6/3 Ian Hardingham : > Thank you Igor, I'll do some more thorough profiling. > > When I run the query: > >

Re: [sqlite] Do I need to migrate to MySQL?

-users-boun...@sqlite.org] on behalf of Ian Hardingham [i...@omroth.com] Sent: Friday, June 03, 2011 8:40 AM Cc: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Do I need to migrate to MySQL? Hey guys, thank you very much for the help so far. The list of calls which I make during

Re: [sqlite] Do I need to migrate to MySQL?

Thank you Igor, I'll do some more thorough profiling. When I run the query: UPDATE multiturnTable SET complete=1 WHERE id=-5 This takes ~45ms (as reported by SQLite's profile) - is this in the right ballpark? I'm running a fairly fast modern intel chip here. Thanks, Ian

Re: [sqlite] Do I need to migrate to MySQL?

Ian Hardingham wrote: > Hey guys, thank you very much for the help so far. > > The list of calls which I make during the "end match section", which can > take 2 seconds, is: The queries you show, on the amount of data you claim, can't possibly take 2 seconds. They should run

Re: [sqlite] Do I need to migrate to MySQL?

Hey guys, thank you very much for the help so far. The list of calls which I make during the "end match section", which can take 2 seconds, is: SELECT * FROM multiturnTable WHERE id=? LIMIT 1 UPDATE multiturnTable SET p1SubmitScore=1 WHERE id=? UPDATE multiturnTable SET complete=1, score=?

Re: [sqlite] Do I need to migrate to MySQL?

On 03/06/2011, at 9:47 PM, Ian Hardingham wrote: > What is basically happening is that we're getting a fairly large number > of requests every second. There is one specific activity which takes > about 2 seconds to resolve, which is finishing a match. This requires > an update to three

Re: [sqlite] Do I need to migrate to MySQL?

On Fri, Jun 3, 2011 at 6:58 AM, Ian Hardingham wrote: > > I may be looking at a complete re-write. I may also need to have a > solution which scales beyond one machine. Can anyone give me advice on > this matter specifically? > > See http://www.sqlite.org/whentouse.html for

Re: [sqlite] Do I need to migrate to MySQL?

On 3 Jun 2011, at 12:47pm, Ian Hardingham wrote: > Common database operations: > > - select all of my non-completed matches > - select all of my matches > - select an old match to watch > - get my current friends If any of these operations are slow, make sure you have indexes suited to your

Re: [sqlite] Do I need to migrate to MySQL?

On Fri, Jun 3, 2011 at 7:47 AM, Ian Hardingham wrote: > Thanks Eduardo, I will go into more detail. > > The core of the server is the match list. It is a table with currently > about 200,000 rows in it. > > Two players will start a match, and a new entry is placed in the >

Re: [sqlite] Do I need to migrate to MySQL?

Thanks Eduardo, I will go into more detail. The core of the server is the match list. It is a table with currently about 200,000 rows in it. Two players will start a match, and a new entry is placed in the matchTable. A typical match will last 8 turns - as each player finishes a turn, the

Re: [sqlite] Do I need to migrate to MySQL?

At 12:58 03/06/2011, you wrote: >Guys, the server for this game - > >http://www.frozensynapse.com > >uses SQLite. We've had an unexpectedly successful launch which has >resulted in the server being swamped with players, and I'm trying to >optimise everywhere I can. I've always been under the

Re: [sqlite] Do I need to migrate to MySQL?

I start tclsqlite in 16 threads on 2-core Intel Xeon servers and these work fine. 2011/6/3 Ian Hardingham : > Guys, the server for this game - > > http://www.frozensynapse.com > > uses SQLite.  We've had an unexpectedly successful launch which has > resulted in the server being

[sqlite] Do I need to migrate to MySQL?

Guys, the server for this game - http://www.frozensynapse.com uses SQLite. We've had an unexpectedly successful launch which has resulted in the server being swamped with players, and I'm trying to optimise everywhere I can. I've always been under the impression that SQLite is pefectly