Do I need to migrate to MySQL?

classic Classic list List threaded Threaded
42 messages Options
123
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Alexey Pechnikov-2
I start tclsqlite in 16 threads on 2-core Intel Xeon servers and these
work fine.

2011/6/3 Ian Hardingham <[hidden email]>:

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



--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Eduardo Morras
In reply to this post by Ian Hardingham
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
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Richard Hipp-3
On Fri, Jun 3, 2011 at 7:47 AM, Ian Hardingham <[hidden email]> 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
> >> [hidden email]
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Simon Slavin-3
In reply to this post by Ian Hardingham

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Martin Gadbois-2
In reply to this post by Ian Hardingham
On Fri, Jun 3, 2011 at 6:58 AM, Ian Hardingham <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

BareFeetWare-2
In reply to this post by Ian Hardingham
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Igor Tandetnik
Ian Hardingham <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Ian Hardingham-2
Thank you Igor, I'll do some more thorough profiling.

When I run the query:

UPDATE multiturnTable SET complete=1 WHERE id=-50000

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Ian Hardingham
In reply to this post by Igor Tandetnik
Thank you Igor, I'll do some more thorough profiling.

When I run the query:

UPDATE multiturnTable SET complete=1 WHERE id=-50000

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Black, Michael (IS)
In reply to this post by Ian Hardingham
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: [hidden email] [[hidden email]] on behalf of Ian Hardingham [[hidden email]]
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Alexey Pechnikov-2
In reply to this post by Ian Hardingham
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 <[hidden email]>:

> Thank you Igor, I'll do some more thorough profiling.
>
> When I run the query:
>
> UPDATE multiturnTable SET complete=1 WHERE id=-50000
>
> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Black, Michael (IS)
In reply to this post by Ian Hardingham
And do you wrap all your updates inside a transaction?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate



________________________________
From: [hidden email] [[hidden email]] on behalf of Ian Hardingham [[hidden email]]
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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, &m_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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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: [hidden email] [[hidden email]] on behalf of Ian Hardingham [[hidden email]]
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, &m_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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Black, Michael (IS)
In reply to this post by Ian Hardingham
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: [hidden email] [[hidden email]] on behalf of Ian Hardingham [[hidden email]]
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, &m_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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Nico Williams
In reply to this post by Ian Hardingham
On Jun 3, 2011 10:04 AM, "Ian Hardingham" <[hidden email]> wrote:
>
> Thank you Igor, I'll do some more thorough profiling.
>
> When I run the query:
>
> UPDATE multiturnTable SET complete=1 WHERE id=-50000
>
> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Do I need to migrate to MySQL?

Nico Williams
In reply to this post by Alexey Pechnikov-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
123