CPU suggestions for Interbase/Firebird SQL db server

[Jay_S]

I work for a small company (fewer than 10 employees). We want to upgrade our primary server hardware this year.

Current server is a HP ML330 G3 with a single Xeon @ 2.8 Ghz, 3GB ram, Raid5 consisting of three 15krpm Ultra320 SCSI drives. OS is Windows 2000 Server.

Our current CPU features hyperthreading, which is enabled. The motherboard is a dual socket board, so initially I was shopping for a second identical CPU (because they're as cheap as $15 on eBay). But after running some tests, I don't think it would benefit us.

Our ERP software uses Interbase version 5.6, which does not appear to be multi-threaded based on my tests. I contacted the developer of our ERP package, and they confirmed this: Interbase will NOT utilize multiple physical or logical CPUs. More recent revisions of our ERP software have switched to Firebird db server, which I've learned is not multi-thread aware either. This sounds terribly old-fashioned, since modern CPUs are trending toward higher core-count.

I'm a total noob when it comes to db testing - please forgive me. My test involved monitoring the interbase process in task manager on the Win 2000 server while a client PC searched the db. Individual searches cause the interbase process to peg right to 50% (100% of a logical processor). Prior to this morning, I suspected our network (100Mb/s switched) or our server's disk subsystem was our bottleneck. But by all appearances, we're severely CPU bound.

Unfortunately, our ERP vendor is not going to move away from Interbase/Firebird. So it seems we should be shopping for a CPU that offers the highest single-threaded performance we can find.

Does this sound reasonable? At the moment I'm looking at the following:

Dual core Xeon E3110 @ 3.0 GHz (core2duo based)
Quad core Xeon E5504 @ 2.0 Ghz (Nehalem based - it's only $20 more than the dual E5502)

Both would be in Dell servers (the PowerEdge T410 w/Xeon E5504 starting at $820 is hard to beat IMO).

How is Nehalem's single-threaded performance? What sort of benchmarks should I be searching for in online reviews that are similar to my application?

Thanks all-

 

[Slapo]

Our current CPU features hyperthreading, which is enabled. The motherboard is a dual socket board, so initially I was shopping for a second identical CPU (because they're as cheap as $15 on eBay). But after running some tests, I don't think it would benefit us.

Our ERP software uses Interbase version 5.6, which does not appear to be multi-threaded based on my tests. I contacted the developer of our ERP package, and they confirmed this: Interbase will NOT utilize multiple physical or logical CPUs. More recent revisions of our ERP software have switched to Firebird db server, which I've learned is not multi-thread aware either. This sounds terribly old-fashioned, since modern CPUs are trending toward higher core-count.

I'm a total noob when it comes to db testing - please forgive me. My test involved monitoring the interbase process in task manager on the Win 2000 server while a client PC searched the db. Individual searches cause the interbase process to peg right to 50% (100% of a logical processor). Prior to this morning, I suspected our network (100Mb/s switched) or our server's disk subsystem was our bottleneck. But by all appearances, we're severely CPU bound.

Unfortunately, our ERP vendor is not going to move away from Interbase/Firebird. So it seems we should be shopping for a CPU that offers the highest single-threaded performance we can find.

The newer versions of Firebird are capable of multi-threading, see here:
http://www.firebirdsql.org/guide/FBFactsheet.html (look for Server engine versions in the headings)

The fact sheet (first link) is for the old Firebird 1.5. The current stable version is 2.1.2 (at the time of writing this) and there's 2.5 nearing release candidate stage, which means it could reach stable this year. However, at least the Classic mode of Firebird should show utilisation of both the physical and virtual CPU.
To learn more about the modes, go here:
http://www.firebirdfaq.org/faq25/
and here:
http://www.sinatica.com/blog/en/ind...ird-superserver-classicserver-or-superclassic

Here's the roadmap for 2009:
http://www.firebirdsql.org/index.php?op=devel&sub=engine&id=roadmap_2009
Yes, the 2.5 release seems to have slipped considerably, but you can at least see some of the features planned.

Here's how you turn Superclassic on:
http://firebirdsql.org/devel/doc/rlsnotes/html/rlsnotes25.html#rnfb25-threading-superclassic

As for your benchmarking method, it would be better if you could monitor query execution times with Hyperthreading on and off and see whether there is any difference. Note that Hyperthreading doesn't always lead in an increase of performance with databases.

However, if the second CPU costs only $15 on eBay, I'd say get it - if nothing else, you'll be able to take some load off the first processor already present in your current machine. The /affinity switch could help you:
http://waynes-world-it.blogspot.com/2009/06/processor-affinity-on-windows-server.html
although I'm not sure it works on Windows 2000, you might want to check that.

It would be nice if you could post your further findings here.

 

[Jay_S]

Thanks for taking the time to write such an extensive reply. I had read about Firebird's newer versions and "classic" mode. Unfortunately, our software maintenance agreement will not cover db servers other than that which is supplied from our ERP vendor. So until our ERP vendor ships with a multi-thread optimized db server, our hands are tied.

As for your benchmarking method, it would be better if you could monitor query execution times with Hyperthreading on and off and see whether there is any difference. Note that Hyperthreading doesn't always lead in an increase of performance with databases.

OK. How can I do this? Again, I'm pretty new at this. Also, as this is a production server, I doubt Mgmnt will permit testing that requires rebooting.

 

[Slapo]

I don't know how you'd either monitor processes for Interbase 5.6 or log queries for it (I'm not sure you can actually monitor current queries with Firebird or Interbase, I've only worked with it a couple of times).
You can either try to find out on some database forums.

You can ask you ERP vendor whether they didn't set CPU affinity for Interbase, if you google "interbase 5.6 cpu affinity" without the quotes, you'll see it's possible even with IB 5.6.
Which version of Firebird do they use for the ERP?
If the upgrade to the ERP version that uses Firebird isn't expensive, you'd probably be better off getting it and the $15 CPU from eBay. I'd suggest you get that one anyway, $15 can't be that much of an expense, surely.

From monitoring tools for Firebird, I only know Sinática monitor, but their page won't load for me now (probably a DNS problem on my side). It's not free, but if it supports you Interbase version, it might be worth considering (or try to find an alternative to it).

Sorry if I can't help you more with this one.

 

[bearlevel]

You might also check with your ERP to see if they support virtualization as this would allow you to run a test server with any of thses setups you with. VMware will let you download the VSphere demo for 30 days to try it out. But you will need a spare box to test it on.

 

[extide]

How is Nehalem's single-threaded performance? What sort of benchmarks should I be searching for in online reviews that are similar to my application?

Clock for clock it's the fastest chip on the market. For your uses that 3.0Ghz C2D based one might be a slightly better bet, although if you ever care to upgrade that proc later then going with the Nehalem based one will offer more options in the future. I'd say probably go with the Quad core 2.0Ghz Nehalem based one, it will run circles around that P4 based Xeon you currently have.