Server Cluster

[Dragonfire]

The amount of faf players increase, but atm we have only one poor server, doing all the hard work.
Upgrade server is maybe a temporary fix, but not a long time solution + expensive for Zep ...

I want to discuess possibilities, it is interesring for me to talk about such things

My first idea:
1. Reduce the server load - opti the client ...
e.g. Local Player cache ... Save Player in users cache folder

2. IRC Cluster: Atm we have one IRC Server, maybe we can add more servers and form a cluster ... (Player can host irc servers)
2b. If we have more IRC servers we can abuse them, e.g. Create a channel "status" and use this irc channel as alternatice to json messages.

3. P2P solution: Ports are almost open, so why not change to a P2P system?
It is hard work, but it lower the server load very much.
(Maybe a second level cache, a server runs on some users machine, is a good alternative for a full p2p implementation)
The only problem: we have one instance that validate the stats ...

[Ze_PilOt]

That will unlikely happens, too much work.

On the other end, there are a lot of room for optimizing the database.
I'm nowhere a mySQL expert, and it's really the culprit in the current system.

[Dragonfire]

That will unlikely happens, too much work.

On the other end, there are a lot of room for optimizing the database.
I'm nowhere a mySQL expert, and it's really the culprit in the current system.

The problem is, that the backend is not open source ...
I love databases but I have no professional experiences with them ... but I am able to optimize queries and some other stuff ...
If you really want to optimize, mostly a database restructure is required ...

The biggest problem:
Optimization need time, and you must test and validate each setting/step ...


For the start:
What mysql storage engine do you use?
What are the cache sizes?
What is the connections limit?
Did you create indezies? http://dev.mysql.com/doc/refman/5.1/en/ ... dexes.html
(SQL command "EXPLAIN" and "ANALYZE" are very useful for that)

More infos:
http://www.mysqlperformanceblog.com/

PS.: If each message in _clientwindow.py is a mysql query there is a huge space for optimization

PSS.: How did you figure out, that the mysql server is the bottleneck?

[Aurion]

Is everything being done with MySQL only? And how much is being cached (most of the stuff you see just being displayed in the client can be cached ofc). I don't know how everything is being done at the backend, but using document based databases (like MongoDB) could be an interesting optimization in some cases. Most of this stuff is for readable data and I don't know if that is the main problem.

[Dragonfire]

Is everything being done with Myhorizontal SQL only? And how much is being cached (most of the stuff you see just being displayed in the client can be cached ofc). I don't know how everything is being done at the backend, but using document based databases (like MongoDB) could be an interesting optimization in some cases. Most of this stuff is for readable data and I don't know if that is the main problem.

I think MySQL is has the same performance like MongoDB ...
MongoDB has only a better horizontal scalability ...

PS.: MongoDB have a different use case, then MySQL

PSS.: You can also cluster MySQL Server (MySql Replication)... but I have no experience with that ...

###
btt:
http://www.khankennels.com/presentation ... tuning.pdf
http://www.mysqlperformanceblog.com/fil ... zation.pdf

[Dragonfire]

Today: 14:25 GMT +1
Users: ~240

again server problems ...
Forum not available

sites with less sql requests load faster ...
so it looks like a mysql problem ...

[Aurion]

Is everything being done with Myhorizontal SQL only? And how much is being cached (most of the stuff you see just being displayed in the client can be cached ofc). I don't know how everything is being done at the backend, but using document based databases (like MongoDB) could be an interesting optimization in some cases. Most of this stuff is for readable data and I don't know if that is the main problem.

I think MySQL is has the same performance like MongoDB ...
MongoDB has only a better horizontal scalability ...

PS.: MongoDB have a different use case, then MySQL

PSS.: You can also cluster MySQL Server (MySql Replication)... but I have no experience with that ...

###
btt:
http://www.khankennels.com/presentation ... tuning.pdf
http://www.mysqlperformanceblog.com/fil ... zation.pdf

MongoDB is not a replacement for MySQL, it complements it. And actually, it is faster with readable data (because there are no relations, so queries that are simple are of equal speed, yes), but you need a relational database when your data integrity is important.

But when it's still possible to optimize MySQL, that is preferable of course. After that caching is quite important when the user base gets quite big (and most of the times quite easy to do). With static stuff, you can even use something like Varnish (just the website, but that obviously isn't the highest load for the database at the moment). When load is high after that you should look at solutions like MongoDB (and indeed, scaling horizontally).

[Dragonfire]

MongoDB is not a replacement for MySQL, it complements it. And actually, it is faster with readable data (because there are no relations, so queries that are simple are of equal speed, yes), but you need a relational database when your data integrity is important.

But when it's still possible to optimize MySQL, that is preferable of course. After that caching is quite important when the user base gets quite big (and most of the times quite easy to do). With static stuff, you can even use something like Varnish (just the website, but that obviously isn't the highest load for the database at the moment). When load is high after that you should look at solutions like MongoDB (and indeed, scaling horizontally).

Have you any test results of MongoDB?
I said ... Mongo is for a different use case ...
I did not know the db structure of faf ...

@Varnish:
Looks like a L2 cache for me,
mabye good for the forum,
but faf.lobby used json messages of tcp (or?)

FAF caches already some things:
C:\ProgramData\FAForever\cache
Maybe add more caching,
like I said ... players stats

Informations that are not cacheable:
new games/game updates

I watched the traffic (messages) for 100mins
14:20 - 16:00:

775 player_info
17 mod_info
36 matchmaker_info
1260 game_info
28 tutorials_info


player_info
tutorials_info

is static ...

PS.: MongoDB looks very cool ...
but we can not optimize without the current queries ...

http://stackoverflow.com/questions/9702 ... ve#tab-top
http://www.moredevs.ro/mysql-vs-mongodb ... benchmark/

Mabye MongoDB for user_tables, vault (all static, not often changed data) and

MySQL for events, like new games (game results) ... btw. I am not sure if open/hosted games are tracked in a db ...

[Dragonfire]

@Aurion:
If you would implement a client side cache for player stats and more "static" data ...
Which persistence method would you recommend?
SqlLite? CVS? own folder system?

We cannot install MongoDB on each client

[Ze_PilOt]

I'm not sure to understand.

These data are already cached.