Running lots of inserts from selects on 9.4.5

От: Dan Langille
Тема: Running lots of inserts from selects on 9.4.5
Дата: ,
Msg-id: 69012280-1847-426A-8813-D51CA4CC9B02@langille.org
(см: обсуждение, исходный текст)
Ответы: Re: Running lots of inserts from selects on 9.4.5  (Jeff Janes)
Список: pgsql-performance

Скрыть дерево обсуждения

Running lots of inserts from selects on 9.4.5  (Dan Langille, )
 Re: Running lots of inserts from selects on 9.4.5  (Jeff Janes, )
  Re: Running lots of inserts from selects on 9.4.5  (Dan Langille, )
   Re: Running lots of inserts from selects on 9.4.5  (Dan Langille, )
    Re: Running lots of inserts from selects on 9.4.5  (Dan Langille, )
     Re: Running lots of inserts from selects on 9.4.5  (Dan Langille, )

I have a wee database server which regularly tries to insert 1.5 million or even 15 million new rows into a 400 million row table.  Sometimes these inserts take hours.

The actual query to produces the join is fast.  It's the insert which is slow.

INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq) 
  SELECT batch_testing.FileIndex, batch_testing.JobId, Path.PathId, Filename.FilenameId, batch_testing.LStat, batch_testing.MD5, batch_testing.DeltaSeq 
    FROM batch_testing JOIN Path     ON (batch_testing.Path = Path.Path) 
                       JOIN Filename ON (batch_testing.Name = Filename.Name);


This gist contains postgresql.conf, zfs settings, slog, disk partitions.

   https://gist.github.com/dlangille/33331a8c8cc62fa13b9f

I'm tempted to move it to faster hardware, but in case I've missed something basic...

Thank you.

-- 
Dan Langille - BSDCan / PGCon




Вложения

В списке pgsql-performance по дате сообщения:

От: Dan Langille
Дата:
Сообщение: Running lots of inserts from selects on 9.4.5
От: Jeff Janes
Дата:
Сообщение: Re: Running lots of inserts from selects on 9.4.5