In PHP 5.4.1 i am using native PostgreSQL driver to connect to the SQL server. Even if i try the PDO way it is the same. Only one exception here. If i use pg_pconnect (persistent) it is very fast as i expected.

My Connection from PHP to PostgreSQL is slow. Also, some queries are slow too.

local all postgres trust
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
host all postgres 192.168.1.0/24 trust
host all all 127.0.0.0/24 trust

I tried enable/disable autovacuum, disable logging etc. Not worked. M system has 8 CPU with 3GB memory and i have 1 TB dedicated space for this SQL server.

@MarcusAdams yes ity is InnoDB. But when i tested this i also test the MyISAM and its almost identical.
– flower58May 2 '12 at 21:34

If it is fast with some connectors and not others the first two things I would check is whether the slow connectors connect and disconnect for each request. That could account for the difference. The other question is whether they use TCP_NODELAY. By default, if you write something to a TCP buffer, it waits a little while to send it just in case you might want to send more. That's good for streaming big files, but bad for the back and forth of database protocols.
– kgrittnMay 2 '12 at 21:44

3 Answers
3

Anecdotally MySQL has been to be be faster at establishing connections. In environments like PHP where connections are being setup and torn down so fast, as opposed to longer running connections in an application environment such as Rails or the JVM.

In this case it might make sense to use a connection pool for Postgres. Two come to mind:

I have 12 GB RAM and only 3GB of it is dedicated to VM. VM is not too big it is small CentOS VPS. But it is only for testing. I have a dedicated server with i3 processor and 4GB RAM.
– flower58May 3 '12 at 12:41

see how much performance you get if you disable virtual memory if the localhost thing doesn't do it for you. you didn't say whether you using pgsql locally or across a network. if it's locally only, then use the localhost settings I have listed above. if it's remote, then use your IP settings you have listed, but know that if you do, you need to use a STATIC IP on your NIC. you can't use DHCP.
– Jim MichaelsMay 11 '12 at 5:53

if you are simply connected to a router and this is local traffic, then this should not be a problem, set your NIC to something like 192.168.1.5 or something OUT of DHCP range listed on the setup web page of the router. then you need to bind pgsql to that address. getting the /32 or whatever numbers right is not my area of expertise. I leave that to the pgsql installer. when you disable VM in [windows-logo-flag-key]-[pause-break] Advanced,Performance,settings,advanced,virtualmemory,change, tweak the settings, and click SET and OK a bunch of times, reboot.
– Jim MichaelsMay 11 '12 at 5:56