Postgresql – Does HOT Update re-arrange existing rows on a page to create a contiguous space

Your my.conf says the following: key_buffer_size=32M That's really small. You could afford to set it higher: Run this query please:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will give you a recommended size of key_buffe_size. Please you common sense when setting this value.

Here is something else: I don't see a bulk_insert_buffer_size. The default is 8MB. Try setting it to 512MB:

[mysqld]
bulk_insert_buffer_size=512M

I just noticed your myisam_sort_buffer_size is only 300M. Set it to 4G (max value)

[mysqld]
myisam_sort_buffer_size=4G

1. No the only thing close to that is when you compile the server with the --with-segsize switch, this might help if your table is taking up more space than a gig and your file system can handle a single file being over a gig. If your inserting 20 gigs it will have to create 20 files if you don't use this switch. If your file system can handle a file over a gig you can just set it to a large value most likely see some benefit, worst case a small benefit.

2. Take a look at CLUSTER http://www.postgresql.org/docs/9.1/static/sql-cluster.htmland FILLFACTOR http://www.postgresql.org/docs/9.1/static/sql-createtable.html, http://www.postgresql.org/docs/9.1/static/sql-createindex.html

Note that FILLFACTOR can be applied to both tables and indexes.