Re: performance for high-volume log insertion

Поиск
Список
Период
Сортировка
От Stephen Frost
Тема Re: performance for high-volume log insertion
Дата
Msg-id 20090422215100.GI8123@tamriel.snowman.net
обсуждение исходный текст
Ответ на Re: performance for high-volume log insertion  (Glenn Maynard <glennfmaynard@gmail.com>)
Ответы Re: performance for high-volume log insertion  (Glenn Maynard <glennfmaynard@gmail.com>)
Re: performance for high-volume log insertion  (Thomas Kellerer <spam_eater@gmx.net>)
Список pgsql-performance
* Glenn Maynard (glennfmaynard@gmail.com) wrote:
> >> separate inserts, no transaction: 21.21s
> >> separate inserts, same transaction: 1.89s
> >> 40 inserts, 100 rows/insert: 0.18s
> >> one 40000-value insert: 0.16s
> >> 40 prepared inserts, 100 rows/insert: 0.15s
> >> COPY (text): 0.10s
> >> COPY (binary): 0.10s
> >
> > What about 40000 individual prepared inserts?  Just curious about it.
>
> 40000 inserts, one prepared statement each (constructing the prepared
> statement only once), in a single transaction: 1.68s
>
> I'm surprised that there's any win here at all.

For a single column table, I wouldn't expect much either.  With more
columns I think it would be a larger improvement.

> Each row had one integer column.  I expect strings to be harder to
> parse, since it's allocating buffers and parsing escapes, which is
> usually more expensive than parsing an integer out of a string.  I'd
> expect the difference to be negligible either way, though, and I'd be
> interested in hearing about use cases where binary copying is enough
> of a win to be worth the development cost of maintaining the feature.

I've seen it help, but I was sending everything as binary (I figured,
once I'm doing it, might as well do it all), which included dates,
timestamps, IP addresses, integers, and some text.  It may have more of
an impact on dates and timestamps than on simple integers.

    Thanks!

        Stephen

Вложения

В списке pgsql-performance по дате отправления:

Предыдущее
От: Glenn Maynard
Дата:
Сообщение: Re: performance for high-volume log insertion
Следующее
От: Glenn Maynard
Дата:
Сообщение: Re: performance for high-volume log insertion