How to create pg_reorg v.1.1.7 rpm for Postgres 9.1

Here is an update to my recent post on creating pg_reorg 1.1.7 rpm. That previous post created rpm working with Postgres 9.0.  Now, I need it working with Postgres 9.1. Also, the problem with the rpm created using the old approach was that the rpm name did not reflect what   postgres version it was created for.

The rpm creation method probably might be more elegant. I just needed something quick to work. So, RPM police, please don’t be too strict to this post.

pg_reorg used to be available as an rpm up to the version 1.1.5, but for the latest version 1.1.7, only source code is available. However, the old 1.1.5 spec files for postgresql 8.4 and 9.0 are still included in the 1.1.7 tarball. With a little tweaking, it’s not a problem to build a 1.1.7 for Postgres 9.1 rpm if you like to install it this way.  Here is how:

• Download the source package to a machine where you have your target postgresql and linux versions. You will also need at least gcc and make installed on this machine.   I will do it for postgresql 9.1 on RedHat Linux 5.5

mkdir pg_reorg
cd pg_reorg

• Untar the source package

tar -xvzf pg_reorg-1.1.7.tar.gz
rm pg_reorg-1.1.7.tar.gz
cd pg_reorg/SPECS/ 

• Create a new spec file in the SPECS folder inside of the source package.

cp pg_reorg90.spec pg_reorg91.spec

vi pg_reorg91.spec

The necessary changes are:

• Make sure that the line “%define _pgdir”  reads as %define _pgdir   /usr/pgsql-9.1
• Make sure that the line “Name:    %{sname}” reads as Name:    %{sname}91
• Replace the line “Version: 1.1.5” with “Version: 1.1.7“
• Make sure that the line “%define pg_sharedir” reads as “%define pg_sharedir %{_pgdir}/share“

• Now, repackage everything back

cd ../../

mv pg_reorg pg_reorg-1.1.7
tar -cvzf pg_reorg-1.1.7.tar.gz pg_reorg-1.1.7

• Execute the following actions as root

sudo su

• Copy the source package to  /usr/src/redhat/SOURCES

cp pg_reorg-1.1.7.tar.gz /usr/src/redhat/SOURCES
cp pg_reorg-1.1.7/SPECS/pg_reorg9*.spec /usr/src/redhat/SPECS

• Build rpm

rpmbuild -bb /usr/src/redhat/SPECS/pg_reorg91.spec

• Now you have two rpm’s built in  /usr/src/redhat/RPMS/x86_64/

ls -lrt  /usr/src/redhat/RPMS/x86_64/
-rw-r--r-- 1 root root 38745 Sep 18 20:57 pg_reorg91-1.1.7-1.x86_64.rpm
-rw-r--r-- 1 root root 96683 Sep 18 20:57 pg_reorg91-debuginfo-1.1.7-1.x86_64.rpm

The one you need is pg_reorg91-1.1.7-1.x86_64.rpm

For the installation, follow the document pg_reorg.html in the   doc folder of your source tarball.

Her is the diff between original pg_reorg90.spec and the new pg_reorg91.spec:

5c5
< %define _pgdir   /usr/pgsql-9.1
—
> %define _pgdir   /usr/pgsql-9.0
11,12c11,12
< Name:         %{sname}91
< Version:      1.1.7
—
> Name:         %{sname}
> Version:      1.1.5
20,21c20,21
< BuildRequires:        postgresql91-devel, postgresql91
< Requires:     postgresql91, postgresql91-libs
—
> BuildRequires:        postgresql90-devel, postgresql90
> Requires:     postgresql90, postgresql90-libs
47c47
< %define pg_sharedir %{_pgdir}/share
—
> %define pg_sharedir

How to create pg_reorg v.1.1.7 rpm ( Additional material to my PG West 2011 presentation)

I’m making a presentation “pg_reorg – A Tool You Can’t Live Without” on PGWest 2011 conference in San Jose.
Here is some additional material on pg_reorg for the people who would like to try the tool.

pg_reorg used to be available as an rpm up to the version 1.1.5, but for the latest version 1.1.7, only source code is available. However, the old 1.1.5 spec files for postgresql 8.4 and 9.0 are still included in the 1.1.7 tarball. With a little tweaking, it’s not a problem to build a rpm if you like to install it this way.  Here is how:

• Download the source package to a machine where you have your target postgresql and linux versions. You will also need at least gcc and make installed on this machine.   I will do it for postgresql 9.0 on RedHat Linux 5.5

mkdir pg_reorg
cd pg_reorg
wget http://pgfoundry.org/frs/download.php/3104/pg_reorg-1.1.7.tar.gz

• Untar the source package

tar -xvzf pg_reorg-1.1.7.tar.gz
rm pg_reorg-1.1.7.tar.gz
cd pg_reorg/SPECS/ 

• Edit the spec file in the SPECS folder inside of the source package.

vi pg_reorg90.spec

The necessary changes are:

• Replace the line “Version: 1.1.5” with “Version: 1.1.7“
• Make sure that the line “%define pg_sharedir” reads as “%define pg_sharedir %{_pgdir}/share“

• Now, repackage everything back

cd ../../
mv pg_reorg pg_reorg-1.1.7
tar -cvzf pg_reorg-1.1.7.tar.gz pg_reorg-1.1.7

• Execute the following actions as root

sudo su

• Copy the source package to  /usr/src/redhat/SOURCES

cp pg_reorg-1.1.7.tar.gz /usr/src/redhat/SOURCES
cp pg_reorg-1.1.7/SPECS/pg_reorg90.spec /usr/src/redhat/SPECS

• Build rpm

rpmbuild -bb /usr/src/redhat/SPECS/pg_reorg90.spec

• Now you have two rpm’s built in  /usr/src/redhat/RPMS/x86_64/

ls -lrt  /usr/src/redhat/RPMS/x86_64/
-rw-r--r-- 1 root root 38745 Sep 18 20:57 pg_reorg-1.1.7-1.x86_64.rpm
-rw-r--r-- 1 root root 96683 Sep 18 20:57 pg_reorg-debuginfo-1.1.7-1.x86_64.rpm

The one you need is pg_reorg-1.1.7-1.x86_64.rpm

For the installation, follow the document pg_reorg.html in the   doc folder of your source tarball.

Dealing with low free swap space issue

Recently, I’ve received a nagios alert “SWAP CRITICAL – 1% free (0 MB out of 1983 MB)”
on one of my large batch processing hosts. The batch process finished, and nothing was happening on the host,
but the swap space did not release by itself, and nagios was not happy.

To verify if your swap space is  close to be completely used
execute command:

free


total used free shared buffers cached
Mem: 66074056 65496768 577288 0 3407652 33605860
-/+ buffers/cache: 28483256 37590800
Swap: 2031608 2031284 324

Look for the value in the “free” column of the “Swap:” line. The value is in kilobytes. You don’t want it to become too low, a few megabytes for example. The example above definitely shows a problem.

Here is what to do if it is low.

The commands below I’ve executed on Redhat 5.5, but the same process should work on other flavors of Linux, too.
First you need to make sure that there is some available memory to free-up the swap.
I saw situations when the swap space was completely used and a lot of memory was consumed by a large cache produced by creating a few huge files. I did not need these files in cache anymore, so I’ve cleaned the cache running commands below as root:

sync
echo 3 > /proc/sys/vm/drop_caches

Then I ran a script to empty the swap. I did not verify if I could do it without cleaning the cache as described above. If you by some reason don’t want to clean your cache and you have enough free memory, you may empty the swap without emptying the cache first.

The commands for cleaning the swap I’ve found here:

#Create and execute the script swap2ram.sh :
err=”not enough RAM to write swap back, nothing done”
mem=`free|grep Mem:|awk ‘{print $4}’`
swap=`free|grep Swap:|awk ‘{print $3}’`
test $mem -lt $swap && echo -e $err && exit 1
/sbin/swapoff -a && /sbin/swapon -a &&
exit 0

The script takes a while to complete and it releases the swap space gradually. It i snot unusual to have the script working for more than an hour.
You won’t see the result till the script completes. If you cancel the script, you will see how much swap space you’ve managed to release so far.
If you can’t wait to see some progress and swap space emptied, the script may be cancelled and relaunched again.

How to reduce the probability of this situation in the future?
Change sappiness
You may tell your host to be less enthusiastic about swapping. The default value for the swapiness parameter is 60, which should be reasonable on a desktop. Many experts recommend to reduce it to at most 10 on servers. In my case after the change, the host prefers to allocate less space to cache to avoid swapping as much as it can.
Here is how to do it.

Change the current swappiness value:
/sbin/sysctl vm.swappiness=10

To make the change persistent across reboots, edit the /etc/sysctl.conf file:

vi /etc/sysctl.conf

Add the line:
vm.swappiness=10

Mount HDFS as a filesystem on Centos 5.6

Image via CrunchBase

The libhdfs library from project FUSE allows to mount HDFS as a filesystem.

This is how I did it using Cloudera cdh3 hadoop distro on Centos 5.6 EC2 host.
It’s so easy when it works

First, install the necessary packages. I’ve installed them on a computer, which has a hadoop installation, but is not a part of  the hadoop cluster. This is my Cloudera SCM server and Hive workstation.

yum install hadoop-0.20-fuse  hadoop-0.20-libhdfs


Create mount point and mount the new filesystem in the debug mode first:

mkdir /mnt/hdfs
hadoop-fuse-dfs dfs://:8020 /mnt/hdfs -d

Please note, the port number is 8020 here, not 50070. I did not get it from the very beginning and wasted some time on this issue.

Test the new filesystem from a separate terminal session: create a directory, copy a file there. If you see errors in your debug session, look at the troubleshooting section below. If everything works well, add the mount point to the /etc/fstab:

hadoop-fuse-dfs#dfs://namenode host>:8020 /mnt/hdfs fuse allow_other,protected=/system:/tmp:/user 2 0

Fair warning: The read/write operations would be slower that using hadoop shell. Also, not all the commands are supported on the fuse filesystem. chmod for example is not supported. I was not able to rsync to my fuse filesystem, but scp works well.

Troubleshooting.

If you are getting error like this:

/usr/lib/hadoop-0.20/bin/fuse_dfs: error while loading shared libraries: libjvm.so: wrong ELF class: ELFCLASS64

You probably have both 32 and 64 bit versions of hadoop-0.20-fuse  package installed. To double-check, run:

yum list hadoop-0.20-fuse

In my case, I’ve got:

Installed Packages
hadoop-0.20-fuse.i386         0.20.2+923.97-1       installed
hadoop-0.20-fuse.x86_64   0.20.2+923.97-1        installed
fuse.x86_64                             2.7.4-8.el5                  installed
fuse-libs.x86_64                     2.7.4-8.el5                   installed
fuse-libs.i386                           2.7.4-8.el5                  installed

Remove the unnecessary packages:
yum erase fuse-libs.i386 hadoop-0.20-fuse.i386

pg_reorg – A Tool You Can’t Live Without

Below is the working material for my presentation about pg_reorg at the San Francisco Postgres Meetup on August 9, 2011 and PgWest 2011.

Here are the actual slides with fewer words, but more pictures.

What is pg_reorg?

pg_reorg can re-organize tables online on a production database without long-lasting locks, so that you can retrieve or update rows in reorganized tables.

The project’s home page:
http://pgfoundry.org/projects/reorg/
The latest version is  1.1.7, August 7, 2011

Project Admins:
Masahiko Sakamoto
Toru SHIMOGAKI
Takahiro Itagaki

Available packages:
pg_reorg-1.1.5-1.pg84.rhel5.i386.rpm
pg_reorg-1.1.5-1.pg84.rhel5.x86_64.rpm
pg_reorg-1.1.5-1.pg90.rhel5.i386.rpm
pg_reorg-1.1.5-1.pg90.rhel5.x86_64.rpm
pg_reorg-1.1.5.tar.gz
pg_reorg-1.1.7.tar.gz

You can choose one of the following methods to reorganize.

• Order by an index’ filed(s) (online CLUSTER), or any set of specified columns
• Packing tuples without ordering (online VACUUM FULL ), works faster than with ordering

How pg_reorg Works

In the beginning:

• tries to acquire an ACCESS EXCLUSIVE lock nowait in a loop till it succeeds.

• creates a DML tracking trigger on the reorganized table,
• creates a new table using CTAS (the  SELECT may include ORDER BY clause if sorting option is chosen),

After the new table is created:

• replays all the accumulated DML on the new table,
• acquires an exclusive lock on the table,
• applies the latest DML,
• swaps the old and new tables
• drops the old table.
• analyzes the newly built table.

What it is good for:

Remove Database Bloat

The table has 62M records, size 53 GB

Identify bloat
#/usr/lib/nagios/plugins/contrib/check_postgres_bloat -H localhost -u postgres –db=feed

POSTGRES_BLOAT CRITICAL: table public.product_feed_data rows:60614560 pages:6622583 shouldbe:5356924 (1.2X) wasted size:10368278528 (9888 MB) — 9.8GB+ (20%) of disk space is wasted

Eliminate the bloat

$ pg_reorg -t product_feed_data -n -T 86400 -d feed

The results:
#/usr/lib/nagios/plugins/contrib/check_postgres_bloat -H localhost -u postgres –db=feed

POSTGRES_BLOAT WARNING: table public.product_feed_data rows:62703028 pages:5891010 should be:5535050 (1.1X) wasted size:2916024320 (2781 MB)  – reduced from 9GB+ to 2.7GB

A copy of the full table before and after reorg took 14min 48 sec and 12 min 17 sec – 17% improvement.

Reduce IO and Improve Performance

For a random single row access, the rows’ order in a table is unimportant. However, for queries on an indexed value that has multiple matching rows, pre-ordered physical data allocation would drastically reduce required disk IO and hence improve performance. Once the index identifies the table page for the first row that matches, all other rows that match are probably already on the same cached page.

Consider a query returning 3.2M records using indexed access:
select * from product_feed_data where merchant_id=’XYZ’;

How many pages will need to be read from a disk to fetch all the records?

Before reorg: 275482, execution time 6,677.249 ms
After reorg sorted on merchant_id:    153280, execution time 2251.190 ms

If we run a lot of queries based on merchant_id, the total required disk IO may drastically reduce, and We may serve more traffic on the same hardware.

Manage database shards and massive deletes

Deleting a large part of a huge table may create a lot of problems. At the very least, it would create a lot of bloat and make b-tree indexes unbalanced. pg_reorg, with a little tweaking helps solve this problem.  The CTAS definitions used to create the new tables are saved in a reorg.tables view.
Tweak the definition as you need, run a reorg, and the newly reorged table will have only a subset of the original data.

select create_table from reorg.tables where relname=’product_feed_data’::regclass;

create_table                                          —————————————————————————–
CREATE TABLE reorg.table_21845 WITH (oids=false) TABLESPACE pg_default AS SELECT * FROM ONLY product_feed_data

Change it to:
CREATE TABLE reorg.table_21845 WITH (oids=false) TABLESPACE pg_default AS SELECT * FROM ONLY product_feed_data where merchant_id not in ( ‘ABC’,’PQR’,’XYZ’)

pg_reorg may be a life saver if partitions on a specific shard become too busy.
It allows manual table re-sharding with very little downtime. To split a partition A into sub-partitions A1 and A2 do:

• Replicate the table A to a different host/shard
• Rename the original partition into A1 and the new one A2, switch the application to use the two new partitions. This is the only required downtime. Should be very short, depending on how good you are managing partitions in the application.
• Tweak the reorg.tables view on the original and new shards
• Run pg_reorg on both partitions and remove stale A2 data in A1 and A1 data in A2.

Compare with VACUUM FULL and CLUSTER

• pg_reorg’s functionality overlaps with both VACUUM FULL and CLUSTER and works in a similar fashion. However, the latter commands require an ACCESS EXCLUSIVE lock on a table for the duration of a command, which makes then unfit for many production databases.
• pg_reorg only requires short-living locks in the beginning and the end of the processing. It acquires the locks opportunistically mostly without serious disruption of the service.
• pg_reorg offers additional functionality on top of VACUUM FULL and CLUSTER overlap, especially with some tweaking.

According to the tool’s creators, pg_reorg may be faster than clusterdb.

Execution speed performance comparison with clusterdb on 16.5M records table

http://reorg.projects.postgresql.org/index.html

Pitfalls and things to watch for

• Like VACUUM FULL and CLUSTER, pg_reorg requires extra disk space, since it writes a new copy of the table and indexes and doesn’t release the old copy until the operation is complete.

• Only superusers can use the utility.
• Target table must have PRIMARY KEY.

• pg_reorg acquires two short-living ACCESS EXCLUSIVE locks in the beginning and the end of the reorg. It may create some problems on databases with very long running transactions, though I still could execute reorg on a database concurrently with 12+ hours long transactions.

• In the end, if pg_reorg cannot acquire a lock in a specified time, it will cancel the long running transactions preventing it from getting a lock. Choose the waiting time carefully.
• Sometimes, it’s better to kill a pg_reorg session which waited for a lock for too long, and start again.
• As anything, test pg_reorg for yourself before using it in production.
• Works nice with trigger based replication. Did not test with WAL shipping or streaming replication, but should work, too.

The changes in the latest version 1.1.7:
- Bugfix: VIEWs and FUNCTIONs could be corrupted that used a reorganized table which has a dropped column.
- Supports PostgreSQL 9.1 and 9.2dev. (but EXTENSION is not yet)

How I’ve got 5x more throughput on cheaper storage (compare DELL MD1200+H800 vs DELL MD3000+MD1000 or MD3220)

Recently, I’ve spent some time building new hosts for PowerReviews’ databases and benchmarking some Dell’s DAS arrays. These databases execute a lot of batch operations and need good sequential I/O throughput.  I’ve setup a cheaper development and QA host in the office, tested  it, and was pleasantly surprised with the I/O performance.  Then I’ve got a host and more expensive storage for the production database configured by my vendor in their data center.  Imagine my frustration when my vendor’s production configuration provided 5x  less throughput than my development setup. Here is the story.

My development host in the office uses a dumb shelf with 24 spindles and external I/O controller installed in the host:

Dell R710 2CPU X5650 @ 2.67GHz 12 cores 96GB DDR3, RHEL5.5 , kernel  2.6.18-194.26.1.el5
Storage: Dell MD1220 + single H800 controller with 1GB cache, uniform (redundant path) connection,
             Write Policy: Write Through, Read Policy: No-Read-Ahead
RAID10, 512K chunk size, 22x300GB 2.5" SAS 10K RPM, xfs filesystem

Here is the best information I could get on the production host and storage at my vendor’s DC:

Dell R710 2CPU L5520 @ 2.27GHz 8 cores 144GB DDR3 RAM, RHEL 5.5, Kernel 2.6.18-164.30.1.el5
Storage: Dell MD3000 + MD1000 shelf, 2 redundant in-storage controllers ( some kind of LSI, I could not find much information on it),
            cache size is 512MB per controller ( 1GB total)
Two RAID10, 512K chunk size, each RAID volume 14x146GB 3.5" SAS 15K, striped on lvm into a single volume, 28 drives total,  xfs filesystem

The two configurations above are not quite identical, but close. If anything,  my vendor’s setup has many more and faster spindles. It should be a winner. I’ve double-checked that the all the kernel parameters are set identically on both hosts, and then ran the same bonnie++ v1.96 test on both configurations: The file size is the double of the available RAM, concurrency 8,  fsync is not called after each write, 3 executions per test. The exact bonnie parameters are “-s 192g -n 0 -f -c 8″ for the development host and “-s 288g -n 0 -f -c 8″ for the production host. The extracts from the test results are in the Table 1 and the Table 2.   I was less interested in the random I/O, also both setups demonstrated satisfactory random performance, so I do not provide the results here for simplicity.

Table 1: Test results for MD1220, 22 10K RPM drives

put_block	cpu %	rewrite	cpu %	get_block	cpu %	put_block latency	rewrite latency	get_block latency
1034979	73	476585	44	1142492	62	64172us	229ms	241ms
1028566	72	472739	44	1125068	62	44732us	237ms	235ms
1028705	71	472782	44	1111932	62	49168us	229ms	228ms

Table 2: Test Result for MD3000+MD1000, 28 15K RPM drives

put_block	cpu %	rewrite	cpu %	get_block	cpu %	put_block latency	rewrite latency	get_block latency
179597	30	131568	17	543166	32	78897ms	1983ms	1157ms
155649	26	133041	18	534784	33	85449ms	1734ms	1157ms
187603	31	129889	16	573729	34	1473ms	1899ms	1158ms

So, my cheaper MD1220 configuration provides much better throughput than more expensive MD3000+MD1000. I get 1GB/s and 1.1GB/s of Write/Read throughput om MD1220 versus about  179MB/s and 543 MB/s Write / Read throughput on MD3000+MD1000. The latency on the MD1220 is also way better than on MD3000+MD1000. The only metric that looks better for the MD3000+MD1000 pair is CPU usage, but this is because the storage  bottleneck prevented using all the CPU power for the test on that host. What is the reason for such a huge performance disparity?  At first, I thought it should be simple. The guys who setup the production configuration in the datacenter installed LVM and did not pay attention to the striping alignment, or they misconfigured striping on the RAID10 arrays, or perhaps LVM gives me such a terrible performance, or perhaps the hardware is faulty.   After I asked the vendor’s sysadmins to double check everything, and after they replaced all the pieces of hardware one by one because they thought it was faulty, we still had the same performance. I got rid of the LVM completely and tested against a physical LUN, nothing helped. I saw only a few percent of difference, where I needed 400%. The vendor finally gave me some explanation of the performance disparity. My setup with a single H800 I/O controller is not redundant, and the MD3000 has 2 embedded I/O controllers, multi-path redundant access, and write cache replication. I love redundancy, but not at the price of 5x performance degradation. I’ve asked the vendor to remove the I/O controller cache replication, and finally we saw almost 150% improvement in I/O throughput ( Table 3).

Table 3: Test results for MD3000+MD1000, 28 15K RPM drives, cache mirroring disabled

put_block	cpu %	rewrite	cpu %	get_block	cpu %	put_block latency	rewrite latency	get_block latency
447892	59	190942	25	509686	29	354ms	1086ms	370ms
442786	59	198513	25	499326	29	384ms	1211ms	290ms
447470	58	198156	25	507137	29	379ms	1654ms	341ms

The proposed production configuration now gave me 447MB/s and 507MB/s of Write/Read throughput. It’s better than before, but now I don’t have I/O controller redundancy, and still have only about half of performance of my cheaper development setup. I’ve suspected I/O controllers in MD3000 were the root cause of the issue well before all the testing and tweaking was completed.  Eventually, my vendor gives up and replaces MD3000+MD1000 with newer MD3220 with 24 15K RPM drives. The idea is that this is a newer device, that should have better controllers. It has 6GB/s HBAs instead of 3GB/s at MD3000. In fact, my MD1220 dumb shelf is intended to work as an extension to the latest and greatest MD3220. However, the new MD3220 produced about the same results as the original MD3000+MD1000. In the end, my boss and I just decided to go with the known configuration and press the vendor to purchase and install for us MD1220 with in-host H800 controller, which they consider non-standard setup. I felt a little nervous when I first tested the new MD1220 + H800 at my vendors data-center. What if I just had some mistake in my tests and the new MD1220 won’t show the same great results as my original setup? But miracles, good or bad, don’t happened. The know configuration gave me known results, comparable with my original setup. This was finally a relief.

Conclusion:

1. MD3220 and older MD3000 are not good for sequential loads.

2. MD1220 with H800 controller provides great results.

3. Never assume that if you got a latest array with plenty of spindles, you’ll get proper performance. Always benchmark it yourself.

Yahoo mail does not like Chrome anymore?

I was using Yahoo mail in Chrome browser on my Ubuntu host for quite a while.
The “new” Yahoo mail always gave me a warning right after the login, something along the lines that my browser is not supported.
However, it allowed me to launch their Ajax interface anyway, and it was happily working.
Tonight, after the login, I see this message below. As you can see, there is no option to “launch anyway,” so browsers like Chrome or Opera are explicitly prohibited now with the Ajax interface.
Only classic interface is available for these browsers. I hope that this is a temporary glitch. I was already contemplating transitioning from Yahoo as my main e-mail provider. If they are serious with such sudden exclusion of my browser, I would finally start my move to Gmail.

Sorry, the all-new att.net Mail does not support your browser.
You can either download a compatible browser or proceed to att.net Mail Classic.


If you want to use the all-new att.net Mail...
You can download any of the supported browsers for free by clicking the links below:

	
Internet Explorer 6.0 or higher (for Windows)
	
Firefox 2.0.0.1 or higher (for Mac and Windows)
	
Safari 3.0.4 or higher (for Mac)


To proceed to att.net Mail Classic...
Simply follow the link below. If you sign in later with a supported browser, you will automatically be taken to the all-new att.net Mail.
Proceed directly to att.net Mail Classic

How to insert a Table in my WordPress post?

I’ve asked a question How to insert a Table in my post?
on a WordPress forum in August 2009. My blog still gets many visitors through the link posted with the question. Why people are coming back?  The problem is that adding a table is still not quite trivial in WordPress, and the only answer given for my question does not work for many people, as it did not work for me.

On the forum, a WordPress  member thesacredpath suggested downloading and using Windows Live Writer blog client. I sincerely appreciate the desire to help, but this advice does not work for me. I don’t want to download anything. Plus, I’m using Mac and Linux  computers, and any Windows program would be  a hassle for me.

The good news, it is not necessary to download any software to be able to add a simple table to a WP blog. I’m using Google Sites, a free service from Google, which has slightly more advanced online HTML WYSIWYG  editor than WordPress. You don’t need to install anything, but you’ll need to register for a free Google account.

If you know other easy ways of creating a simple table without HTML coding and installing software, please comment here, but for me, using Google Sites was good enough. I’ll explain how to create a table below in in great details in case a less technical beginner blogger would like to use this method.

If you already have a Google account, do the following to create an HTML table:

1. Go to http://www.google.com/sites and press the button “Create new site”

2. On the next page, enter a name you like into the field “Name your Site:”, type the right captsha code on the bottom of the page and press “Create site” button. Your new site will be created and opened on the next page. A great byproduct of that method is that you are getting your own free website, which you may use for whatever purposes.

3. On your new site, press “Edit page” button in the upper right corner. Than, in the menu bar on the top of the page select “Table -> Insert Table”, and choose number of rows and columns for your table. When you finished, an empty table will be inserted on your site’s page.

4. Edit your page as you need. Type your data into it. Using the “Table” menu off the site’s editor, you may reconfigure your table, insert and delete rows and columns.

5. When you are done with the creating you table, click on little  icon on the top of the page. You will be taken to the HTML  representation of your page. Select and copy to clipboard the text beginning with “<table style…” and including “… </table>“

6. Come back to you WorPress editor, the post where you want to insert your table.  Choose “HTML” tab in the top right corner of the editor, choose the place where you want to insert your table and paste the html text that you remember on the google site’s. Now switch the editor to the visual mode – choose “Visual” tab in the upper right corner.  You will find your table inserted in your post.

Here is the example of the table hat I created using described method while writing this post. No software installation or HTML coding were involved.

	Store 1	Store 2
Gatget A	120	200
Gatget B	300	50
Widget 1	10	25

Database Nation: The death of privacy in 21st century by Simson Garfinkel

I’ve got this book at the MySQL meetup’s raffle last month. The books title is a little deceiving with the “Database Nation” in very large font on the cover top. In fact, it’s not much about databases.  The smaller subtitle on the cover’s bottom conveys the true subject of the book: “The death of privacy in 21st century.” This is the O’Reilly’s reprint of the book published in 2000.

So, it’s not quite fresh. Some situations and examples in the book are from 1980′s. However, the Database Nation did not loose it’s relevance  since it was first published. It still reads like a thriller, and the the situation with privacy in the US, if anything, became only much worse since the first publication. Garfinkel compares the privacy advocacy movement with the environmental movement born in 1960′s. He hopes that his book will seed in people deeper  awareness of the problem and encourage them to act. At least, the book makes a reader to think more about the privacy issue.

I usually take some reasonable steps to guard my privacy on the Internet, but after reading this book, I’ll  at least will watch closer what’s happening with my credit history. I’ll think twice before each little daily sacrifice of my privacy for convenience,  like recent registering my public transportation pass smart-chip card on the issuer’s website. I’ll also pass the book around. Let’s consider it my small contribution to the privacy movement. Seriously, the Database Nation is very worth reading it.

Londiste upgrade from 2.1.10 to 2.1.12

The londiste upgrade from 2.1.10 is not complicated, but it is not really well documented. Also, I wanted to try it on a test environment before upgrading the replication on the production, so this little checklist was created.

1. Install new binaries’ version

#Run on the host where londiste binaries are installed as postgres:

cd /var/lib/pgsql

wget http://pgfoundry.org/frs/download.php/2872/skytools-2.1.12.tar.gz && tar xvfz skytools-2.1.12.tar.gz && cd ./skytools-2.1.12

#Stop processes
ps -ef | grep londiste
#Kill londiste process
pgqadm.py –stop <pgq_ticker.ini file>

./configure –prefix=/usr –with-pgconfig=/usr/bin/pg_config
make
sudo make install

#Check the new version, it sould be 2.1.12 now:
londiste.py –version

2. Upgrade the londiste db objects
#run on the master and replica
/var/lib/pgsql/skytools-2.1.12/sql/londiste/londiste.upgrade.sql

Check the current londiste schema version:
select * from londiste.version();

3. Run the pgq db objects
I saw skytools v2.1.10 and v2.1.12 both showing version v.2.1.8 for pgq, so initially I decided not upgrade the pgq schema. However, after a londiste upgrade without refreshing the pgq schema, I had a bad ticker lag problem. I’m not 100% sure if this lag problem was caused by the un-refreshed pgq schema or a huge update somebody ran soon after the migration. However, coincidence or not, the ticker lag seems to go away after upgrading the pgq schema.  So, it won’t hurt to upgrade the pgq schema, though it will still report v2.1.8 after the upgrade. On the other hand, it may save you a sleepless night, if leaving pgq schema unrefreshed really causes the ticker problem.

#run on the master only

/var/lib/pgsql/skytools-2.1.12/sql/pgq/pgq.upgrade.sql

#Check the current pgq version:
select * from pgq.version();

4. Restart the replication processes

pgqadm.py -d <pgq_ticker.ini file> ticker
londiste.py -d <londiste.ini file> replay

5. Verify that the replication works

Execute on the master, and verify that the last_seen and the lag values are low:

select * from pgq.get_consumer_info();

Check the londiste log files, try to modify a record in a replicated table and check if the change gets replicated.