{"id":1423,"date":"2018-01-19T19:22:49","date_gmt":"2018-01-19T19:22:49","guid":{"rendered":"http:\/\/goofy-trucks.flywheelsites.com\/optimizing-postgresql-page-2\/"},"modified":"2018-01-19T19:24:46","modified_gmt":"2018-01-19T19:24:46","slug":"optimizing-postgresql-page-2","status":"publish","type":"post","link":"https:\/\/phpbuilder.com\/optimizing-postgresql-page-2\/","title":{"rendered":"Optimizing Postgresql Page 2"},"content":{"rendered":"
\n
\n
By PHP Builder Staff<\/div>\n
on August 21, 2001<\/div>\n<\/p><\/div>\n
\n

Optimizing<\/h2>\n
\nOne of the first things I noticed after turning on the CGI program, was
\nthat although queries were returned almost as fast as from the previous
\nMySQL based system, the load on the server was much higher — in fact
\nalmost 90-percent! Then I started to go down into the
\nnitty-gritty of things. I had optimized MySQL before by greatly
\nincreasing cache and buffer sizes and by throwing more ram towards the
\nproblem.<\/div>\n
\nThe single biggest thing that you have to do before running Postgresql,
\nis to provide enough shared buffer space. Let me repeat: provide
\nenough buffer space! Let’s say you have about 512MB of ram on a
\ndedicated database server, then you need to turn over about 75-percent
\nof it to this shared buffer. Postgresql does best when it can load most
\nor — even better — all of a table into its shared memory space. In
\nour case, since our database was fairly small, I decided to allocate
\n128MB of RAM towards the shared buffer space. <\/div>\n
\nThe file \/var\/lib\/pgsql\/data\/postgresql.conf contains settings for the
\ndatabase server. Postgresql uses system shared memory as a buffer. On a
\nLinux system, you can see how much shared memory was allocated by your
\nsystem by running the command: <\/div>\n
\ncat \/proc\/sys\/kernel\/shmmax<\/div>\n
\nAnd to view shared memory use on the system:<\/div>\n
ipcs<\/div>\n
\nThe result will be in bytes. By default RedHat 7.1 allocates 32MB of
\nshared memory, hardly enough for postgresql. I increased this limit to
\n128MB by doing the command:<\/div>\n
echo 128000000 > \/proc\/sys\/kernel\/shmmax<\/div>\n
\nBe aware that once you reboot the server, this setting will disappear.
\nYou need to place this line in your postgresql startup file, or by
\nediting the \/etc\/sysctl.conf file for a more permanent setting.<\/div>\n
\nThen in our postgresql.conf I set shared_buffers to 15200. Because
\nPostgresql uses 8K segments, I made a calculation of 128000\/8192 plus a
\n512K overhead. I also set our sort_mem to 32168 (32Megs for a sort memory
\narea). Since connection pooling was in effect, I set max_connections to 64.
\nAnd fsync was also set to false.<\/div>\n
\n
\nshared_buffers = 15200\nsort_mem = 32168\nmax_connections=64\nfsync=false\n<\/pre>\n<\/div>\n
\nYou can read the manual to tweak other settings, but I never had the
\nneed to do so. Note that if you set shared_buffers to more than what
\nyour shared memory limit is, postgresql will refuse to start. This
\nconfused us for a while, since no logging was taking place. You can
\ntweak the startup file in \/etc\/init.d for the postmaster to write its
\noutput to a log file.
\nChange the fragment from:<\/div>\n
\n
\n\/postmaster start > \/dev\/null 2>\n<\/pre>\n<\/div>\n
\nto<\/div>\n
\n
\n\/postmaster start > \/var\/lib\/pgsql.log 2>\n<\/pre>\n<\/div>\n
\n(or wherever you want to store the log.)<\/i><\/div>\n<\/div>\n
<\/p>\n
\n
\u00ab Previous Page<\/a><\/div>\n
1<\/a> <\/div>\n
| <\/div>\n
2<\/div>\n
| <\/div>\n
3<\/a> <\/div>\n
Next Page \u00bb<\/a><\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"
I decided to take a shot at installing and using this database. For most of our work I use MySQL and will continue to do so, because of its ease of<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1423","post","type-post","status-publish","format-standard","hentry","category-tutorials"],"_links":{"self":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1423","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/comments?post=1423"}],"version-history":[{"count":1,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1423\/revisions"}],"predecessor-version":[{"id":3282,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1423\/revisions\/3282"}],"wp:attachment":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/media?parent=1423"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/categories?post=1423"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/tags?post=1423"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}