Page 1 of 1

Slowdown with Postgresql 9.2 - possible resolutions?

Posted: 01.12.2016, 23:19
by EnderX
I am working with the Zeos 6.1.5 components in Delphi 7, the CLX environment. For several years now, we've been using these components to update our main Postgresql database (Versions were Postgresql 8.1 - 8.4) on a daily basis. All databases are on Linux boxes, running either Red Hat Enterprise or CentOS 7.

With the previous, and current, databases, our daily update process only takes about twenty to thirty minutes. However, we have now set up a new server, intended to become our new main server within a few months, and the load times while we're testing it are... rather long. The new server is running Postgresql 9.2, and attempting to run the same daily update against that machine takes several hours to run - I believe the last official count was seven hours. The same holds true of the test server set up in the same location - both are running 9.2, and both are taking multiple hours to run. A simple collection of a mere 5000 insert statements to a single table took about seven and a half minutes, compared to about two minutes on an 8.4 machine.

I've tried everything I can think of to tune the Postgresql server for speed, with no effect. I also know that I have seen other messages online indicating that other people are using Zeos successfully with Postgresql 9.2. Given that if I directly run the statements in the update against the database, they complete in normal time frames, I believe the Zeos components we're using have to be what I check next. What options do I have for getting this slowness issue handled, and how should I go about them? Would it be enough simply to replace the library? Or would I be better off removing the zeos components we have installed and installing the newest package? Or any other options that I might not be able to think of?

Re: Slowdown with Postgresql 9.2 - possible resolutions?

Posted: 02.12.2016, 13:34
by marsupilami
Hello EnderX,

hmmm - please use the Zeos 7.2 version vrom SVN. It contains loads of bugfixes and will become the stable release during the next months. The main objective for Zeos 7.2 was to do speed improvements. If Zeos still ist not fast enough you might want to try profiling Zeos in your application to see where it spends most of its time and where we could try to improve it. I usually use GpProfile for that. As an alternative you might want to provide a test case (table definitions + small program) that I can use for doing tests.

With best regards,

Jan