From: Merlin Moncure <mmoncure@gmail.com>
To: hansell baran <hansellb@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow transfer speeds with PostgreSQL
Date: Thu, 10 Aug 2006 16:51:16 -0400
Message-ID: <b42b73150608101351m168dd1e9mbe74b820d4e720b5@mail.gmail.com> (raw)
In-Reply-To: <20060803233939.75401.qmail@web30304.mail.mud.yahoo.com>
References: <20060803233939.75401.qmail@web30304.mail.mud.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mmoncure@gmail.com, hansellb@yahoo.com
Subject: Re: slow transfer speeds with PostgreSQL
In-Reply-To: <b42b73150608101351m168dd1e9mbe74b820d4e720b5@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox