Re: PostgreSQL Tweaking

From: "renier" <renier(at)vvconsult(dot)co(dot)za>
To: "'Alastair Turner'" <bell(at)ctrlf5(dot)co(dot)za>
Cc: <jnbpug(at)postgresql(dot)org>
Subject: Re: PostgreSQL Tweaking
Date: 2010-04-09 11:58:01
Message-ID: d3160250-0b1d-4ce7-b122-2d6809f25933@vvconsult.co.za
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: jnbpug

I would fully agree with your statement looking at the figures now. The results are from PGAdmin, just running the query.
What really puzzled me, is that it took 4 times longer running the query on the local pgadmin, than it takes to run it over the network. After running the explain analyse query, the speeds improved a lot. But I could not simulate those speeds again. They are back to > 200000ms, even after running Explain Analyse. It seems I cannot get a constant speed.

We did some tests on Ubuntu in a VM on the new server. Specs were 2 CPU (2.93x64 Xeon, 8gb ram) and the same query ran in 10sec on localhost.

2 reasons we don’t want to go with Ubuntu (or any linux for that matter)
1: We need to host another VM. We do not want to use too much system resources on VM's. We need to host a Web server in a VM and a SQL Server testing environment in a VM for a client. Therefore running it on the host is better.
2: Since we are quite a small company, we do not have any Linux specialists, and should problems arise, we need some technical expertise in troubleshooting.

The first result is the same on local or over the network
-- Executing query:
SELECT COUNT(componentid) from assetregistericon
Total query runtime: 210 ms.
1 row retrieved.

-- Executing query:
SELECT * from assetregistericon
Total query runtime: 213929 ms.
738050 rows retrieved.

-- Executing query:

BEGIN;
EXPLAIN ANALYZE SELECT * from assetregistericon;
;
ROLLBACK;
Query result with 2 rows will be returned.

-----Original Message-----
From: Alastair Turner [mailto:bell(at)ctrlf5(dot)co(dot)za]
Sent: 09 April 2010 01:13 PM
To: renier
Cc: jnbpug(at)postgresql(dot)org
Subject: Re: [jnbpug] PostgreSQL Tweaking

On Fri, Apr 9, 2010 at 12:54 PM, renier <renier(at)vvconsult(dot)co(dot)za> wrote:
.......
> Explain Analyse results:
> "Seq Scan on assetregistericon (cost=0.00..41106.50 rows=738050 width=428) (actual time=0.036..667.962 rows=738050 loops=1)"
> "Total runtime: 1209.181 ms"
>
............
> -- Executing query:
> select * from assetregistericon
> Total query runtime: 16345 ms.
> 738050 rows retrieved.
>
>
My first thought looking at the difference between the number given by
the analyse and the number given by (I presume) the client application
is that the transfer of the data between the server and the client
application is what's taking the time. Which would explain the near
constant total time for remote clients on different servers.

I presume there is some sort of id column on the table. What are the
execution times on the different servers for SELECT SUM(id) FROM
assetregistericon; ?

Responses

Browse jnbpug by date

  From Date Subject
Next Message Alastair Turner 2010-04-09 12:44:27 Re: PostgreSQL Tweaking
Previous Message Alastair Turner 2010-04-09 11:13:26 Re: PostgreSQL Tweaking