2012Christoph Berg's Bloghttps://www.df7cb.de/blog/2012.htmlChristoph Berg's Blogikiwiki2012-12-07T09:31:58Zapt.postgresql.orghttps://www.df7cb.de/blog/2012/apt.postgresql.org.html2012-12-07T09:31:58Z2012-12-07T09:31:58Z
<p>So we finally made it, and sent out an
<a href="http://archives.postgresql.org/pgsql-announce/2012-12/msg00008.php">official announcement</a> for
<a href="https://wiki.postgresql.org/wiki/Apt">apt.postgresql.org</a>.</p>
<p>This new repository hosts packages for all PostgreSQL server versions (at the
moment 8.3, 8.4, 9.0, 9.1, 9.2) for several Debian/Ubuntu distributions
(squeeze, wheezy, sid, precise) on two architectures (amd64, i386). Now add
packages for extension modules on top of all these, and you get a really large
amount of binaries from a small number of sources. Right now there's 1670 .deb
files and 148 .dsc files, but the .dsc count includes variants that only differ
in the version number per distribution (we attach .pgdg60+1 for squeeze
packages, .pgdg70+1 for wheezy and so on), so the real number of different
sources is rather something like 81, with 38 distinct source package names.</p>
<p><a href="http://tapoueh.org/">Dimitri Fontaine</a>,
<a href="http://www.hagander.net/">Magnus Hagander</a>, and I have been working
on this since I first presented the idea at PGconf.EU 2011 in Amsterdam. We now
have a Jenkins server building all the packages, an archive server with the
master repository, and a feed that syncs the repository to the postgresql.org
FTP (well, mostly http) server.</p>
<p>If you were previously using pgapt.debian.net, that's the same archive as on
apt.postgresql.org (one rsync away). Please update your sources.list to point
to apt.postgresql.org, I'll shut down the archive at that location at the end
of January.</p>
<p>Here's the <a href="https://wiki.postgresql.org/wiki/Apt#Quickstart">Quickstart
instructions from the Wiki page</a>:</p>
<p>Import the repository key from http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc:</p>
<pre>
wget -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
</pre>
<p>Edit /etc/apt/sources.list.d/pgdg.list. The distributions are called codename-pgdg. In the example, replace squeeze with the actual distribution you are using:</p>
<pre>
deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main
</pre>
<p>Configure apt's package pinning to prefer the PGDG packages over the Debian ones in /etc/apt/preferences.d/pgdg.pref:</p>
<pre>
Package: *
Pin: release o=apt.postgresql.org
Pin-Priority: 500
</pre>
<p>Note: this will replace all your Debian/Ubuntu packages with available packages from the PGDG repository. If you do not want this, skip this step.</p>
<p>Update the package lists, and install the pgdg-keyring package to automatically get repository key updates:</p>
<pre>
apt-get update
apt-get install pgdg-keyring
</pre>
pgbouncer running on the same hardwarehttps://www.df7cb.de/blog/2012/pgbouncer_running_on_the_same_hardware.html2012-11-30T07:52:40Z2012-11-30T07:52:40Z
<p>We have a PostgreSQL server with 16 cores that was apparently running well
below its capacity: load something between 3.0 and 4.0, around 200 active
database connections, almost all always being <IDLE>. However, when the
tps count reached 7k transactions per second, things started to throttle, and
pgbouncer (running on the database server) started listing up to half of the
client connections to be in cl_waiting state. Load was still low, but
application performance was bad.</p>
<p><img src="http://www.df7cb.de/blog/2012/renice_pgbouncer.png"></p>
<p>The culprit turned out to be the kernel scheduler, fairly distributing CPU time
among all running processes. There's one single pgbouncer process, but hundreds
of postgres processes.</p>
<p>A simple <em>renice</em> of the pgbouncer process did the trick and gave us another
extra 2k tps.</p>
renice pgbouncer.pnghttps://www.df7cb.de/blog/2012/renice_pgbouncer.png2012-11-30T07:52:40Z2012-11-30T07:52:40ZShared Memory and Swappinghttps://www.df7cb.de/blog/2012/Shared_Memory_and_Swapping.html2012-11-29T09:23:10Z2012-11-26T20:17:36Z
<p>We have this PostgreSQL server with plenty of RAM that is still using some of
its swap over the day (up to 600MB). Then suddenly everything is swapped in
again.</p>
<p><img src="http://www.df7cb.de/blog/2012/dbsrv_swap.png"></p>
<p>It turned out the reason is there are two clusters running, and the second one
isn't used as heavily as the first one. Disk I/O activity of the first cluster
slowly evicts pages from the second shared buffers cache to swap, and then the
daily pg_dump run reads them back every evening.</p>
<p>I was not aware of an easy way to get numbers for "amount of SysV shared memory
swapped to disk", but some googling led to shmctl(2):</p>
<pre>
#define _GNU_SOURCE 1
#include <sys/ipc.h>
#include <sys/shm.h>
#include <stdio.h>
#include <unistd.h>
int main ()
{
struct shm_info info;
int max;
long PAGE_SIZE = sysconf(_SC_PAGESIZE);
max = shmctl(0, SHM_INFO, (struct shmid_ds *) &info);
printf ("max: %d\nshm_tot: %ld\nshm_rss: %ld\nshm_swp: %ld\n",
max,
info.shm_tot * PAGE_SIZE,
info.shm_rss * PAGE_SIZE,
info.shm_swp * PAGE_SIZE);
return 0;
}
</pre>
<p>The output looks like this:</p>
<pre>
max: 13
shm_tot: 13232308224
shm_rss: 12626661376
shm_swp: 601616384
</pre>
<p><b>Update:</b> Mark points out that <em>ipcs -mu</em> shows the same information. Thanks for the hint!</p>
<pre>
# ipcs -mu
------ Shared Memory Status --------
segments allocated 2
pages allocated 3230544
pages resident 3177975
pages swapped 51585
Swap performance: 0 attempts 0 successes
</pre>
dbsrv swap.pnghttps://www.df7cb.de/blog/2012/dbsrv_swap.png2012-11-26T20:17:36Z2012-11-26T20:17:36Zgrep -r foobarhttps://www.df7cb.de/blog/2012/grep_-r_foobar.html2012-11-22T15:25:49Z2012-11-22T15:25:49Z
<p>In Wheezy's grep version [1], you can omit the "." in</p>
<pre>
$ grep -r foobar .
</pre>
<p>and just write</p>
<pre>
$ grep -r foobar
</pre>
<p>[1] actually since 2.11</p>
mkfs.ext3 -b 1024https://www.df7cb.de/blog/2012/mkfs.ext3_-b_1024.html2012-10-01T13:21:08Z2012-10-01T13:21:08Z
<p>If you think you are smart and create an ext filesystem with 1024 bytes
blocksize because there will be zillions of very small files, and then run into
ENOSPC errors while there's both space and inodes left, you will probably see
<a href="http://linux.derkeiler.com/Mailing-Lists/Kernel/2008-05/msg08251.html">ext3_dx_add_entry: Directory index full!</a>
in the kernel log.</p>
<p>Turns out that there's a limit of approximately 300,000 files per directory
with 1k blocks, after which some hash tables are full. Recreate the filesystem
with 2k blocks and the limit will be MUCH higher.</p>
PostgreSQL in Debian Hackathonhttps://www.df7cb.de/blog/2012/PostgreSQL_in_Debian_Hackathon.html2012-08-27T12:21:08Z2012-08-27T09:53:53Z
<p>Almost a year has passed since my talk at pgconf.eu 2011 in Amsterdam on
<a href="http://www.postgresql.eu/events/schedule/pgconfeu2011/session/166-connecting-the-debian-and-postgresql-worlds/">Connecting the Debian and PostgreSQL worlds</a>,
and unfortunately little has happened on that front, mostly due to my limited
spare time between family and job.
<a href="http://pgapt.debian.net/">pgapt.debian.net</a> is up and running, but
got few updates and is lagging behind on PostgreSQL releases.</p>
<p>Luckily, we got the project moving. <a href="http://tapoueh.org/blog/index.html">Dimitri Fontaine</a>
and <a href="http://blog.hagander.net/">Magnus Hagander</a> suggested to do a
face-to-face meeting, so we got together at my house for two days last week and
discussed ideas, repository layouts, build scripts, and whatnot to get all of
us aligned for pushing the project ahead. My
<a href="http://www.credativ.de/">employer</a> sponsored my time off work for
that. We almost finished moving the repository to postgresql.org
infrastructure, barring some questions of how to hook the repository into the
existing mirror infrastructure; this should get resolved this week.</p>
<p>The build server running Jenkins is still located on my laptop, but moving this
to a proper host will also happen really soon now. We are using
<a href="http://michael-prokop.at/blog/">Mika Prokop</a>'s
<a href="http://jenkins-debian-glue.org/">jenkins-debian-glue</a> scripts for
driving the package build from Jenkins. The big plus point about Jenkins is
that it makes executing jobs on different distributions and architectures in
parallel much easier than a bunch of homemade shell scripts could get us with
reasonable effort.</p>
<p>Here's a list of random points we discussed:</p>
<ul>
<li>We decided to go for "pgdg" in version numbers and distribution names, i.e.
packages will have version numbers like 9.1.5-1.pgdg+1, with distributions
wheezy-pgdg, squeeze-pgdg, and so on.</li>
<li>There will be Debian-testing-style distributions called like
wheezy-pgdg-testing that packages go into for some time before they get
promoted to the "live" distributions.</li>
<li>PostgreSQL versions out of support (8.2 and below) will not be removed from
the repository, but will be moved to distributions called like
wheezy-pgdg-deprecated. People will still be able to use them, but the
naming should make it clear that they should really be upgrading.</li>
<li>We have a slightly modified (compared to Debian unstable) postgresql-common
package that sets the "supported-versions" to all versions supported by the
PostgreSQL project. That will make the postgresql-server-dev-all package
pull in build-dependencies for all server versions, and make extension
module packages compile for all of them automatically. (Provided they are
using pg_buildext.)</li>
<li>There's no Ubuntu support in there yet, but that's mostly only a matter of
adding more cowbuilder chroots to the build jobs. TBD soon.</li>
</ul>
<p>We really aim at using unmodified packages from Debian as much as possible, and
in fact this project doesn't mean to replace Debian's PostgreSQL packaging
work, but to extend it beyond the number of server versions (and Debian and
Ubuntu versions covered) supported. The people behind the Debian and Ubuntu
packages, and this repository are mostly the same, so we will claim that "our"
packages will be the same quality as the "original" ones. Big thanks go to
<a href="http://www.piware.de/">Martin Pitt</a> for maintaining the
postgresql-common testsuite that really covers every aspect of running
PostgreSQL servers on Debian/Ubuntu systems.</p>
<p>Stay tuned for updates! :)</p>
Machine Check Exceptionhttps://www.df7cb.de/blog/2012/Machine_Check_Exception.html2012-07-19T12:10:05Z2012-07-19T12:10:05Z
<p>Today's wtf:</p>
<pre>
Wed Jul 18 20:25:01 CEST 2012 MCA: Generic CACHE Generic Generic Error
</pre>
Andreashttps://www.df7cb.de/blog/2012/Andreas.html2012-05-07T21:56:12Z2012-05-07T21:56:12Z
<p><img src="http://www.df7cb.de/pictures/2012-05-Andreas/IMG_6414.JPG" width="800" height="600"></p>
<p>May 7th, 2012</p>
Cool Unix Features: /usr/bin/timehttps://www.df7cb.de/blog/2012/usr_bin_time.html2012-03-21T08:45:42Z2012-03-20T14:32:23Z
<p>Ever wondered how much memory a program needed? Install the "time" package:</p>
<pre>
$ /usr/bin/time ls
[...]
0.00user 0.00system 0:00.00elapsed 0%CPU (0avgtext+0avgdata 4000maxresident)k
0inputs+0outputs (0major+311minor)pagefaults 0swaps
</pre>
<p>Unfortunately the "time" bash built-in makes it necessary to use the full path.</p>
<p>Thanks to youam for the tip.</p>
<p><em>Update:</em> aba notes that calling \time works as well. Thanks!</p>
How not to edit fileshttps://www.df7cb.de/blog/2012/How_not_to_edit_files.html2012-01-30T10:17:44Z2012-01-30T10:17:44Z
<p>When packaging new backport versions, I diff the old Debian package with the
old backport package to extract the changes I did there, and then apply this
patch to the new version. There is always a reject in debian/changelog because
the topmost bpo entry won't apply cleanly to the new changelog. To fix this, I
invoke "vi debian/changelog*" and manually copy the rejected hunk.</p>
<p>Unfortunately, I regularly end up copying it from debian/changelog.rej
(buffer 3) into debian/changelog.orig (buffer 2) instead of debian/changelog
(buffer 1). Here's the fix in my .vimrc:</p>
<pre>
" Prevent accidental editing of patch .orig files
autocmd BufRead *.orig set readonly
</pre>