[kwlug-disc] I need Painfully simple load balancing.

[Fernando Duran]

> From: Chamunks Arkturus <chamunks at gmail.com>
>To: KWLug Discussion <kwlug-disc at kwlug.org> 
>Sent: Thursday, December 20, 2012 6:19:33 AM
>Subject: [kwlug-disc] I need Painfully simple load balancing.
> 
>
>I need a load balancer that can react to queries from a sqlite database.  It basically needs to check a sql database for server capacity levels and if the capacity gets close to being reached send players to another server.
>
>
>Then it also needs to react to people wanting to directly move from target point to target point.
>
>
>is there something that could do this?
>



Hello Chamunks,

Not sure what your architecture is or what you want to solve. What (client) protocols are involved? what's "service capacity" for a database server?

For example for a web application requests come in HTTP to a load balancer and it redirects them to one of several web servers. For HTTP client requests you can use nginx which is easy to set up and can do round-robin with weighs (it doesn't poll the status of back-end servers though) or you can use HAProxy.

For a database spread over several servers and a database connection/protocol we have clusters (ex: MySQL cluster) that can be load-balanced (there's no sqlite cluster that I'm aware of).

Sqlite is a single-file database, so it doesn't work well for concurrent writes (file locks) while for many reads it works pretty well.  It's very possible that you don't need more than one sqlite database server or load balancing. Reading from a file is fast and if the sqlite db file is not big compared to RAM size it will be cached to RAM by Linux automatically (or you can copy the sqlite db file to a RAMdisk you create).

The server utilization for CPU and RAM will be low for sqlite3 in general. I/O (the usual bottleneck in databases) could be an issue if you have many writes, but if this is the case then you probably want to use another RDBMS like PostgreSQL or Mysql. So I'm not sure under what case you'd need to load-balance sqlite.

As a thought experiment I guess you can have several copies of the sqlite db file in different servers, mount them with remote filesystem (see https://gist.github.com/1870471 for example) to a central server and then write your own script to round-robin access to them or similar. If you have a database spread over different servers now you also have a consistency problem (if you make changes to one db file you need to replicate that to the other files), if changes (writes again) are not frequent perhaps you could use inotify to keep the copies of the slqite file in sync.


Cheers,

---------------------
Fernando Duran
http://www.fduran.com