Databases
StackOverflow2013
Postsdbo
POUBUNTU 11.04 + PHP + POSTGRESQL Enhance performance
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUBUNTU 11.04 + PHP + POSTGRESQL Enhance performance
    text
    copied!<p>I'm on this machine:</p> <p>intel core 2 duo e8400 @3GHZ 4GB ram ddr2</p> <p>php 5.3.6 pgsql 9.1</p> <p>I'm running a php script that takes like 5 minutes on a mac with similar specs. This php script, essentially, recreate a db importing some data into it.</p> <p>On this computer it runs in more than 20 minutes.</p> <p>The weird thing is the use of the CPU from both PHP &amp; POSTGRESQL</p> <pre><code> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 8408 postgres 20 0 2188m 44m 40m D 4 1.1 0:20.71 postgres 8407 gianps 20 0 380m 225m 6620 S 2 5.7 0:11.78 php top - 16:08:32 up 3:35, 3 users, load average: 1.26, 1.15, 0.80 Tasks: 187 total, 1 running, 185 sleeping, 0 stopped, 1 zombie Cpu(s): 4.8%us, 2.7%sy, 0.2%ni, 87.0%id, 5.1%wa, 0.0%hi, 0.2%si, 0.0%st Mem: 4056572k total, 2541972k used, 1514600k free, 117772k buffers Swap: 3905532k total, 0k used, 3905532k free, 902048k cached </code></pre> <p>I setup the php (both cli and apache) to use as much ram as they need (memory limit -1) and tuned postgres to use:</p> <p>shared_buffers = 2GB<br> effective_cache_size = 3072MB</p> <p>Any suggestion to let this script use more ram &amp; more cpu and run faster?</p> <p>thanks</p> <p>update: after some investigation i found that that setting synchronous commit (in this situation) makes my script 10x faster.</p> <pre><code>set synchronous_commit to off; </code></pre> <p>since is not safe to make this option a default, i just switch it to off when needed.. to understand what synchronous commit does <a href="http://www.postgresql.org/docs/9.0/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT" rel="nofollow">documentation</a></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload