At 05:49 PM 4/03/2008, you wrote:

>Hi,
>
>currently I run a webserver with Windows 2000, where I have the
>following three parts
>1) Firebird 1.5 as database
>2) Apache 1.3 / PHP 4.4 for the website. To speed up the site a create
>a hash on the url's and use that as cache file name. So during one
>week the number of cache files goes up over 100'000 files
>3) Delphi 2007 application for some background tasks and as http
>service to provide graphics.
>
>Because of size of the database and number of requests the website is
>rather slow now and I think of adding a second machine. Before I do
>this, my questions are:
>- What parts (1 - 3) should I put together?

2 and 3. Let the database server have its own host machine, otherwise all that I/O from your web app is competing with the database server.

>Should I use the new machine as a database server only?

Yes

> Or should I separate the Delphi 2007 application?

No. The Delphi app is the "marshall" between the database and the web service. The most important thing of all - even if you can't get a separate machine for a database server - is to design a very lean, efficient database application module that plays nice with both Apache and the database.

>- Should I consider to setup the new machine with Linux?

For a database server, yes, it's certainly worthy of consideration. Don't even *think* of running your Delphi app out of a wine server, though. Wine is too many extra layers.

>Any other hints, also how to find out, what makes the system slow and
>how it could be improved? I already checked all SQL statements and I
>think, there is none that causes a problem with a wrong plan or
>missing indexes.

With Delphi apps, you *always* have to watch out to write apps that manage transactions extremely well and also avoid unnecessary round trips to the server. Avoid cute desktop Delphi hacks like CommitRetaining (carefully examine what Autocommit gives you with PHP and stay away from it if you can't separate it from CommitRetaining!) If your users are updating tables, *don't* use dataset methods. Don't write apps that grab a dataset and loop through it doing DML. Use Read-only, read-committed transactions wherever possible and use straight, prepared DML statements to perform writes.

Don't allow mad things like letting users construct their own queries. Don't do mad things yourself, like constructing run-time statements if you can possibly do the job with pre-packaged parameterised statements that can be prepared once and executed many times.

Consider using Classic for the database server if you have plenty of resources on it, so that you can make the database server work at its best capacity. If you're doing a lot of sorting, say "Yes" to that extra stick of RAM, because Classic can use it. Configure it so it is used effectively. Also think about putting in a dedicated fast hard-drive which you configure just for sort space and virtual memory caching.

./heLen