Re: slow transfer speeds with PostgreSQL - Mailing list pgsql-performance
From | Merlin Moncure |
---|---|
Subject | Re: slow transfer speeds with PostgreSQL |
Date | |
Msg-id | b42b73150608101351m168dd1e9mbe74b820d4e720b5@mail.gmail.com Whole thread Raw |
In response to | slow transfer speeds with PostgreSQL (hansell baran <hansellb@yahoo.com>) |
List | pgsql-performance |
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
pgsql-performance by date: