Technical point of SuicideChess project. (part 2 : Database)

I have two database, one is for the forum, and one for any suicide related thing.

The forum database was auto-generate via some script when I installed S.M.F forum.

About the suicide one, let start with some stats to start:
There’s about 530 queries / seconds on this database , this is just a average.
The size of database is currently 98.8 Gb (increasing each seconds).
More than half of billions rows in it.

In this database, there’s two principal table:
Move and GameTree

The Move table record the move ID ,FEN, SAN , score , status (« Not calculated », »Compute in proceed », »Done ») , priority and some other technical fields to provide some info for Gore server.

The GameTree table is quite simple, it’s a many-to-many table , from Move table. This provide all moves possibles after a move.
Let give a example: if the move 1.e3 was ID =1 , and the move 1… c4 was ID = 24. In the GameTree, we will find a row like this : 1 , 24

Beside those two table, I have a table that record the result of all Azor analysis, the Calculate one. I keep info like: which Azor analyze this move, which version of Azor it was, time taken , when it have been analyze. I usually keep only the last month in it, this table is use to generate some stats in the Stats section. When the data is more than a month old, I transfer them in a historical table. I keep them for future bug-hunting, cheater research, etc

I have a Version table, that keep info about version of Azor. This table is read when Azor check if your Azor version is the current one. So when I need to do an Azor update, I just need to add a new row in it, and Azor will be auto-update.

I have a Username table, it keep only a id number and the name that users have provided in the config file. It help to provide some stats by users.

I have a main statistical table that keep info to provide the bottom graphic in the Stats section.

Two other tables exist even if now most user can’t use it, it’s the FavoritePosition and MoveComments one. You may have seen already some move comments. Soon it will be able to add comments to any registered users (via the forum).

I didn’t give the exact name of table , field of database , mainly of security reasons. But like you may have seem , I didn’t keep any informations, even the ip address of azor aren’t keep. Because my goal wasn’t to collect personnal info , it just to provide the largest opening-book database.

About queries made most of them are made for Azors and Gore server. In the 24 hours about 150 000 moves have been analyze by Azors, for each of them. There’s some queries to select a high priority move info. After the Azor analysis, there’s a update on move to update the score value and his status and a row is added in the Calculation table. After that, it’s Gore turn, he made a select to retrieve any move with status « Done » and the move doesn’t have any child. Gore create possible child move according to suicide rules, check with a select if the move already exist in the database,: if not, he insert a new move in the table move , and a new row in GameTree table to link those move. If the move already exist, he simply add a row in GameTree table to link the move analyzed by Azor, with his already existed child.

In average in suicide each positions have about 11 possibles move to do. So about 5 queries for Azor, and about 34 queries for Gore server for each analysis made by a Azor.
So of all 150000 move analyze by Azor, there’s about 5.8 millions queries made to the database.
And when all childs of a move are analyzed, the score of the move is update too, and so on.

Others important queries are those made by the website when an user visit the opening book, stats section.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s