Postgresql – Moved PostgreSQL 8.4 tables to a tablespace on an SSD, but still getting 4MB/s writes to old HDD

postgresqlssd

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

Best Answer

If you're only talking writes, that's probably because your transaction log in the pg_xlog directory is still on the old drive. I'd suggest moving the tables back to the regular harddrive and move the pg_xlog directory (which is the one that takes sync writes) to the SSD.

Related Topic