Обсуждение: slow transfer speeds with PostgreSQL

Hi. I'm new at using PostgreSQL.
Where I work, all databases were built with MS Access. The Access files are hosted by computers with Windows 2000 and Windows

XP. A new server is on its way and only Open Source Software is going to be installed. The OS is going to be SUSE Linux 10.1

and we are making comparisons between MySQL, PostgreSQL and MS Access. We installed MySQL and PostgreSQL on both SUSE and

Windows XP (MySQL & PostgreSQL DO NOT run at the same time)(There is one HDD for Windows and one for Linux)
The "Test Server" in which we install the DBMS has the following characteristics:

CPU speed = 1.3 GHz
RAM = 512 MB
HDD = 40 GB

The biggest table has 544371 rows(tuples?) with 55 rows. All fields are float8. Only 1 is varchar(255) and 1 timestamp.
We query the MS Access databases through Visual Basic Programs and ODBC Drivers. We made a Visual Basic program that uses ADO

to connect to ALL three DBMS using ODBC drivers.

When we run the following query "SELECT * FROM big_table", we get the following resutls:

MS Access
- Execution time ~ 51 seconds (Depending on the client machine, it can go as low as 20 seconds)
- Network Utilization ~ 80 Mbps (According to Windows Task Manager)

MySQL 5.0 (under Windows)
- Execution time ~ 630 seconds
- Network Utilization ~ 8 Mbps

PostgreSQL 8.1 (under Windows)
- Execution time ~ 290 seconds)
- Network Utilization ~ 13 Mbps


MS Access (under Linux. MS Access files are in the Linux computer which has the SAMBA server running. The client computer has

a mapped network drive that conects to the Linux files.)
- Execution time ~ 55 seconds (Depending on the client machine, it can go as low as 20 seconds)
- Network Utilization ~ 76 Mbps (According to Windows Task Manager)

MySQL 5.0(under Linux)
- Execution time ~ 440 seconds
- Network Utilization ~ 11 Mbps

PostgreSQL 8.1(under Linux)
- Execution time ~ 180 seconds)
- Network Utilization ~ 18 Mbps


Very different results are obtained if a the query "SELECT * from big_table ORDER BY "some_column"". In this scenario

PostgreSQL is faster than MS Access or MySQL by more than 100 seconds.

We have run many other queries (not complex, at most nesting of 5 inner joins) and MS Access is always faster. We have seen

by looking at the network activity in the Windows Task Manager that the main problem is the transfer speed. We also have

noticed that MS Access quickly downloads the file that has the necesary information and works on it locally on the client

computer. The queries, obviously, run faster if the client computer has more resources (CPU speed, RAM, etc.). The fact that

the client computer does not use any resource to execute the query, only to receive the results, is one big plus for

PostgreSQL (we think). We need,however, to improve the performance of the queries that return a lot of rows because those are

the most used queries.

We searched the postgresql archives, mailing lists, etc. and have tried changing the parameters of the PostgreSQL server(both

on Linux and Windows)(We also tried with the default parameters) and changing the parameters of the ODBC driver as suggested.

We still get aproximately the same results. We have even changed some TCP/IP parameters(only in Windows) but no improvement.

To get to the point: Is this problem with the transfer rates a PostgreSQL server/PostgresQL ODBC driver limitation?
Is there a way to increase the transfer rates?

Thank you very much for any help received!

Hansell E. Baran Altuve

P.S.: I apologize for the lenght of this post and for any missing information you might need. I will gladly hand out all the

necessary information to receive any help with my problem. Thanks again!

---

Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

On 8/3/06, hansell baran <hansellb@yahoo.com> wrote:
> Hi. I'm new at using PostgreSQL.
> Where I work, all databases were built with MS Access. The Access files are
> hosted by computers with Windows 2000 and Windows
>
> XP. A new server is on its way and only Open Source Software is going to be
> installed. The OS is going to be SUSE Linux 10.1
>
> and we are making comparisons between MySQL, PostgreSQL and MS Access. We
> installed MySQL and PostgreSQL on both SUSE and
>
> Windows XP (MySQL & PostgreSQL DO NOT run at the same time)(There is one HDD
> for Windows and one for Linux)
> The "Test Server" in which we install the DBMS has the following
> characteristics:
>
> CPU speed = 1.3 GHz
> RAM = 512 MB
> HDD = 40 GB
>
> The biggest table has 544371 rows(tuples?) with 55 rows. All fields are
> float8. Only 1 is varchar(255) and 1 timestamp.
> We query the MS Access databases through Visual Basic Programs and ODBC
> Drivers. We made a Visual Basic program that uses ADO
>
> to connect to ALL three DBMS using ODBC drivers.
>
> When we run the following query "SELECT * FROM big_table", we get the
> following resutls:
>
> MS Access
> - Execution time ~ 51 seconds (Depending on the client machine, it can go as
> low as 20 seconds)
> - Network Utilization ~ 80 Mbps (According to Windows Task Manager)
>
> MySQL 5.0 (under Windows)
> - Execution time ~ 630 seconds
> - Network Utilization ~ 8 Mbps
>
> PostgreSQL 8.1 (under Windows)
> - Execution time ~ 290 seconds)
> - Network Utilization ~ 13 Mbps
>
>
> MS Access (under Linux. MS Access files are in the Linux computer which has
> the SAMBA server running. The client computer has
>
> a mapped network drive that conects to the Linux files.)
> - Execution time ~ 55 seconds (Depending on the client machine, it can go as
> low as 20 seconds)
> - Network Utilization ~ 76 Mbps (According to Windows Task Manager)
>
> MySQL 5.0(under Linux)
> - Execution time ~ 440 seconds
> - Network Utilization ~ 11 Mbps
>
> PostgreSQL 8.1(under Linux)
> - Execution time ~ 180 seconds)
> - Network Utilization ~ 18 Mbps
>
>
> Very different results are obtained if a the query "SELECT * from big_table
> ORDER BY "some_column"". In this scenario

you have to be careful comparing access to mysql/postgresql in this
way because the architecture is different...these results are a bit
misleading.  access can do some optimization tricks on very simple
queries, especially select * from bigtable becuase the result does not
have to be fully materialized and returned to the client.

> PostgreSQL is faster than MS Access or MySQL by more than 100 seconds.
>
> We have run many other queries (not complex, at most nesting of 5 inner
> joins) and MS Access is always faster. We have seen

i find this really hard to believe. is your postgresql database
properly indexed and did you run analyze?  do the standard
-performance thing, run the query in with explain analyze:

explain anaylze 5_table_join_query

and post the results to this list.

merlin

Hi, Hansell,

hansell baran wrote:

> When we run the following query "SELECT * FROM big_table", we get the
> following resutls:

Just for Curiosity:

Could you try to "COPY big_table TO stdout" from psql[.exe]? (and
possibly redirect the psql output to /dev/null or so?)

> Is there a way to increase the transfer rates?

Which file system do you use?

Could you try to "VACUUM FULL" the tables?

I assume that, for complex queries, you have all the appropriate indices
etc.

Also, I have to admit, that for single-client scenarios and simple,
mostly read-only queries, PostgreSQL tends to be slower than Access and
MySQL.

However, this changes as soon as you have multiple concurrent writing
clients. You should take this into account when benchmarking your
servers (by modelling the appropriate benchmarks), and when deciding
which database to use (by trying to estimate future usage patterns).


HTH,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org