ehsanul's questions

I've set swappiness on my DB server to zero, but during a large sort operation the swap space used grew even as the cache was still using a lot of memory, and then cache grew but swap was left alone. This makes no sense to me. Here's free -m as of now:

             total       used       free     shared    buffers     cached
Mem:         16000      15979         20          0         24      10520
-/+ buffers/cache:       5434      10565
Swap:         6111       5478        633

This is happening in the middle of a large sort in PostgreSQL. The issue is that the swapped out memory will probably be called upon as soon as the sort is over (it's a big UPDATE on a table I put into tmpfs), resulting in a huge slowdown as it will be random access.

tee forwards its stdin to every single file specified, while pee does the same, but for pipes. These programs send every single line of their stdin to each and every file/pipe specified.

However, I was looking for a way to "load balance" the stdin to different pipes, so one line is sent to the first pipe, another line to the second, etc. It would also be nice if the stdout of the pipes are collected into one stream as well.

The use case is simple parallelization of CPU intensive processes that work on a line-by-line basis. I was doing a sed on a 14GB file, and it could have run much faster if I could use multiple sed processes. The command was like this:

pv infile | sed 's/something//' > outfile

To parallelize, the best would be if GNU parallel would support this functionality like so (made up the --demux-stdin option):

pv infile | parallel -u -j4 --demux-stdin "sed 's/something//'" > outfile

However, there's no option like this and parallel always uses its stdin as arguments for the command it invokes, like xargs. So I tried this, but it's hopelessly slow, and it's clear why:

pv infile | parallel -u -j4 "echo {} | sed 's/something//'" > outfile

I just wanted to know if there's any other way to do this (short of coding it up myself). If there was a "load-balancing" tee (let's call it lee), I could do this:

pv infile | lee >(sed 's/something//' >> outfile) >(sed 's/something//' >> outfile) >(sed 's/something//' >> outfile) >(sed 's/something//' >> outfile)

Not pretty, so I'd definitely prefer something like the made up parallel version, but this would work too.

The strange and extremely slow IO pattern I'm seeing is this (output of iostat -dxk 1 /dev/xvdb1):

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvdb1             0.00     0.00  0.99  0.99     7.92     3.96    12.00     1.96 2206.00 502.00  99.41

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvdb1             0.00     0.00  0.00  0.00     0.00     0.00     0.00     1.00    0.00   0.00 100.40

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvdb1             0.00     0.00  0.00  0.00     0.00     0.00     0.00     1.00    0.00   0.00 100.40

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvdb1             0.00     0.00  0.99  0.00     3.96     0.00     8.00     0.99 2220.00 1004.00  99.41

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvdb1             0.00     0.00  0.00  0.00     0.00     0.00     0.00     1.00    0.00   0.00 100.40

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvdb1             0.00     0.99  0.99  0.00     7.92     0.00    16.00     1.14 2148.00 1004.00  99.41

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvdb1             0.00     0.00  0.00  0.00     0.00     0.00     0.00     2.01    0.00   0.00 100.40

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvdb1             0.00     0.00  1.00  1.00     4.00     8.00    12.00     2.01 1874.00 502.00 100.40

I don't know why disk utilization and await is so high, and the read/write rates are so low. What could the reason for this be?

The table being queried simply has several varchar columns only, one of which is last_name, which is indexed (actually lower(last_name) is indexed). The query itself is simple:

SELECT * FROM consumer_m WHERE lower(last_name) = 'hoque';

Here's the explain output:

                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on consumer_m  (cost=2243.90..274163.41 rows=113152 width=164)
   Recheck Cond: (lower((last_name)::text) = 'hoque'::text)
   ->  Bitmap Index Scan on consumer_m_last_name_index  (cost=0.00..2215.61 rows=113152 width=0)
         Index Cond: (lower((last_name)::text) = 'hoque'::text)

Also note that the database is on auto_vacuum, so no explicit vacuum/analyze was performed.

I was sorting some large files (91GB across 27 files) with GNU sort when I noticed that iostat -dxk 3 showed very slow read speeds, between 5 MB/s and 10 MB/s, with 100% disk utilization. I tried cat large-file > /dev/null, and got similar performance, only slightly higher. The same for cp large-file /tmp/, with /tmp on a separate disk. vim experiences the same, as well as scripts I write in Ruby reading files, if that helps. Write speed is fine and fast though.

EDIT: It looks like these operations are only slow on files in a certain directory. The same operations on other files in a sibling directory (same disk partition), end up being fast, with above 90 MBPS read speed. This makes no sense to me. Could it possibly be due to the manner in which these files were constructed? I created them by reading in a lot of other files, and writing each line into an appropriate "bucket file", depending on the first character in the line (so a-z, and a single file for others). So I was pretty much simultaneously appending lines to 27 files, one at a time, through 8 processes while reading a couple thousand files. Could this cause the sequential order the blocks representing a file to be out of order instead? Hence the slow sequential reads afterwards?

However, I tried using fio to measure sequential read performance, and it clocked in at 73 MB/s. Also notable is that my boss got proper read speeds when downloading some files via FTP from the same machine.

So I'm guessing this is some configuration issue somewhere, but I have no idea where. What could the reason be and how can I try to fix it?

Edit: This machine is running under Citrix Xen virtualization.

Edit: Output of iostat -dxk while sort is loading a large file into its buffer (get similar output for cat/cp):

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvdb              0.00     0.00 1000.00  0.00  6138.61     0.00    12.28    24.66   24.10   0.99  99.41
xvdb1             0.00     0.00 1000.00  0.00  6138.61     0.00    12.28    24.66   24.10   0.99  99.41
xvda              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
xvda1             0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Edit: Further performance degradation after a few hours (with breaks for the disk when sort was processing). It almost looks like random IO, but there's only a single sort operation going on, with no other processes doing any IO, so reads should be sequential =/ :

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvdb              0.00     0.00 638.00  0.00  2966.67     0.00     9.30    25.89   40.62   1.57 100.00

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvdb              0.33     0.00 574.67  0.00  2613.33     0.00     9.10    27.82   47.55   1.74 100.00 

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvdb              0.00     0.00 444.33  0.00  1801.33     0.00     8.11    28.41   65.27   2.25 100.00

I first made a postgres-owned directory on the SSD and created the tablespace like so:

CREATE TABLESPACE ssd_tablespace LOCATION '/ssd/pgdata';

I proceeded to move important tables and all indices on them (this is showing just a single partition, and I altered the parent table too):

ALTER TABLE coreg_2010_a SET TABLESPACE ssd_tablespace;
ALTER INDEX coreg_2010_a_pkey SET TABLESPACE ssd_tablespace;
ALTER TABLE visits_2010_a SET TABLESPACE ssd_tablespace;
ALTER INDEX visits_2010_a_pkey SET TABLESPACE ssd_tablespace;

All operations were successful, and I confirmed the tablespace has been set in psql. There are foreign keys as well between the tables moved, but those are not indexes that can be moved.

iostat shows me that I'm there's a little write activity on the SSD (though not much) through an ongoing script which is doing INSERT operations into the tables I've moved. However, the vast majority of the write activity is on the old hard disk for some reason (and I'm only writing to tables in ssd_tablespace). Furthermore, the total size of the tables with indices seems to exceed the space used up on the SSD, though I have not confirmed this properly yet.

Why am I still getting writes to my old hard drive if the tables being inserted into (and their indices) have been moved to a new tablespace on a new hard drive?

For reference, here is what iostat -dxk 5 shows with an 5-second interval. /dev/xvde1 is the old hard drive, while /dev/xvdc1 is the SSD associated with ssd_tablespace:

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvde1             0.00   486.60  0.00 453.40     0.00  3760.00    16.59     0.85    1.87   1.87  84.72
xvdb1             0.00     1.20  0.00  0.80     0.00     8.00    20.00     0.00    0.00   0.00   0.00
xvdc1             0.00    26.20  0.00 11.20     0.00   149.60    26.71     0.00    0.29   0.14   0.16

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvde1             0.00   561.40  0.00 523.60     0.00  4340.00    16.58     0.86    1.65   1.65  86.40
xvdb1             0.00     1.20  0.00  0.60     0.00     7.20    24.00     0.00    0.00   0.00   0.00
xvdc1             0.00    26.20  0.00 13.20     0.00   157.60    23.88     0.02    1.21   0.06   0.08

Device:         rrqm/s   wrqm/s   r/s   w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
xvde1             0.00   524.80  0.00 489.40     0.00  4056.00    16.58     0.85    1.72   1.73  84.56
xvdb1             0.00     1.00  0.00  2.00     0.00    12.00    12.00     0.00    1.20   0.40   0.08
xvdc1             0.00    24.60  0.00 10.80     0.00   141.60    26.22     0.00    0.07   0.07   0.08

As requested, here is output of iostat -dx 5:

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvde1             3.00  1078.20 31.40 469.00  2664.00 12379.20    30.06     1.29    2.57   1.46  73.04
xvdb1             0.00     1.00  0.00  0.60     0.00    12.80    21.33     0.00    1.33   1.33   0.08
xvdc1             0.00     7.40  0.00  5.00     0.00    99.20    19.84     0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvde1             6.00  1067.60 32.80 425.40  2768.00 11944.00    32.11     1.37    3.00   1.53  70.16
xvdb1             0.00     1.00  0.00  0.60     0.00    12.80    21.33     0.00    0.00   0.00   0.00
xvdc1             0.00     6.80  0.00  6.00     0.00   102.40    17.07     0.00    0.00   0.00   0.00

Question

How can I make the query described in this post faster, in particular by making PostgreSQL using the available RAM? - Note that I have tried to configure effective_cache_size and shared_buffers appropriately. See below.

Background

I have to regularly join a ~260 million row table (coreg_master) with new data that comes in. I've partitioned the table to allow each partition to fit in RAM. I do have appropriate indexes set up too of course. However, when joining the partitioned tables separately against the other (much smaller) tables, it's doing fully random IO on the disk. This is due to a nested loop index scan on the large table, which is really slow as we don't have a great disk setup.

I'd expect it to use all available RAM to cache the large partitioned table instead, which I understand should be done by the Linux kernel/filesystem itself. But it still doesn't load the table into RAM, though it would fit. I guess that's because the access pattern isn't sequential and thus doesn't trigger caching? I have no idea. Query plan and configuration parameters below.

Table Structure

This is one of the partitions of coreg_master, my large table. The partitioned tables are named coreg_a, coreg_b, etc.

\d coreg_a
                                   Table "public.coreg_a"
   Column    |       Type        |                         Modifiers                         
-------------+-------------------+-----------------------------------------------------------
 id          | integer           | not null default nextval('coreg_master_id_seq'::regclass)
 first_name  | character varying | 
 last_name   | character varying | 
 phone       | character varying | 
 city        | character varying | 
 zip         | integer           | 
 address     | character varying | 
 dob         | date              | 
 ip          | character varying | 
 source      | character varying | 
 gender      | character varying | 
 state       | character varying | 
 record_date | date              | 
 email       | character varying | 
Indexes:
    "coreg_a_name" btree (lower(first_name::text), lower(last_name::text))
Check constraints:
    "coreg_a_first_name_check" CHECK (first_name::text >= 'a'::text AND first_name::text < 'b'::text)
Inherits: coreg_master

The following is a partition of table appendable_24, an example of the table being joined with coreg_master. It is also partitioned the same way as coreg_master, so actually coreg_a is joined with appendable_24_a, etc, one at a time.

\d appendable_24_a
       Table "public.appendable_24_a"
   Column   |       Type        | Modifiers 
------------+-------------------+-----------
 line_num   | integer           | not null
 first_name | character varying | 
 last_name  | character varying | 
 address    | character varying | 
 state      | character varying | 
 zip        | integer           | 
Indexes:
    "appendable_24_a_name_index" btree (lower(first_name::text), lower(last_name::text))
Check constraints:
    "appendable_24_a_first_name_check" CHECK (first_name::text >= 'a'::text AND first_name::text < 'b'::text)
Inherits: appendable_24

Query & EXPLAIN ANALYZE

Below is the output of explain analyze for the smallest of the joins (tables are partitioned according to the first letter of the first_name column), as it doesn't take ages. However, the query plan is the same for all the joins on each partition, so it should be representative of the larger joins as well (note, i did ANALYZE the tables, and the total time was actually 20 seconds, but faster here as the result was cached):

explain analyze SELECT
          coreg_x.phone,
          coreg_x.email,
          coreg_x.record_date,
          appendable_24_x.line_num
        FROM appendable_24_x INNER JOIN coreg_x ON
          lower(appendable_24_x.first_name) = lower(coreg_x.first_name) AND
          lower(appendable_24_x.last_name) = lower(coreg_x.last_name) AND
          (coreg_x.phone IS NOT NULL OR coreg_x.email IS NOT NULL) AND
          similarity(lower(appendable_24_x.address), lower(coreg_x.address)) > 0.7
      ; 
                                                                                       QUE
RY PLAN 

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-----
 Nested Loop  (cost=0.01..640.49 rows=875 width=39) (actual time=9.990..53.839 rows=29 loo
ps=1)
   Join Filter: (similarity(lower((appendable_24_x.address)::text), lower((coreg_x.address
)::text)) > 0.7::double precision)
   ->  Seq Scan on appendable_24_x  (cost=0.00..1.80 rows=80 width=34) (actual time=0.009.
.0.111 rows=80 loops=1)
   ->  Index Scan using coreg_x_name on coreg_x  (cost=0.01..7.95 rows=1 width=64) (actual
 time=0.024..0.137 rows=44 loops=80)
         Index Cond: ((lower((coreg_x.first_name)::text) = lower((appendable_24_x.first_na
me)::text)) AND (lower((coreg_x.last_name)::text) = lower((appendable_24_x.last_name)::tex
t)))
         Filter: ((coreg_x.phone IS NOT NULL) OR (coreg_x.email IS NOT NULL))
 Total runtime: 53.950 ms
(7 rows)

Some stats, configuration parameters and other data

• PostgreSQL Version: 8.4.4
• OS: CentOS release 5.5 (Final)
• Filesystem: ext3
• Total available RAM: 8GB
• shared_buffers = 2GB
• effective_cache_size = 7200MB
• full runtime configuration through show all: http://pastie.org/1159746
• largest partitioned coreg table (coreg_j) size: ~4900MB
• corresponding number of rows: ~32 million
• corresponding (first_name, last_name) index size: ~1000MB
• second partitioned table (appendable_24_j) size: ~1800kB
• rows in appendable_24_j: ~25,000

I'm on Ubuntu 8.10

The script runs when called from the command line with sudo /etc/init.d/xbindkeys start. Here's how it looks:

#! /bin/sh
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin
case "$1" in
  start)
    xbindkeys
  ;;
  stop)
    ps aux | grep xbindkeys | head -n1 | awk '{print $2}' | xargs kill
  ;;
  restart)
    $0 stop
    $0 start
  ;;
esac

I had run sudo update-rc.d xbindkeys defaults earlier to create the symlinks. To make sure it was linked correctly, I tried chkconfig | grep xbindkeys, which returns:

xbindkeys                        2345

However, after restarting, I don't see the process with ps aux | grep xbindkeys.

Ideas?

Each time I make some changes to a file and save, I'd like a command to run. I can set up a Ruby script that could monitor the file and run the command as I'd like, but it occurs to me that there should be some simple Unix way to do this. Is there? I'm on Ubuntu 8.10, to be slightly less vague.

Edit:

incron was suggested, and looks good. But I'm having trouble getting it to work (edit: the command doesn't seem to run when I save a file in the specified directory, or my command is faulty). I installed incron and set up my user's incron table using incrontab, and added the user to the allowed list. My specific situation is editing a TeX file. I want the command to render it as a PDF to run every time I edit and save the TeX file. So this is the incron table entry I came up with:

/home/ehsanul/Documents/latex IN_CLOSE_WRITE echo $@/$# | grep \\.tex$ | xargs xelatex

What am I doing wrong?

I actually just have shared hosting at Dreamhost, and I'm going to use it to test deploying a MongoDB based application. There are 32-bit and 64-bit pre-built distros, but I don't know which I should install, until I can determine the server's architecture.

I suppose this can be done by detecting the OS version, but I have no idea how to do that. I can ssh in, though not as root of course, since it's a shared server. I just need to know what command or commands can tell me what architecture is being run on.

Is anybody familiar with how to go about frugal installations of different linux distributions? The information I have is mostly from a poster on linuxquestions.org (here's the thread), and installation worked for me on one particular distribution.

But I haven't been able to find any general information on how this method of installation works, and how to apply it to other linux distributions. I'm also clueless about the relative advantages and disadvantages of this over other methods.

I'm having some trouble with dependencies it seems, but myself don't really know how to resolve the issue. Here's the output:

~:) sudo apt-get install build-essential
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.

Since you only requested a single operation it is extremely likely that
the package is simply not installable and a bug report against
that package should be filed.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
  build-essential: Depends: g++ (>= 4:4.3.1) but 4:4.2.3-1ubuntu6 is to be installed
E: Broken packages
~:) sudo apt-get install g++
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.

Since you only requested a single operation it is extremely likely that
the package is simply not installable and a bug report against
that package should be filed.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
  g++: Depends: cpp (>= 4:4.3.1-1ubuntu2) but 4:4.2.3-1ubuntu6 is to be installed
       Depends: gcc (>= 4:4.3.1-1ubuntu2) but 4:4.2.3-1ubuntu6 is to be installed
       Depends: g++-4.3 (>= 4.3.1-1) but it is not going to be installed
       Depends: gcc-4.3 (>= 4.3.1-1) but it is not installable
E: Broken packages
~:) 

Edit: I just tried aptitude instead of apt-get, as suggested. Doesn't work, had other problems:

~:) sudo aptitude install build-essential
[sudo] password for ehsanul: 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Reading extended state information      
Initializing package states... Done
Building tag database... Done      
The following packages are BROKEN:
  g++ g++-4.3 libstdc++6-4.3-dev 
The following packages have been automatically kept back:
  dpkg-dev fakeroot libdns35 libisc35 linux-libc-dev patch 
The following NEW packages will be automatically installed:
  libgmp3c2 libmpfr1ldbl 
The following packages have been kept back:
  adobe-flashplugin bind9-host dnsutils gvfs gvfs-backends gvfs-fuse 
  libatm1 libbind9-30 libgvfscommon0 libisccc30 libisccfg30 liblwres30 
  libnautilus-extension1 linux-headers-2.6.24-24 
  linux-headers-2.6.24-24-generic linux-image-2.6.24-24-generic nautilus 
  nautilus-data 
The following NEW packages will be installed:
  libgmp3c2 libmpfr1ldbl 
The following packages will be upgraded:
  build-essential 
The following partially installed packages will be configured:
  timidity 
2 packages upgraded, 4 newly installed, 0 to remove and 24 not upgraded.
Need to get 775kB/6265kB of archives. After unpacking 20.3MB will be used.
The following packages have unmet dependencies:
  libstdc++6-4.3-dev: Depends: gcc-4.3-base (= 4.3.2-1ubuntu11) which is a virtual package.
                      Depends: libstdc++6 (>= 4.3.2-1ubuntu11) but 4.2.4-1ubuntu4 is installed.
  g++-4.3: Depends: gcc-4.3-base (= 4.3.2-1ubuntu11) which is a virtual package.
           Depends: gcc-4.3 (= 4.3.2-1ubuntu11) which is a virtual package.
           Depends: libc6 (>= 2.8~20080505) but 2.7-10ubuntu4 is installed.
  g++: Depends: cpp (>= 4:4.3.1-1ubuntu2) but 4:4.2.3-1ubuntu6 is installed.
       Depends: gcc (>= 4:4.3.1-1ubuntu2) but 4:4.2.3-1ubuntu6 is installed.
       Depends: gcc-4.3 (>= 4.3.1-1) which is a virtual package.
Resolving dependencies...
The following actions will resolve these dependencies:

Keep the following packages at their current version:
build-essential [11.3ubuntu1 (hardy, now)]
g++ [4:4.2.3-1ubuntu6 (hardy-updates, now)]
g++-4.3 [Not Installed]
libstdc++6-4.3-dev [Not Installed]

Score is -9852

Accept this solution? [Y/n/q/?]