--- In firebird-support@yahoogroups.com, Milan Babuskov <milanb@...> wrote:

> 50-150 queries in which timeframe? You can have 150 connections sitting
> there idle with 386 ;)

Its for serving PHP apps, so I was assuming something like 50-150 queries per minute, so probably something like 3 or so per second, I guess. It may not be that heavy but at peak load I suspect it will be something like that.

> How big are the databases? What's the size of your indexes? If all index
> pages can fit into RAM you can have great performance.

Most of the databases have around 100 or so tables, and data sizes are typically between 1-500,000 rows per table. Some tables have as many as 30 or so fields, although I've managed to normalize as much of the design to keep this down. There is no use of BLOBs in there - but some text fields can be large (ie. the odd table has 30,000 character sized VARCHAR fields in there for textual content).

> > a. 50 concurrent queries
> > b. 100 concurrent queries
> > c. 150 concurrent queries
>
> This mostly depends on the server model you choose (CS,SS,SC) and the
> values you set up firebird.conf for cache.

Normally I'm running SuperServer by default here, but if there is a more optimized way by using a different model, I can change for that.

> I can give you an example. I run a server with a single 20GB database,
> answering to about 95 queries per second. On average that's 30
> concurrent users running about 3 queries per page load. One of those 3
> queries is a select from 18-million-rows table that uses composite index
> on 2 fields. About 85% of queries are SELECTs and *all* queries use
> indexes. There are some batch jobs that cannot use indexes effectively,
> so they run off-hours. I have some statistics: in the past 14 days I had
> only 2 queries out of 117.297.240 that took longer than 10 seconds to
> complete.

That's really impressive! If I could get that performance, I'd be really happy. It sounds like I definitely could get something like that.

> On this same server I run Apache+PHP and nginx in front of Apache.
> Apache usually peaks at about 40 processes at a single moment, each
> taking about 25MB of RAM. So, at peak load I "waste" about 1GB of RAM to
> web server. I'm writing this because the server has 2GB of RAM, so
> memory available to database server goes from 1 to 1.6GB during the day.

I am lucky in that the Web servers & PHP processing will be done on separate boxes. This box is purely as a central database for ALL web servers, rather than the previous model I was using which was to put separate Firebird servers on the same boxes as the web servers. That's where I was getting hit with lesser performance.

> The system is AMD Athlon64 X2 DualCore 5600. Not really state of the
> art. I would pick 4 or 8-core Intel i7 if I had bigger needs (and
> budget). There are two 500GB Samsung HD501LJ SATA disks in RAID-1.
>
> On this hardware, the system load is around 1.30 on average, and peaks
> at about 2.50 during a busy day.

Is that running Linux? I suspect it is based on what you have described. Out of interest, how much memory do you have installed in that server? I'm thinking 16GB on this box, particularly if you feel that loading indexes into memory will increase performance.

Myles