slow insert speeds with bytea

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

slow insert speeds with bytea

Alex O'Ree-2
Is there anything I can to increase insert speeds for bytea? Currently running postgres 9.6.15

I have a few tables without a bytea and a few with bytea. There is a large performance difference with inserts between the two. I'm inserting a byte[] that's usually less than 1MB on content. The content itself is actually just utf8 string data.

For the non-bytea table, inserts can be as high as 40k rows/sec, whereas the bytea table is closer to 4k/sec or less.

If this is just a limitation of postgres, then that's fine but the performance delta is so significant that i feel like i'm missing something
Reply | Threaded
Open this post in threaded view
|

Re: slow insert speeds with bytea

Adrian Klaver-4
On 12/1/19 9:59 AM, Alex O'Ree wrote:

> Is there anything I can to increase insert speeds for bytea? Currently
> running postgres 9.6.15
>
> I have a few tables without a bytea and a few with bytea. There is a
> large performance difference with inserts between the two. I'm inserting
> a byte[] that's usually less than 1MB on content. The content itself is
> actually just utf8 string data.
>
> For the non-bytea table, inserts can be as high as 40k rows/sec, whereas
> the bytea table is closer to 4k/sec or less.
>
> If this is just a limitation of postgres, then that's fine but the
> performance delta is so significant that i feel like i'm missing something

It would help to have more information:

1) The schema of the table e.g. the output of \d in psql.

2) The actual INSERT query.

3) An EXPLAIN ANALYZE of the INSERT query.


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: slow insert speeds with bytea

Steve Atkins
In reply to this post by Alex O'Ree-2

On 01/12/2019 17:59, Alex O'Ree wrote:

> Is there anything I can to increase insert speeds for bytea? Currently
> running postgres 9.6.15
>
> I have a few tables without a bytea and a few with bytea. There is a
> large performance difference with inserts between the two. I'm
> inserting a byte[] that's usually less than 1MB on content. The
> content itself is actually just utf8 string data.
>
> For the non-bytea table, inserts can be as high as 40k rows/sec,
> whereas the bytea table is closer to 4k/sec or less.

4k 1MB rows/sec would be 4GB a second. It would need to be a fairly
decent IO system to manage that speed, let alone ten times that.

What's the typical row size of the tables with bytea fields vs the other
tables?

What are your IO stats during the two sorts of insert?

Cheers,
   Steve


Reply | Threaded
Open this post in threaded view
|

Re: slow insert speeds with bytea

Jeff Janes
In reply to this post by Alex O'Ree-2
On Mon, Dec 2, 2019 at 4:42 AM Alex O'Ree <[hidden email]> wrote:
Is there anything I can to increase insert speeds for bytea? Currently running postgres 9.6.15

I have a few tables without a bytea and a few with bytea. There is a large performance difference with inserts between the two. I'm inserting a byte[] that's usually less than 1MB on content. The content itself is actually just utf8 string data.

For the non-bytea table, inserts can be as high as 40k rows/sec, whereas the bytea table is closer to 4k/sec or less.

If this is just a limitation of postgres, then that's fine but the performance delta is so significant that i feel like i'm missing something

That does seem pretty drastic.  But I don't think we will get anywhere unless you post your actual benchmarking program so that we can try it for ourselves.  I certainly don't see anything that drastic in my own testing.  Maybe the bottleneck is on the client side.

Cheers,

Jeff