On Fri, Jun 3, 2011 at 7:47 AM, Ian Hardingham <i...@omroth.com> 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
