1

I have 4 python scripts each running in their own virtual machine. The scripts are accessing a central postgresql database. All scripts are reading from the same table but posting to different tables in the same database. The database is located on its own machine. The DB machine has 8 CPUs.

Running the command top on the database server shows:

  PID USER      PR  NI  VIRT  RES  SHR S  %CPU %MEM    TIME+  COMMAND
12609 postgres  20   0 3700m 563m 559m R  98.4  3.5   1602:57 postgres
12603 postgres  20   0 3700m 927m 923m R  97.8  5.8   1603:17 postgres
20619 postgres  20   0 3700m 455m 451m R  96.4  2.8 237:36.05 postgres
12616 postgres  20   0 3700m 1.0g 1.0g R  94.8  6.4   1547:26 postgres

The CPU usage is extremely high. I have noted that my scripts have become slower all of a sudden hence my interrogation of resources using top. Question is, am i better off letting each script run alone i.e. with db exclusivity or the high CPU usage seen does not necessary slow down the database and I should be looking elsewhere to optimize my speeds?

Tuned Postgresq

I have tried tunning postgres as directed here but I don't see any increase in speeds or even higher memory utilization by postgres. I have set the shared buffers to 3.5GB as my machine has 16GB and edited the shared memory kannel to 4GB

$sysctl -p
kernel.shmmax = 4294967296
kernel.shmall = 1073741824
vm.overcommit_memory = 1
Improve this question
8
  • 1
    This could be a sign of bad database design: do you have proper PK/FK constraints and indexes, and are your typical queries able to use these? Try to obtain a few typical queries and the corresponding query plans. (and maybe add them to your question) BTW: the RSS is extremely high; did set work_mem extremely high?
    – wildplasser
    Nov 24, 2013 at 12:08
  • 2
    You really need to figure out what that CPU time is being used for. Use the auto_explain and pg_stat_statements / pg_stat_plans modules, use explain analyze, do query logging, etc. Profile with perf top -az if you're comfortable reading stacks.
    – Craig Ringer
    Nov 24, 2013 at 12:39
  • BTW: it could also mean that the entire working set of your queries is located in the shared buffers, and the queries's work consists only of scanning and combining these pieces of memory.
    – wildplasser
    Nov 24, 2013 at 15:32
  • @CraigRinger I wish I new where to start running those queries... but I'll Google and revert. Got pointers in the mean time?
    – lukik
    Nov 24, 2013 at 18:41
  • @wildplasser what do you mean by `the RSS is extremely high?
    – lukik
    Nov 24, 2013 at 18:43

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.