суббота, 24 сентября 2011 г.

Adding vdev to raidz pool

There is one fundamental flaw in raidz ZFS pools: you can't add devices to raidz (as opposed to usual raid5). So, could we do anything to expand our pool without full rebuilding? Yes, if we are ready to sacrifice one more disk.
From zpool man page:

A pool can have any number of virtual devices at the top of the config‐
uration (known as "root vdevs"). Data is dynamically distributed across
all top-level devices to balance data among devices. As new virtual
devices are added, ZFS automatically places data on the newly available
devices.

So, to add some capacity to our pool, we can add one more raidz to it. Let's look at this process. Firstly, let's create test pool from three "disks":

freebsd# foreach i ( 1 2 3 4 5 6)
foreach? dd if=/dev/zero of=/tmp/disk$i bs=100M count=1
foreach? end
freebsd# zpool create testpool raidz /tmp/disk1 /tmp/disk2 /tmp/disk3

Now, let's see what we have:

freebsd# zpool status testpool
pool: testpool
state: ONLINE
scrub: none requested
config:

NAME STATE READ WRITE CKSUM
testpool ONLINE 0 0 0
raidz1 ONLINE 0 0 0
/tmp/disk1 ONLINE 0 0 0
/tmp/disk2 ONLINE 0 0 0
/tmp/disk3 ONLINE 0 0 0

errors: No known data errors
freebsd# zpool list testpool
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
testpool 286M 156K 286M 0% ONLINE -
freebsd# zfs list testpool
NAME USED AVAIL REFER MOUNTPOINT
testpool 95.9K 158M 28.0K /testpool

Now let's expand this pool:

freebsd# zpool add testpool raidz /tmp/disk4 /tmp/disk5 /tmp/disk6

So, we recieved the following configuration:

freebsd# zpool status testpool
pool: testpool
state: ONLINE
scrub: none requested
config:

NAME STATE READ WRITE CKSUM
testpool ONLINE 0 0 0
raidz1 ONLINE 0 0 0
/tmp/disk1 ONLINE 0 0 0
/tmp/disk2 ONLINE 0 0 0
/tmp/disk3 ONLINE 0 0 0
raidz1 ONLINE 0 0 0
/tmp/disk4 ONLINE 0 0 0
/tmp/disk5 ONLINE 0 0 0
/tmp/disk6 ONLINE 0 0 0

errors: No known data errors
freebsd# zpool list testpool
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
testpool 572M 210K 572M 0% ONLINE -
freebsd# zfs list testpool
NAME USED AVAIL REFER MOUNTPOINT
testpool 114K 349M 28.0K /testpool

Let's compare this to raidz from 6 disks:

freebsd# zpool create testpool raidz /tmp/disk1 /tmp/disk2 /tmp/disk3 /tmp/disk4 /tmp/disk5 /tmp/disk6
freebsd# zpool status testpool
pool: testpool
state: ONLINE
scrub: none requested
config:

NAME STATE READ WRITE CKSUM
testpool ONLINE 0 0 0
raidz1 ONLINE 0 0 0
/tmp/disk1 ONLINE 0 0 0
/tmp/disk2 ONLINE 0 0 0
/tmp/disk3 ONLINE 0 0 0
/tmp/disk4 ONLINE 0 0 0
/tmp/disk5 ONLINE 0 0 0
/tmp/disk6 ONLINE 0 0 0

errors: No known data errors
freebsd# zpool list testpool
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
testpool 572M 147K 572M 0% ONLINE -
freebsd# zfs list testpool
NAME USED AVAIL REFER MOUNTPOINT
testpool 112K 443M 34.9K /testpool

So, we lost more then 21% of space, but expanded our pool without downtime. Firstly, the lost of more then 20% space suprised me, but 20% was expectable (in second case only 5 disks, in first - 4, so 20% is the difference).
Let's see, where did 1 percent go. 443 MB is useful in second case, it means about 11.4 MB (100 /*disk size*/ - 443/*useful space*/ / 5 /*useful disks*/) metadata per disk. In first case we have 349 MB. It is provided by two pools with 2 useful disks in each. So, wasted space per disk is 12.75 MB (100 - 349/2/2). It seems, in this configuration we have a bit more metadata.

среда, 21 сентября 2011 г.

PostgreSQL 9.1 create extension and database upgrade

Yesterday I was eager to try some new 9.1 features, in particular to experiment with exclude constraints (I've read they were improved since 9.0). So, I decided to upgrade my test 9.0 installation. It worth mentioning that it was 8.4 installation, previously upgraded to 9.0.
Everything went as expected, even my C functions were successfully recompiled without any warnings.
So, I decided to try extensions. I had hstore module installed in my db, so I issued "create extension" and received the following error:


db=# CREATE EXTENSION hstore WITH SCHEMA public FROM unpackaged;
ERROR: function hstore_recv(internal) does not exist


I looked at hstore definition in /usr/local/share/postgresql/extension/hstore--1.0.sql, copied definition of this function, created it, but received error saying that some other function doesn't exist.
So I run full script to create unpackaged extension:

# sed -e 's|MODULE_PATHNAME|$libdir/hstore|' < /usr/local/share/postgresql/extension/hstore--1.0.sql | su pgsql -c 'psql -d db '

After that it was possible to create extension:

db=# CREATE EXTENSION hstore WITH SCHEMA public FROM unpackaged;


It seems that the reason for this behavior is that I didn't upgrade hstore module after upgrading database from 8.4 to 9.0...

воскресенье, 18 сентября 2011 г.

Extracting domain name from uri with SQL

I don't know if it is effective, but this ugly SQL (in PostgreSQL dialect) extracts domain name part from uri (i.e. translates protocol://something/page into "something"):

SELECT uri, substring (uri from position('//' in uri)+2 for ( char_length(uri) - char_length(substring (uri from position ('/' in substring ( uri from position('//' in uri)+2 )) ) ))) from proxy_data

It seems, it would look better with regexp:
 
select uri, substring ( substring (uri from '//[[:alnum:]\._\-]+') from 3) from proxy_data

However, in later case we should list all possible symbols in domain name in our regexp.