Re: improving write performance for logging application

От: Tom Lane
Тема: Re: improving write performance for logging application
Дата: ,
Msg-id: 27720.1136332812@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: improving write performance for logging application  (Steve Eckmann)
Ответы: Re: improving write performance for logging application  (dlang)
Re: improving write performance for logging application  (Steve Eckmann)
Список: pgsql-performance

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

improving write performance for logging application  (Steve Eckmann, )
 Re: improving write performance for logging application  (Tom Lane, )
  Re: improving write performance for logging application  (dlang, )
   Re: improving write performance for logging application  (Steve Eckmann, )
  Re: improving write performance for logging application  (Steve Eckmann, )
   Re: improving write performance for logging application  (Tom Lane, )
    Re: improving write performance for logging application  (Steve Eckmann, )
 Re: improving write performance for logging application  ("Steinar H. Gunderson", )
  Re: improving write performance for logging application  (Steve Eckmann, )
   Re: improving write performance for logging application  (Kelly Burkhart, )
    Re: improving write performance for logging application  (Steve Eckmann, )
 Re: improving write performance for logging application  (Ian Westmacott, )
  Re: improving write performance for logging application  (Steve Eckmann, )
  Re: improving write performance for logging  (Ron, )
   Re: improving write performance for logging  (Ian Westmacott, )
    Re: improving write performance for logging  ("Jim C. Nasby", )
     Re: improving write performance for logging  (Ian Westmacott, )
      Re: improving write performance for logging  ("Jim C. Nasby", )
       Re: improving write performance for logging  (Ian Westmacott, )
        Re: improving write performance for logging  (Tom Lane, )
      Re: improving write performance for logging  (Michael Stone, )

Steve Eckmann <> writes:
> We also found that we could improve MySQL performance significantly
> using MySQL's "INSERT" command extension allowing multiple value-list
> tuples in a single command; the rate for MyISAM tables improved to
> about 2600 objects/second. PostgreSQL doesn't support that language
> extension. Using the COPY command instead of INSERT might help, but
> since rows are being generated on the fly, I don't see how to use COPY
> without running a separate process that reads rows from the
> application and uses COPY to write to the database.

Can you conveniently alter your application to batch INSERT commands
into transactions?  Ie

    BEGIN;
    INSERT ...;
    ... maybe 100 or so inserts ...
    COMMIT;
    BEGIN;
    ... lather, rinse, repeat ...

This cuts down the transactional overhead quite a bit.  A downside is
that you lose multiple rows if any INSERT fails, but then the same would
be true of multiple VALUES lists per INSERT.

            regards, tom lane


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

От: "Virag Saksena"
Дата:
Сообщение: Avoiding cartesian product
От: Ian Westmacott
Дата:
Сообщение: Re: improving write performance for logging application