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

[Dagdamor]

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 going either way, but one way is the typically
> safer one.

Actually, what you're saying is: "you should wear red helmets, not the green 
ones", and only because you heard somewhere that red helmets are "better". 
Using Postgre is not much safer than using MySQL; both are programs with 
inevitable bugs and imperfections in the code.

You don't even understand that each tool is for its job; running small and 
medium web application (we aren't talking about monsters like 
Google/Facebook/YouTube/Wikipedia here, although all of them also use MySQL 
:)), especially where transactions aren't needed and SELECT speed is a primary 
goal, is a very common task. You just say: for each new project, Postgre 
"should" be used. No, it shouldn't. It depends on what you are planning to do, 
in the first place.

> The Postgres makers take quality and reliability as top concerns, and have 
> for a
> long time, so to make the product much more solid.  They have high standards 
> for
> declaring the DBMS production ready and lengthy testing/shakeout periods.

Any advertizing of any product says the same. You really believe MySQL team has 
any different approach, that their standards are lower? Up to the recent days, 
Postgre was sadly known as "one of the slowest DBMSes ever". Is this a "high 
standard"? What's the point in "quality and reliability" if you simply can't 
get enough number of queries in a second, if Postgre cannot handle your 
server's load?

By the way, does Postgre support clustering/replication natively? MySQL does.

> I don't believe that MySQL development has anywhere near this kind of rigor.

What makes you say that? I submitted few bug reports/feature requests in the 
past, some of them were approved, some argued, but in all cases, I've got fast 
and professional reply from the team. Just like here :) For a free product, 
this is an excellent approach.

Regards,
Serge
___
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?

[Simon Slavin]

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 between multiple SQL
>> DBMSs; instead, the default non-lite choice should be Postgres.
> 
> Wow, communistic regime is back! Thanks for telling me what I should do and 
> what not, what I should use and what not. ;)

Dude, it's just advice.  That's all any of us do: post our opinions.  Calm down.

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?

[Darren Duncan]

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; instead, the default non-lite choice should be
>> Postgres.
> 
> Wow, communistic regime is back! Thanks for telling me what I should do and
> what not, what I should use and what not. ;)

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 going either way, but one way is the typically
safer one.

> Mind you, once Postgre will become more or less known world-wide (its usage
> is not comparable with either MySQL or SQLite which are way more popular),
> you will have exactly the same issues: lots of bugs open, lots of features
> missing, lots of holes in SQL compliance found. The fact that all that wide
> field is not discovered yet, doesn't make Postgre "the best".

Postgres is quite widely used already, though not as widely as MySQL, so it 
gets 
a big workout and exposure of bugs.

One big reason I recommend Postgres as a first choice now is that I have some 
familiarity with the community that makes it.

The Postgres makers take quality and reliability as top concerns, and have for 
a 
long time, so to make the product much more solid.  They have high standards 
for 
declaring the DBMS production ready and lengthy testing/shakeout periods.

Despite this, Postgres still releases a major version about once per year, 
where 
each version goes through alpha/beta/RC/etc stages on a semi-predictable 
schedule.

And then after it is released, a major version is only updated minimally, to 
fix 
security or other bugs that become known, so users can be confident that minor 
version updates are just going to be more solid and not risk breaks due to 
larger changes.  New features or non-bugfix changes only come out in the yearly 
major versions.

I don't believe that MySQL development has anywhere near this kind of rigor.

See also the Change logs for both products with each minor release and just 
what 
kinds of bugs each one is fixing, including their severity.

-- Darren Duncan
___
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?

[Dagdamor]

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 non-lite choice should be Postgres.

Wow, communistic regime is back! Thanks for telling me what I should do and 
what not, what I should use and what not. ;)

Mind you, once Postgre will become more or less known world-wide (its usage is 
not comparable with either MySQL or SQLite which are way more popular), you 
will have exactly the same issues: lots of bugs open, lots of features missing, 
lots of holes in SQL compliance found. The fact that all that wide field is not 
discovered yet, doesn't make Postgre "the best".

Regards,
Serge
___
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?

[Dagdamor]

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.
>
> There's more to transactions than just grouping together multiple SQL
> statements.
>
> If you do any INSERTs, UPDATEs, or DELETEs at all, you need
> transactions, even if there are no indexes involved, even if the
> individual statements don't affect each other.
>
> Even a simple insert can involve multiple operations like requesting
> more disk space from the OS, rearranging pointers in the database file
> on the disk, etc.
>
> What happens if there's a power failure while it's in the middle of that
> insert?  With transactions, it gets rolled back when things start up
> again.  Without, you could wind up with a corrupted database.

You are mixing two different issues: atomic operations and automatic repair 
after crash.

MyISAM tables *are* atomic. Even if you update 1000 rows at once, another 
process will never see a moment when only 500 of them are updated and another 
500 are not.

About repair, yes, MySQL is not as much concerned about that, and it never 
became a problem to me either (my servers don't crash every day :)). See, if 
your server died in the middle of transaction, the best thing even a 
transactional DBMS can do it to roll it back. *But you will lose that data 
anyway*, despite that the table would be "not corrupted", and usually, data 
loss is more serious issue rather than a need to run table check for several 
seconds.

MyISAM tables would never become so popular if automatic rollback after crash 
was a serious issue for server owners.

Regards,
Serge
___
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?

[Darren Duncan]

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, 
for 
projects not currently using it, when you have a choice between multiple SQL 
DBMSs; instead, the default non-lite choice should be Postgres.  If you don't 
know the difference, then Postgres will serve you much better and keep you 
safer.  Just choose MySQL if you are informed enough about various SQL DBMSs 
and 
can thereby justify that MySQL will actually serve your needs better.  One 
reason for this is that Postgres defaults to more safer behaviors, while with 
MySQL you have to explicitly ask for some of the same safety nets, and people 
less knowledgeable about it won't know to do this.

I will also say that for business-level use, it is no justification to say that 
MySQL is your only choice because that is the only thing the web host provides. 
  If you're a serious business user, you have a lot more leverage to pick and 
choose any choice of software you want.

> 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.

See also my prior reply, about CHECK having never been fixed/supported in MySQL.

-- Darren Duncan
___
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?

[Joe D]

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 never, ever, seen any database application that did not need 
transactions.  Ever.

There's more to transactions than just grouping together multiple SQL 
statements.

If you do any INSERTs, UPDATEs, or DELETEs at all, you need 
transactions, even if there are no indexes involved, even if the 
individual statements don't affect each other.

Even a simple insert can involve multiple operations like requesting 
more disk space from the OS, rearranging pointers in the database file 
on the disk, etc.

What happens if there's a power failure while it's in the middle of that 
insert?  With transactions, it gets rolled back when things start up 
again.  Without, you could wind up with a corrupted database.

Joe D
___
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?

[Dagdamor]

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 (since it's hosted there),
> but it points out some fairly specific differences in features,
> performance, etc.  (I have no personal experience either way, but was
> just curious myself).

Posting stuff like that on your site is pathetic >:( If you build a nice DBMS, 
you should work on how to make it even better, not on how to slander different 
DBMSes.

I work with MySQL for years, both in small and medium-loaded applications, and 
it works like a charm for me. Extremely fast, extremely reliable, full of nice 
features like native fulltext support. If you need a non-transactional 
(atomic), very fast, reliable and simple server-like DBMS, then MySQL+MyISAM 
would suit you perfectly. If you need transactions (although in most of the web 
cases you don't need them), you can use InnoDB. And if your application is 
heavy, then I guess Oracle would be a better choice for you.

After reading this article, I'm starting to disrespect Postgre :( The article 
is very questionable in places. Yes, SQLite documentation also has comparisons 
against MySQL, but it never offends it.

Regards,
Serge
___
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?

[Darren Duncan]

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 but
>> ignored by all storage engines."
> 
> Yes and no. Apparently the above was true before 5.0.2, but apparently it has
> been fixed since then. From the docs,

No, it hasn't been fixed at all.

Check constraints are accepted and silently ignored, and even with strictness 
enabled, you still don't even get a warning, never mind a rejection.

 From http://dev.mysql.com/doc/refman/5.5/en/create-table.html (the MySQL 5.5 
manual):  "The CHECK clause is parsed but ignored by all storage engines.".

Whenever I listed a MySQL deficiency in this thread, that I encountered, I made 
sure to check whether the latest MySQL version still had the problem, and I 
only 
reported deficiencies that are not addressed in the latest available version, 
so 
5.5.x in this case.

Also, I'm using version 5.0.30 specifically, which is newer than 5.0.2.

Observe ...

---

mysql> create table test2 (myposint integer, check (myposint > 0));
Query OK, 0 rows affected (0.00 sec)

mysql> SET sql_mode = 'STRICT_TRANS_TABLES'; SET sql_mode = 
'STRICT_ALL_TABLES';Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test2 set myposint = -1;
Query OK, 1 row affected (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> select * from test2;
+--+
| myposint |
+--+
|   -1 |
+--+
1 row in set (0.00 sec)

mysql> desc test2;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| myposint | int(11) | YES  | | NULL|   |
+--+-+--+-+-+---+
1 row in set (0.00 sec)

---

> "Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values
> and coerces them to legal values for data entry. In MySQL 5.0.2 and up, that
> remains the default behavior, but you can change the server SQL mode to
> select more traditional treatment of bad values such that the server rejects
> them and aborts the statement in which they occur."
> 
> and
> 
> "In MySQL 5.0.2 and up, you can select stricter treatment of input values by
> using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES SQL modes:
> 
> SET sql_mode = 'STRICT_TRANS_TABLES'; SET sql_mode = 'STRICT_ALL_TABLES';

That is talking about inserting values not of the declared base types of the 
columns, and not about CHECK constraints.

-- Darren Duncan
___
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?

[Ian Hardingham]

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 seconds, is:
>>
>> SELECT * FROM multiturnTable WHERE id=? LIMIT 1
>>
>> UPDATE multiturnTable SET p1SubmitScore=1 WHERE id=?
>>
>> UPDATE multiturnTable SET complete=1, score=? WHERE id=?
>>
>> SELECT * FROM userTable WHERE name='?'  twice
>>
>> UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?,
>> dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?',
>> scoreStreak='?', scoreStreakNumber=? WHERE name='?';   twice
> If properly constructed, I would expect this to be almost instant.
>
> I suggest:
>
> 1. Use integer columns instead of text for id primary key
> 2. Use foreign keys (ie "references")
> 3. Atomize your columns, ie only one piece of data per column (eg don't store 
> record as number tab number in one column).
> 4. For any column that is multiple choice (eg status), don't store as text, 
> store as an integer that references another table.
> 5. In general, properly normalise your tables, which includes the above. It's 
> hard to be more precise without knowing your data better.
> 5. Use triggers to update the related user table when a match changes (eg 
> ends).
> 6. Do all of an event in one transaction.
> 7. Do it all (or as much as possible) of an event in SQL, rather than passing 
> values back from SQL into application then back to SQL.
>
> At the basic level, do the foloowing:
>
> Change multiturnTable to this (I've only really changed the first three 
> columns):
>
> create table multiturnTable
> ( id integer primary key not null
> , player1 integer not null references userTable(id)
> , player2 integer not null references userTable(id)
> , date text
> , rating real
> , info text
> , complete integer
> , currentTurn integer
> , p1Submitted integer
> , p2Submitted integer
> , score real
> , gamemode text
> , turnLimit integer
> , turnTimeLimit integer
> , p1SubmitScore integer
> , p2SubmitScore integer
> , quickMatchId integer
> , nComments integer default 0
> , p1TurnSubTime real
> , p2TurnSubTime float
> , nRatings integer
> , p1GivenUp integer
> , p2GivenUp integer
> , dupId integer default -1
> , p1Declined integer default 0
> , p2Declined integer default 0
> , lastP1CommitTime text default '-1'
> , lastP2CommitTime text default '-1'
> )
> ;
>
> Change userTable to this:
>
> create table userTable
> ( id integer primary key not null
> , name text not null unique collate nocase
> , email text collate nocase
> , key text
> , status text
> , date text
> , playedFor integer
> , totalScore real default 0
> , totalRecordHigh integer default 0
> , totalRecordLow integer default 0
> , dailyScore real default 0
> , dailyRecordHigh integer default 0
> , dailyRecordLow integer default 0
> , dailyGameRecordHigh integer default 0
> , dailyGameRecordLow integer default 0
> , dailyGamesPlayed integer default 0
> , scoreStreak text default ''
> , scoreStreakNumber integer default 0
> , noEmail integer default 0
> , playedInfIds text default ''
> )
> ;
>
> -- These calculations are guesses. You will need to correct them:
>
> create trigger multiturnEndMatch
> after update of complete
> on multiturnTable
> when new.complete = 1 and old.complete != 1
> begin
>   update userTable
>   set totalScore = totalScore + new.p1SubmitScore
>   ,   dailyScore = dailyScore + new.p1SubmitScore
>   ,   dailyGamesPlayed = dailyGamesPlayed + 1
>   ,   scoreStreak = scoreStreak + new.p1SubmitScore
>   ,   scoreStreakNumber = scoreStreakNumber + 1
>   where id = old.player1
>   ;
>   update userTable
>   set totalScore = totalScore + new.p2SubmitScore
>   ,   dailyScore = dailyScore + new.p2SubmitScore
>   ,   dailyGamesPlayed = dailyGamesPlayed + 1
>   ,   scoreStreak = scoreStreak + new.p2SubmitScore
>   ,   scoreStreakNumber = scoreStreakNumber + 1
>   where id = old.player2
>   ;
> -- if player1 won:
>   update userTable
>   set totalRecordHigh = new.p1SubmitScore
>   ,   totalRecordLow = totalRecordLow + new.p2SubmitScore
>   where   new.p1SubmitScore>  new.p2SubmitScore and totalRecordHigh<  
> new.p1SubmitScore
>   ;
>   update userTable
>   set dailyRecordHigh = new.p1SubmitScore
>   ,   dailyRecordLow = new.p2SubmitScore
>   where   new.p1SubmitScore>  new.p2SubmitScore and dailyRecordHigh<  
> new.p1SubmitScore
>   ;
>   update userTable
>   dailyGameRecordHigh = new.p1SubmitScore
>   ,   dailyGameRecordLow = new.p2SubmitScore
>   

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

[Mr. Puneet Kishor]

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 starting work on a project that uses MySQL
>> exclusively, and has done so for years. They have been very happy with it.
>> And, even though I feel like telling them that they should move to Pg, I
>> don't really know what the reasons are. I am not sure if all the reasons that
>> might be, are still valid.
>> Of course, I don't want this to become a long, religious threat that might be
>> inappropriate for this list, or bore most of the other readers to tears. But,
>> it merits asking, why should MySQL be avoided like the plague? It is a strong
>> statement that requires backing evidence, else it would be construed FUD.
> 
> Perhaps my statement was a bit strong, so I will clarify a bit.
> 

You don't say. "Should be avoided like the plague" is way more than a bit, 
hence, my response.


> *And* I'll give concrete examples.
> 
> 1.  Firstly, the context for my statement is someone who is not currently 
> using MySQL, and so they don't already have an investment in it and codebase 
> designed for it.
> 
> If one is already using MySQL, then that is the status quo and the question 
> is on whether benefits from a change to something else is worth the effort or 
> not.  But if one is not already using it, and their current DBMS can't be 
> used how they need, then they have to change anyway and the question is 
> between whether to move to MySQL or to something else instead; I am 
> addressing this latter situation, and you'll notice I also said sticking with 
> SQLite is even better if its usage can be fixed.


The same could be said of anything... "if its usage can be fixed" is a clause 
that can be interpreted widely.


> 
> 2.  I consider MySQL to be an 80% solution.
> 
> It does the job for which it is used adequately in many cases, and it is 
> successfully used in many places, including to drive many businesses and 
> organizations for mission-critical purposes.
> 


If "it is does the job for which it is used adequately" then it is a 100% 
solution.


> ..
> 
> 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 but ignored by all 
> storage engines."


Yes and no. Apparently the above was true before 5.0.2, but apparently it has 
been fixed since then. From the docs, 

"Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values and 
coerces them to legal values for data entry. In MySQL 5.0.2 and up, that 
remains the default behavior, but you can change the server SQL mode to select 
more traditional treatment of bad values such that the server rejects them and 
aborts the statement in which they occur."

and

"In MySQL 5.0.2 and up, you can select stricter treatment of input values by 
using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES SQL modes:

SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';




> 
> 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.  (Okay, I knew about this one previously.)


Shown by another poster to not be true.

No point in going through all the issues you pointed out. I am sure some of 
them are true, while others no longer true, or true but have work-arounds.


> ..
> 
> I say avoid MySQL like the plague because it will bite you in so many ways, 
> while an alternative like Postgres will only bite you in relatively few and 
> less painful ways.  Postgres is more of a 90-95% solution relative to MySQL's 
> 80%, assuming that there is no 100% solution.
> 
> I also know quite a number of savvy people in the developer communities who 
> have used both Postgres and MySQL, and a vast majority of those prefer 
> Postgres and strongly recommend it over MySQL when one has a choice, and so 
> do I.
> 

The main thing is to be aware of as much as possible, and then figure out 
work-arounds. No technology is perfect. There could be serious, 
project-specific show-stoppers that would cause one to choose a particular tech 
over another, but those would be project-specific.

I have no love for MySQL. All I want to emphasize is that statements like 
"avoid it like the plague" become sound-bytes with no substance. They are 
hollow and misleading as "Having problems? Get a Mac" or, on the other side, 
"Don't use Macs for serious work" or "Perl is dead" or "Apple is dead" and the 
like. These statements are fine for bloggers and tech journalists to make for 
their aim is to get page-hits and their 

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

[BareFeetWare]

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 p1SubmitScore=1 WHERE id=?
> 
> UPDATE multiturnTable SET complete=1, score=? WHERE id=?
> 
> SELECT * FROM userTable WHERE name='?'  twice
> 
> UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?, 
> dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?', 
> scoreStreak='?', scoreStreakNumber=? WHERE name='?';   twice

If properly constructed, I would expect this to be almost instant.

I suggest:

1. Use integer columns instead of text for id primary key
2. Use foreign keys (ie "references")
3. Atomize your columns, ie only one piece of data per column (eg don't store 
record as number tab number in one column).
4. For any column that is multiple choice (eg status), don't store as text, 
store as an integer that references another table.
5. In general, properly normalise your tables, which includes the above. It's 
hard to be more precise without knowing your data better.
5. Use triggers to update the related user table when a match changes (eg ends).
6. Do all of an event in one transaction.
7. Do it all (or as much as possible) of an event in SQL, rather than passing 
values back from SQL into application then back to SQL.

At the basic level, do the foloowing:

Change multiturnTable to this (I've only really changed the first three 
columns):

create table multiturnTable
(   id integer primary key not null
,   player1 integer not null references userTable(id)
,   player2 integer not null references userTable(id)
,   date text
,   rating real
,   info text
,   complete integer
,   currentTurn integer
,   p1Submitted integer
,   p2Submitted integer
,   score real
,   gamemode text
,   turnLimit integer
,   turnTimeLimit integer
,   p1SubmitScore integer
,   p2SubmitScore integer
,   quickMatchId integer
,   nComments integer default 0
,   p1TurnSubTime real
,   p2TurnSubTime float
,   nRatings integer
,   p1GivenUp integer
,   p2GivenUp integer
,   dupId integer default -1
,   p1Declined integer default 0
,   p2Declined integer default 0
,   lastP1CommitTime text default '-1'
,   lastP2CommitTime text default '-1'
)
;

Change userTable to this:

create table userTable
(   id integer primary key not null
,   name text not null unique collate nocase
,   email text collate nocase
,   key text
,   status text
,   date text
,   playedFor integer
,   totalScore real default 0
,   totalRecordHigh integer default 0
,   totalRecordLow integer default 0
,   dailyScore real default 0
,   dailyRecordHigh integer default 0
,   dailyRecordLow integer default 0
,   dailyGameRecordHigh integer default 0
,   dailyGameRecordLow integer default 0
,   dailyGamesPlayed integer default 0
,   scoreStreak text default ''
,   scoreStreakNumber integer default 0
,   noEmail integer default 0
,   playedInfIds text default ''
)
;

-- These calculations are guesses. You will need to correct them:

create trigger multiturnEndMatch
after update of complete
on multiturnTable
when new.complete = 1 and old.complete != 1
begin
update userTable
set totalScore = totalScore + new.p1SubmitScore
,   dailyScore = dailyScore + new.p1SubmitScore
,   dailyGamesPlayed = dailyGamesPlayed + 1
,   scoreStreak = scoreStreak + new.p1SubmitScore
,   scoreStreakNumber = scoreStreakNumber + 1
where id = old.player1
;
update userTable
set totalScore = totalScore + new.p2SubmitScore
,   dailyScore = dailyScore + new.p2SubmitScore
,   dailyGamesPlayed = dailyGamesPlayed + 1
,   scoreStreak = scoreStreak + new.p2SubmitScore
,   scoreStreakNumber = scoreStreakNumber + 1
where id = old.player2
;
-- if player1 won:
update userTable
set totalRecordHigh = new.p1SubmitScore
,   totalRecordLow = totalRecordLow + new.p2SubmitScore
where   new.p1SubmitScore > new.p2SubmitScore and totalRecordHigh < 
new.p1SubmitScore
;
update userTable
set dailyRecordHigh = new.p1SubmitScore
,   dailyRecordLow = new.p2SubmitScore
where   new.p1SubmitScore > new.p2SubmitScore and dailyRecordHigh < 
new.p1SubmitScore
;
update userTable
dailyGameRecordHigh = new.p1SubmitScore
,   dailyGameRecordLow = new.p2SubmitScore
where   new.p1SubmitScore > new.p2SubmitScore and dailyGameRecordHigh < 
new.p1SubmitScore
;
-- if player2 won:
update userTable
set totalRecordHigh = 

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

[Ian Hardingham]

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
___
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?

[Luuk]

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.  (Okay, I knew about this one previously.)

It does not do so,
it gives a warning,
its up to the user to decide if something is done with that.

10:44:31 root@test[16]mysql> desc test1;
+-+-+--+-+-++
| Field   | Type| Null | Key | Default | Extra  |
+-+-+--+-+-++
| id  | int(8)  | NO   | PRI | NULL| auto_increment |
| USER_ID | int(8)  | NO   | MUL | NULL||
| TEXT| varchar(64) | NO   | | NULL||
+-+-+--+-+-++
3 rows in set (0.00 sec)

10:44:38 root@test[17]mysql> select * from test1 where user_id=;
Empty set (0.00 sec)

10:44:49 root@test[18]mysql> insert into test1 (user_id, text) values
(,'abcdefghijklmnqrstuvwxyzabcdefghijklmnqrstuvwxyzabcdefghijklmnqrstuvwxyzabcdefghijklmnqrstuvwxyzabcdefghijklmnqrstuvwxyzabcdefghijklmnqrstuvwxyzabcdefghijklmnqrstuvwxyz');
Query OK, 1 row affected, 1 warning (0.00 sec)

10:45:04 root@test[19]mysql> show warnings;
+-+--+---+
| Level   | Code | Message   |
+-+--+---+
| Warning | 1265 | Data truncated for column 'TEXT' at row 1 |
+-+--+---+
1 row in set (0.00 sec)

10:45:19 root@test[20]mysql>

___
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?

[Darren Duncan]

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
> exclusively, and has done so for years. They have been very happy with it.
> And, even though I feel like telling them that they should move to Pg, I
> don't really know what the reasons are. I am not sure if all the reasons that
> might be, are still valid.
> 
> Of course, I don't want this to become a long, religious threat that might be
> inappropriate for this list, or bore most of the other readers to tears. But,
> it merits asking, why should MySQL be avoided like the plague? It is a strong
> statement that requires backing evidence, else it would be construed FUD.

Perhaps my statement was a bit strong, so I will clarify a bit.

*And* I'll give concrete examples.

1.  Firstly, the context for my statement is someone who is not currently using 
MySQL, and so they don't already have an investment in it and codebase designed 
for it.

If one is already using MySQL, then that is the status quo and the question is 
on whether benefits from a change to something else is worth the effort or not. 
  But if one is not already using it, and their current DBMS can't be used how 
they need, then they have to change anyway and the question is between whether 
to move to MySQL or to something else instead; I am addressing this latter 
situation, and you'll notice I also said sticking with SQLite is even better if 
its usage can be fixed.

2.  I consider MySQL to be an 80% solution.

It does the job for which it is used adequately in many cases, and it is 
successfully used in many places, including to drive many businesses and 
organizations for mission-critical purposes.

At the same time, MySQL has a lot of severe flaws, including bugs, 
mis-features, 
and missing useful features.  I won't go into too many details on this here 
because a lot has been written on the subject already that you can reach with 
Google, although I will give some examples.

So, you could do much worse than MySQL, but you could also do much better.

3.  I have many years of personal experience with SQL DBMSs both large and 
small, including many years in using MySQL in production at multiple sites; my 
current main job uses MySQL in fact, so I'm using it day in and out today.  I 
have personally found numerous ways in that MySQL lets me down and I have to 
work around it, where in my usage of Postgres it has not let me down.

Here are a few *current* examples that I discovered (I had previously known of 
many more) because they bit me personally in the last few months (using MySQL 
5.0, though from my reading these are unfixed in the latest versions):

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 but ignored by all storage 
engines."

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.  (Okay, I knew about this one previously.)

c.  MySQL treats all subqueries in the WHERE clause as being "dependent 
subquery" even if they are in fact "independent" (have no free variables to be 
filled in by the outer query), so they reexecute the inner query for every row 
in the outer, instead of running the inner just once.  This is a severe 
performance drain, and so an example query that took 1 second if reformatted as 
a FROM subquery plus join would take over 10 minutes (before I killed it) as a 
"IN" subquery.  And this is on tables that are all properly indexed.  The WHERE 
version is much more concise code than the alternative, which is 2-3X as 
verbose.

d.  MySQL seems incapable of using indexes on derived tables to make them 
faster, not automatically nor provides a way to manually specify the use of 
such.  So we use a bunch of explicit temporary tables with explicit indexes.

e.  All MySQL versions have a serious limitation where you can't refer to the 
same temporary table more than once in the same statement or stored function.
See http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html .  So 
then you can't use temporary tables either to refactor common parts of a query.

f.  My understanding is that many MySQL constraints only affect data 
manipulation done after they were defined; adding constraints to a table won't 
catch bad data already in the tables; I haven't personally verified this one.

And those are just the tip of the iceberg.  See Google.  Or MySQL's own manual, 
which spells out many of its deficiencies.

I say avoid MySQL like the plague because it will bite you in so many 

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

[Matthew L. Creech]

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:

http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009

Obviously a bit biased toward PostgreSQL (since it's hosted there),
but it points out some fairly specific differences in features,
performance, etc.  (I have no personal experience either way, but was
just curious myself).

-- 
Matthew L. Creech
___
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?

[Teg]

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 SQlite. In fact, the thing that feeds the MySQL DB
reads Sqlite DB's and then feeds this data into MySQL every 15 minutes
or so.

I'd have no problem recommending MySQL for mission critical uses.
Postgres might be better but, MySQL's been good enough.

C



Friday, June 3, 2011, 2:19:44 PM, you wrote:

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

DD> Ian Hardingham 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 impression that 
>> SQLite is pefectly fast and it's the scripting language I wrote the 
>> server in which is too blame.  (Yes, I know writing a back-end in a 
>> single-threaded scripting language is an absolutely terrible idea).  
>> However, everyone in the industry I talk to says that SQLite must be one 
>> of the problems.
>> 
>> 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?
>> 
>> (The video on that website at 2.04 gives a good idea of what kind of 
>> functions are being powered by the database).
>> 
>> Thanks,
>> Ian

DD> ___
DD> sqlite-users mailing list
DD> sqlite-users@sqlite.org
DD> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards,
 Tegmailto:t...@djii.com

___
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?

[Mr. Puneet Kishor]

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 have been very happy with it. And, 
even though I feel like telling them that they should move to Pg, I don't 
really know what the reasons are. I am not sure if all the reasons that might 
be, are still valid.

Of course, I don't want this to become a long, religious threat that might be 
inappropriate for this list, or bore most of the other readers to tears. But, 
it merits asking, why should MySQL be avoided like the plague? It is a strong 
statement that requires backing evidence, else it would be construed FUD.




>  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 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 fast and it's the scripting language I wrote the 
>> server in which is too blame.  (Yes, I know writing a back-end in a 
>> single-threaded scripting language is an absolutely terrible idea).  
>> However, everyone in the industry I talk to says that SQLite must be one 
>> of the problems.
>> 
>> 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?
>> 
>> (The video on that website at 2.04 gives a good idea of what kind of 
>> functions are being powered by the database).
>> 
>> Thanks,
>> Ian
> 
> ___
> 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] Do I need to migrate to MySQL?

[Simon Slavin]

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?

[Darren Duncan]

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 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 fast and it's the scripting language I wrote the 
> server in which is too blame.  (Yes, I know writing a back-end in a 
> single-threaded scripting language is an absolutely terrible idea).  
> However, everyone in the industry I talk to says that SQLite must be one 
> of the problems.
> 
> 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?
> 
> (The video on that website at 2.04 gives a good idea of what kind of 
> functions are being powered by the database).
> 
> Thanks,
> Ian

___
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?

[Ian Hardingham]

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 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?  I'm running a fairly fast modern intel chip here.
>
> In journal mode, yes: that's in the ballpark of what three fsync() 
> calls should take, and that's how many fsync calls SQLite makes at 
> commit time in journal mode.  WAL mode should go faster because most 
> often it does a single fsync().  But even three times faster will seem 
> too slow to you, no doubt.  To go faster consider batching updates 
> into larger transactions (so as to amortize the synchronous I/O cost) 
> or using fast flash devices to hold your DB.  Also, serialize your 
> writing if you can.  Finally, consider some other method of obtaining 
> durability, such as logging updates to a large, widely distributed 
> memcache farm, then disable fsync for most transactions.
>
> Nico
> -- 
>

___
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?

[Simon Slavin]

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 be cleanly written to disk.

This is also what happens if you omit the BEGIN and END SQL commands entirely.  
SQLite notices you're doing an INSERT/UPDATE without having opened a 
transaction for it, and makes that statement its own little transaction.  Then 
it finds your next change and, because it has already closed its transaction, 
has to do the same thing for that one too.  Etc. etc..

To make multiple changes as fast as possible, do a SQL 'BEGIN' command before 
your first change, and an END after the last change.  This will tell SQLite it 
can lump all the changes into one big transaction and do them all in a single 
burst of activity.

[Above explanation simplified a little for brevity.  Handwave.]

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?

[Nico Williams]

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 lots of INSERT/UPDATE/DELETEs that it really pays to use one large
transaction for them (also, for atomicity and isolation).

Nico
--
___
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?

[Nico Williams]

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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Nico Williams]

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?  I'm running a fairly fast modern intel chip here.

In journal mode, yes: that's in the ballpark of what three fsync() calls
should take, and that's how many fsync calls SQLite makes at commit time in
journal mode.  WAL mode should go faster because most often it does a single
fsync().  But even three times faster will seem too slow to you, no doubt.
To go faster consider batching updates into larger transactions (so as to
amortize the synchronous I/O cost) or using fast flash devices to hold your
DB.  Also, serialize your writing if you can.  Finally, consider some other
method of obtaining durability, such as logging updates to a large, widely
distributed memcache farm, then disable fsync for most transactions.

Nico
--
___
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?

[Black, Michael (IS)]

I should add you only need it around MULTIPLE statements too.

I don't believe there's any benefit around a single statement.



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [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 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, int hack)
{
int iResult;
sqlite_resultset* pResultSet;
ClearErrorString();

// create a new resultset
pResultSet = new sqlite_resultset;

if (pResultSet)
{
   pResultSet->bValid = false;
   pResultSet->iCurrentColumn = 0;
   pResultSet->iCurrentRow = 0;
   pResultSet->iNumCols = 0;
   pResultSet->iNumRows = 0;
   pResultSet->iResultSet = m_iNextResultSet;
   pResultSet->vRows.clear();
   m_iLastResultSet = m_iNextResultSet;
   m_iNextResultSet++;
}
else
   return 0;


 iResult = sqlite3_exec(m_pDatabase, sql, Callback,
(void*)pResultSet, _szErrorString);

 if (iResult == 0)
 {
 //SQLITE_OK


 SaveResultSet(pResultSet);
 //Con::executef(this, 1, "onQueryFinished()");

#ifdef PROFILE_DB
 Con::errorf("--- NEW DB RESULT SET: %i", pResultSet->iResultSet);
#endif

 return pResultSet->iResultSet;

 }
 else
 {
 // error occured
 Con::executef(this, 2, "onQueryFailed", m_szErrorString);
 delete pResultSet;
 return 0;
 }

return 0;
}

On 03/06/2011 15:28, Black, Michael (IS) wrote:
>
> And do you wrap all your updates inside a transaction?
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
> 
>

___
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] Do I need to migrate to MySQL?

[Black, Michael (IS)]

You only need it around INSERT or UPDATE, not SELECT.



The transaction delays the commit to disk so only transactions that write 
benefit from it.



It makes a HUGE difference in speed.



You may also find increasing your cache size could help.  How big is your 
database?



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [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 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, int hack)
{
int iResult;
sqlite_resultset* pResultSet;
ClearErrorString();

// create a new resultset
pResultSet = new sqlite_resultset;

if (pResultSet)
{
   pResultSet->bValid = false;
   pResultSet->iCurrentColumn = 0;
   pResultSet->iCurrentRow = 0;
   pResultSet->iNumCols = 0;
   pResultSet->iNumRows = 0;
   pResultSet->iResultSet = m_iNextResultSet;
   pResultSet->vRows.clear();
   m_iLastResultSet = m_iNextResultSet;
   m_iNextResultSet++;
}
else
   return 0;


 iResult = sqlite3_exec(m_pDatabase, sql, Callback,
(void*)pResultSet, _szErrorString);

 if (iResult == 0)
 {
 //SQLITE_OK


 SaveResultSet(pResultSet);
 //Con::executef(this, 1, "onQueryFinished()");

#ifdef PROFILE_DB
 Con::errorf("--- NEW DB RESULT SET: %i", pResultSet->iResultSet);
#endif

 return pResultSet->iResultSet;

 }
 else
 {
 // error occured
 Con::executef(this, 2, "onQueryFailed", m_szErrorString);
 delete pResultSet;
 return 0;
 }

return 0;
}

On 03/06/2011 15:28, Black, Michael (IS) wrote:
>
> And do you wrap all your updates inside a transaction?
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
> 
>

___
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] Do I need to migrate to MySQL?

[Ian Hardingham]

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, int hack)
{
int iResult;
sqlite_resultset* pResultSet;
ClearErrorString();

// create a new resultset
pResultSet = new sqlite_resultset;

if (pResultSet)
{
   pResultSet->bValid = false;
   pResultSet->iCurrentColumn = 0;
   pResultSet->iCurrentRow = 0;
   pResultSet->iNumCols = 0;
   pResultSet->iNumRows = 0;
   pResultSet->iResultSet = m_iNextResultSet;
   pResultSet->vRows.clear();
   m_iLastResultSet = m_iNextResultSet;
   m_iNextResultSet++;
}
else
   return 0;


 iResult = sqlite3_exec(m_pDatabase, sql, Callback, 
(void*)pResultSet, _szErrorString);

 if (iResult == 0)
 {
 //SQLITE_OK


 SaveResultSet(pResultSet);
 //Con::executef(this, 1, "onQueryFinished()");

#ifdef PROFILE_DB
 Con::errorf("--- NEW DB RESULT SET: %i", pResultSet->iResultSet);
#endif

 return pResultSet->iResultSet;

 }
 else
 {
 // error occured
 Con::executef(this, 2, "onQueryFailed", m_szErrorString);
 delete pResultSet;
 return 0;
 }

return 0;
}

On 03/06/2011 15:28, Black, Michael (IS) wrote:
>
> And do you wrap all your updates inside a transaction?
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
> 
>

___
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?

[Black, Michael (IS)]

And do you wrap all your updates inside a transaction?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-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 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=? WHERE id=?

SELECT * FROM userTable WHERE name='?'  twice

UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?,
dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?',
scoreStreak='?', scoreStreakNumber=? WHERE name='?';   twice


The setup of the various tables are:

CREATE TABLE IF NOT EXISTS multiturnTable (id INTEGER PRIMARY KEY NOT
NULL UNIQUE, player1 TEXT COLLATE NOCASE, player2 COLLATE NOCASE, date
TEXT, rating REAL, info TEXT, complete INTEGER, currentTurn INTEGER,
p1Submitted INTEGER, p2Submitted INTEGER, score REAL, gamemode TEXT,
turnLimit INTEGER, turnTimeLimit INTEGER, p1SubmitScore INTEGER,
p2SubmitScore INTEGER, quickMatchId INTEGER, nComments INTEGER DEFAULT
0, p1TurnSubTime FLOAT, p2TurnSubTime FLOAT, nRatings INT, p1GivenUp
INT, p2GivenUp INT, dupId INT DEFAULT -1, p1Declined INT DEFAULT 0,
p2Declined INT DEFAULT 0, lastP1CommitTime TEXT DEFAULT '-1',
lastP2CommitTime TEXT DEFAULT '-1');";

Multiturn has about 200,000 rows at present

CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL
UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date
TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT
DEFAULT '0\t0', dailyScore FLOAT DEFAULT 0, dailyRecord TEXT DEFAULT
'0\t0', dailyGameRecord TEXT DEFAULT '', dailyGamesPlayed INTEGER
DEFAULT 0, scoreStreak TEXT DEFAULT '', scoreStreakNumber INT DEFAULT 0,
noEmail INT DEFAULT 0, playedInfIds TEXT DEFAULT '');";

Usertable has about 40,000 rows at present.

I have the following indexes:

db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer1 ON multiturnTable
(player1)", 0);
db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer2 ON multiturnTable
(player2)", 0);


Thanks,
Ian

On 03/06/2011 13:57, BareFeetWare wrote:
> 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 separate tables.
> Send us the schema of the above tables and the SQL that you execute that 
> takes 2 seconds.
>
> Tom
> BareFeetWare
>
>   --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>

___
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] Do I need to migrate to MySQL?

[Alexey Pechnikov]

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:
>
> 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
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
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?

[Black, Michael (IS)]

My radar says having a TEXT field as a primary key is bad (your userTable).  
String compares are horrendously slow.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-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 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=? WHERE id=?

SELECT * FROM userTable WHERE name='?'  twice

UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?,
dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?',
scoreStreak='?', scoreStreakNumber=? WHERE name='?';   twice


The setup of the various tables are:

CREATE TABLE IF NOT EXISTS multiturnTable (id INTEGER PRIMARY KEY NOT
NULL UNIQUE, player1 TEXT COLLATE NOCASE, player2 COLLATE NOCASE, date
TEXT, rating REAL, info TEXT, complete INTEGER, currentTurn INTEGER,
p1Submitted INTEGER, p2Submitted INTEGER, score REAL, gamemode TEXT,
turnLimit INTEGER, turnTimeLimit INTEGER, p1SubmitScore INTEGER,
p2SubmitScore INTEGER, quickMatchId INTEGER, nComments INTEGER DEFAULT
0, p1TurnSubTime FLOAT, p2TurnSubTime FLOAT, nRatings INT, p1GivenUp
INT, p2GivenUp INT, dupId INT DEFAULT -1, p1Declined INT DEFAULT 0,
p2Declined INT DEFAULT 0, lastP1CommitTime TEXT DEFAULT '-1',
lastP2CommitTime TEXT DEFAULT '-1');";

Multiturn has about 200,000 rows at present

CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL
UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date
TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT
DEFAULT '0\t0', dailyScore FLOAT DEFAULT 0, dailyRecord TEXT DEFAULT
'0\t0', dailyGameRecord TEXT DEFAULT '', dailyGamesPlayed INTEGER
DEFAULT 0, scoreStreak TEXT DEFAULT '', scoreStreakNumber INT DEFAULT 0,
noEmail INT DEFAULT 0, playedInfIds TEXT DEFAULT '');";

Usertable has about 40,000 rows at present.

I have the following indexes:

db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer1 ON multiturnTable
(player1)", 0);
db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer2 ON multiturnTable
(player2)", 0);


Thanks,
Ian

On 03/06/2011 13:57, BareFeetWare wrote:
> 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 separate tables.
> Send us the schema of the above tables and the SQL that you execute that 
> takes 2 seconds.
>
> Tom
> BareFeetWare
>
>   --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>

___
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] Do I need to migrate to MySQL?

[Ian Hardingham]

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
___
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?

[Igor Tandetnik]

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 nearly instantaneously. The time must be spent 
elsewhere.

> SELECT * FROM multiturnTable WHERE id=? LIMIT 1

Since id is a primary key, you can drop LIMIT 1 clause.

> UPDATE multiturnTable SET p1SubmitScore=1 WHERE id=?
> UPDATE multiturnTable SET complete=1, score=? WHERE id=?

You could probably combine these two in a single statement.

> SELECT * FROM userTable WHERE name='?'  twice

I assume it's a typo, but just in case it's not, be aware that '?' is *not* a 
parameter placeholder, but a string consisting of a single character '?'. Make 
it

SELECT * FROM userTable WHERE name=?

> UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?,
> dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?',
> scoreStreak='?', scoreStreakNumber=? WHERE name='?';   twice

Same here - all instances of '?' should be simply ?.

> 
> 
> The setup of the various tables are:
> 
> CREATE TABLE IF NOT EXISTS multiturnTable (id INTEGER PRIMARY KEY NOT
> NULL UNIQUE, player1 TEXT COLLATE NOCASE, player2 COLLATE NOCASE, date

PRIMARY KEY implies NOT NULL and UNIQUE.

Should probably be "player2 TEXT COLLATE NOCASE" for symmetry. I don't think 
that could cause the slowdown, though.
-- 
Igor Tandetnik

___
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?

[Ian Hardingham]

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=? WHERE id=?

SELECT * FROM userTable WHERE name='?'  twice

UPDATE userTable SET totalScore=?, totalRecord='?', dailyScore=?, 
dailyRecord='?', dailyGameRecord='?', dailyGamesPlayed='?', 
scoreStreak='?', scoreStreakNumber=? WHERE name='?';   twice


The setup of the various tables are:

CREATE TABLE IF NOT EXISTS multiturnTable (id INTEGER PRIMARY KEY NOT 
NULL UNIQUE, player1 TEXT COLLATE NOCASE, player2 COLLATE NOCASE, date 
TEXT, rating REAL, info TEXT, complete INTEGER, currentTurn INTEGER, 
p1Submitted INTEGER, p2Submitted INTEGER, score REAL, gamemode TEXT, 
turnLimit INTEGER, turnTimeLimit INTEGER, p1SubmitScore INTEGER, 
p2SubmitScore INTEGER, quickMatchId INTEGER, nComments INTEGER DEFAULT 
0, p1TurnSubTime FLOAT, p2TurnSubTime FLOAT, nRatings INT, p1GivenUp 
INT, p2GivenUp INT, dupId INT DEFAULT -1, p1Declined INT DEFAULT 0, 
p2Declined INT DEFAULT 0, lastP1CommitTime TEXT DEFAULT '-1', 
lastP2CommitTime TEXT DEFAULT '-1');";

Multiturn has about 200,000 rows at present

CREATE TABLE IF NOT EXISTS userTable (name TEXT PRIMARY KEY NOT NULL 
UNIQUE, password TEXT NOT NULL, email TEXT, key TEXT, status TEXT, date 
TEXT, playedFor INTEGER, totalScore FLOAT DEFAULT 0, totalRecord TEXT 
DEFAULT '0\t0', dailyScore FLOAT DEFAULT 0, dailyRecord TEXT DEFAULT 
'0\t0', dailyGameRecord TEXT DEFAULT '', dailyGamesPlayed INTEGER 
DEFAULT 0, scoreStreak TEXT DEFAULT '', scoreStreakNumber INT DEFAULT 0, 
noEmail INT DEFAULT 0, playedInfIds TEXT DEFAULT '');";

Usertable has about 40,000 rows at present.

I have the following indexes:

db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer1 ON multiturnTable 
(player1)", 0);
db.query("CREATE INDEX IF NOT EXISTS mtTablePlayer2 ON multiturnTable 
(player2)", 0);


Thanks,
Ian

On 03/06/2011 13:57, BareFeetWare wrote:
> 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 separate tables.
> Send us the schema of the above tables and the SQL that you execute that 
> takes 2 seconds.
>
> Tom
> BareFeetWare
>
>   --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>

___
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?

[BareFeetWare]

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 separate tables.

Send us the schema of the above tables and the SQL that you execute that takes 
2 seconds.

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
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?

[Martin Gadbois]

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 general usage help.

May I suggest to measure before optimizing? It is a common mistake to
optimize before optimizing in general.

By measuring your various queries, you may find a nice index that would help
your short term issues, or simply adjusting some parameters. I found in the
past that auto-index would make a query a lot slower, just because the SQL
ANALYZE was not run. Your problems may be a simple as this.

That will leave you with more time if you indeed need to go to a
client/server DB setup.

I think I'll give Frozen Synapse a try...


-- 
Martin
___
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?

[Simon Slavin]

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 
SELECTs.

> - add/remove a friend

Presumably your schema makes this one deletion of one row of one table.  And 
you have a suitable index.

> What is basically happening is that we're getting a fairly large number 
> of requests every second.

Which SQLite library are you using ?  For instance, are you calling SQLite from 
PHP ?  If so, which API are you using ?  Some are faster than others.

> There is one specific activity which takes 
> about 2 seconds to resolve, which is finishing a match.  This requires 
> an update to three separate tables.

Do you do all three updates in a single transaction ?

Oh, and congratulations on the popularity of your project.

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?

[Richard Hipp]

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
> matchTable.  A typical match will last 8 turns - as each player finishes
> a turn, the matchTable entry is updated.  When the match is finished,
> the matchTable is finally updated.
>
> 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
> - add/remove a friend
>
> 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 separate tables.
>

You have set "PRAGMA journal_mode=WAL" I trust?  If not, do so at once.  It
will make a big difference in your application, I think.

You probably don't need to modify your application to do this.  Just bring
up the database file using the sqlite3 command-line shell and type: "PRAGMA
journal_mode=WAL;"  The WAL mode is persist so the database will continue in
WAL mode until you change it.


>
> Ian
>
>
> > 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 impression that
> >> SQLite is pefectly fast and it's the scripting language I wrote the
> >> server in which is too blame.  (Yes, I know writing a back-end in a
> >> single-threaded scripting language is an absolutely terrible idea).
> >> However, everyone in the industry I talk to says that SQLite must be one
> >> of the problems.
> >>
> >> 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?
> >>
> >> (The video on that website at 2.04 gives a good idea of what kind of
> >> functions are being powered by the database).
> >
> > You only uses sqlite for the player lists? What preferences/pragmas do
> > you use for sqlite? Is the server a dedicated server or a shared vps?
> > Which configuration hardware? Tell us more about our configuration and
> > metal so we can help you.
> >
> >> Thanks,
> >> Ian
> >> ___
> >> 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
>



-- 
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] Do I need to migrate to MySQL?

[Ian Hardingham]

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 matchTable entry is updated.  When the match is finished, 
the matchTable is finally updated.

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
- add/remove a friend

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 separate tables.

Ian


> 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 impression that
>> SQLite is pefectly fast and it's the scripting language I wrote the
>> server in which is too blame.  (Yes, I know writing a back-end in a
>> single-threaded scripting language is an absolutely terrible idea).
>> However, everyone in the industry I talk to says that SQLite must be one
>> of the problems.
>>
>> 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?
>>
>> (The video on that website at 2.04 gives a good idea of what kind of
>> functions are being powered by the database).
>
> You only uses sqlite for the player lists? What preferences/pragmas do 
> you use for sqlite? Is the server a dedicated server or a shared vps? 
> Which configuration hardware? Tell us more about our configuration and 
> metal so we can help you.
>
>> Thanks,
>> Ian
>> ___
>> 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] Do I need to migrate to MySQL?

[Eduardo Morras]

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 impression that
>SQLite is pefectly fast and it's the scripting language I wrote the
>server in which is too blame.  (Yes, I know writing a back-end in a
>single-threaded scripting language is an absolutely terrible idea).
>However, everyone in the industry I talk to says that SQLite must be one
>of the problems.
>
>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?
>
>(The video on that website at 2.04 gives a good idea of what kind of
>functions are being powered by the database).

You only uses sqlite for the player lists? What preferences/pragmas 
do you use for sqlite? Is the server a dedicated server or a shared 
vps? Which configuration hardware? Tell us more about our 
configuration and metal so we can help you.

>Thanks,
>Ian
>___
>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] Do I need to migrate to MySQL?

[Alexey Pechnikov]

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 swamped with players, and I'm trying to
> optimise everywhere I can.   I've always been under the impression that
> SQLite is pefectly fast and it's the scripting language I wrote the
> server in which is too blame.  (Yes, I know writing a back-end in a
> single-threaded scripting language is an absolutely terrible idea).
> However, everyone in the industry I talk to says that SQLite must be one
> of the problems.
>
> 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?
>
> (The video on that website at 2.04 gives a good idea of what kind of
> functions are being powered by the database).
>
> Thanks,
> Ian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Do I need to migrate to MySQL?

[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 swamped with players, and I'm trying to 
optimise everywhere I can.   I've always been under the impression that 
SQLite is pefectly fast and it's the scripting language I wrote the 
server in which is too blame.  (Yes, I know writing a back-end in a 
single-threaded scripting language is an absolutely terrible idea).  
However, everyone in the industry I talk to says that SQLite must be one 
of the problems.

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?

(The video on that website at 2.04 gives a good idea of what kind of 
functions are being powered by the database).

Thanks,
Ian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users