Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
    
add please output: iostat -dx 5 –  bindbn Oct 8 '10 at 14:29
    
added as requested –  ehsanul Oct 8 '10 at 14:35
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks, this must be it. Reads are fast and directly off the SSD, I was just scared I was storing a bunch of table data on the HDD without realizing. Since the pg_xlog gets such heavy write activity, I'm guessing it wouldn't be a good idea to put it on the SSD, lest it quickly erodes. Is there any other way to reduce xlog activity? I'm not processing bank transactions or something, so losing a few writes is not a real problem. –  ehsanul Oct 13 '10 at 1:44
    
If you're willing to lose some transactions while maintaining the crash-safetyness, you can turn off synchronous_commit. It will help in at least some cases. –  Magnus Hagander Oct 17 '10 at 16:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.