Wednesday, February 22, 2006

using sql relay

By Justin Silverton

What is SQL relay?

SQL Relay is a persistent database connection pooling, proxying and load balancing system for Unix and Linux supporting ODBC, Oracle, MySQL, mSQL, PostgreSQL, Sybase, MS SQL Server, IBM DB2, Interbase, SQLite and MS Access (minimally) with APIs for C, C++, Perl, Perl-DBD, Python, Python-DB, Zope, PHP, Ruby, Ruby-DBD, Java and TCL, drop-in replacement libraries for MySQL and PostgreSQL clients, command line clients, a GUI configuration tool and extensive documentation. The APIs support advanced database operations such as bind variables, multi-row fetches, client-side result set caching and suspended transactions. It is ideal for speeding up database-driven web-based applications, accessing databases from unsupported platforms, migrating between databases, distributing access to replicated databases and throttling database access.

SQL Relay is ideal for:

  • speeding up database-driven web-based applications
  • enhancing the scalability of database-driven web-based applications
  • distributing access to replicated databases
  • throttling database access
  • accessing databases from unsupported platforms
  • migrating applications from one database to another

SQL Relay supports the following database backends:

  • Oracle
  • MySQL
  • mSQL
  • PostgreSQL
  • Sybase
  • MS SQL Server
  • IBM DB2
  • Interbase
  • Sybase
  • SQLite
  • ODBC
  • MS Access

The SQL Relay client API's support advanced database operations such as bind variables, multi-row fetches, client-side result set caching and suspended transactions. SQL Relay has native client API's for the following languages:

  • C
  • C++
  • Perl
  • Python
  • PHP
  • Ruby
  • Java
  • TCL
  • Zope
How does SQL Relay work?

SQL Relay's connection daemons log into and maintain sessions with databases. These connection daemons advertise themselves with a listener daemon which listens on an inet and/or unix port for client connections. When a client connects to the listener, if a connection daemon is available, the listener hands off the client to that connection. If no connection daemon is available, the client must wait in queue until one is. Once a client is handed off to a connection daemon, the client communicates to the database through the session maintained by that daemon.

How can SQL Relay improve the efficiency of my website?

Here are some examples of how SQL Relay can improve the efficiency of your web site.

Let's say you're running CGI's againt a transactional database such as PostgreSQL, MS SQL Server or Oracle. CGI's have to log into and out of the database each time they run. If you use SQL Relay to maintain persistent connections to the database and just log into and out of SQL Relay, you can reduce the amount of time wasted establishing database connections and handle more CGI requests per-second. This is both because the time-cost of connecting to SQL Relay is smaller than the time-cost of connecting to a transactional database, and because the SQL Relay client library is smaller than most database client libraries, resulting in a more lightweight CGI.

Let's say you're using Apache, PHP and Oracle and you determine by doing all sorts of analysis that you need to keep 30 Apache processes running to provide adequate response. Since most of your site isn't database-driven, on average, no more than 5 PHP's actually access the database simultaneously. Currently, you're using persistent connections to defeat the time-cost of logging into Oracle, but you have to maintain 30 connections (1 per web server process) which takes up a lot of memory on both the web server and database server and you really only need 5 connections. By using SQL Relay you can reduce the number of Oracle connections to the 5 that you need, continue to run 30 Apache processes and reclaim the wasted memory on both machines.

Many websites run a combination of PHP's and Perl modules. Perl modules can use Apache::DBI and PHP's have a persistent database connection system, but a PHP cannot use an Apache::DBI connection and a Perl module cannot use a PHP persistent connection. Thus in order to make sure that there are enough database connections for each platform, many more web-server processes have to be run, perhaps twice as many. If the PHP's and Perl modules used SQL Relay instead, they could share databse connections and reduce the number of web-server processes and database connections.

SQL Relay makes it easy to distribute load over replicated servers. A common scaling solution when using MySQL or PostgreSQL in a read-only web environment is to run several web servers with a dedicated database server for each web server or group of web servers and update all the databases simultaneously at scheduled intervals. This usually works pretty well, but sometimes database or web servers get runs of heavy load while others are idle. In other cases, an uneven number of machines is required. For example, your application may need 3 web servers but only 2 database servers or vice-versa. People usually just by 3 of each, wasting money. Moreover, in most cases, the servers have to be equivalently powerful machines. You can't usually just add another cheap machine that you have lying around into the pool. SQL Relay can connect to multiple, replicated or clustered database servers, providing web-based applications access to whichever server isn't busy. SQL Relay can also be configured to maintain more connections to more powerful machines and fewer connections to less powerful machines, enabling unevenly matched machines to be used in the same database pool. Collectively, these features allow you to save money by using only the exact number of servers that you need and by enabling you to use spare hardware in your database pools.

Where can I get it?

SQL relay can be downloaded Here


  • Didn't work for me together with zope, because the pool didn't create new connections hanging in a dead lock and thus the zope threads hung too.
    Found threads pointing in the same direction, but no solutions.

    By Anonymous Anonymous, at 2:09 AM  

Post a Comment

Links to this post:

Create a Link

<< Home