running databases inside Vmware
This is just a warning note for people running databases inside Vmware.
The problem:
At my employers company we develop some business critical application for one of our customers. This customer decided to employ Vmware ESX host as a container for a testing farm for this application. One of servers in this farm is a PostgreSQL machine.
The Very Bad Effect that we noticed while debugging some nasty db-related problem is:
Inside a guest OS, time flows differently.
That is, one second can last 1/2 second or 3/4 second or 2 seconds - depending on the disk I/O. The higher disk I/O rates, the slower time flows.
Somehow, this has fatal impact on performance of PostgreSQL. Algorithms which rely on precise timing simply stop to work. This could be a matter of interesting analysis which I don't have time for right now.
I am not a Vmware expert, and probably this is just a matter of bad configuration.
Probably it appears only when you run many single-core guests inside one multi-core host, but anyway - be warned.
psql variables – quoting and more
psql, postgreSQL powerful command line interface, has it's variables system. I do use it sometimes but... how many single quotes should I put there?
I have puzzled over this so many times, that I decided to put it here as a memo.
Proper quoting of text variables
Variable declaration:
\set d1 '''2008-01-01 00:00:00.000'''
\set d2 '''2008-12-31 23:59:59.999'''
Variable usage:
SELECT something FROM somewhere
WHERE now() BETWEEN :d1 AND :d2;
To make this post a bit more useful, here are some other
examples of psql variables usage
A shortcut for frequently used SQL phrase:
\set cnt 'SELECT COUNT(*) FROM'
:cnt users;
Another one:
\set csn 'SELECT * FROM DBA.CLIENTS WHERE name LIKE'
\set csni 'SELECT * FROM DBA.CLIENTS WHERE name ILIKE'
:csni 'kowalski'\g all_kowalskis.txt
This one if for pretty-ascii-report-printout lovers:
\set hr **************************************
\set inform '\\echo :hr \\echo :msg \\echo :hr'
Usage of the above:
\set msg 'starting mass update\nat ' `date`
:inform
**************************************
starting mass update
at Fri Dec 19 21:15:49 CET 2008
**************************************
Nice, eh?
You can put such "macros" into your .psqlrc file to save some keystrokes.
You can even create your very own "library" of psql macros and include it in your scripts with \i.
Have fun!
Note: these are not variables in SQL stored procedures sense. This is just plain text substitution.
A bit similar to Host Variables (note :colon usage), but not so powerful.
