Re: Speed of lo_unlink vs. DELETE on BYTEA
| От | Reuven M. Lerner |
|---|---|
| Тема | Re: Speed of lo_unlink vs. DELETE on BYTEA |
| Дата | |
| Msg-id | 4E7EBF73.4050205@lerner.co.il обсуждение исходный текст |
| Ответ на | Speed of lo_unlink vs. DELETE on BYTEA ("Reuven M. Lerner" <reuven@lerner.co.il>) |
| Ответы |
Re: Speed of lo_unlink vs. DELETE on BYTEA
Re: Speed of lo_unlink vs. DELETE on BYTEA |
| Список | pgsql-general |
body
p { margin-bottom: 10pt; margin-top: 0pt; }
body p { margin-bottom: 10pt; margin-top: 0pt; }
<body style="direction: ltr;"
bidimailui-detected-decoding-type="UTF-8" bgcolor="#FFFFFF"
text="#000000">
Hi, everyone. Daniel Verite <a class="moz-txt-link-rfc2396E"
href="mailto:daniel@manitou-mail.org"><daniel@manitou-mail.org>
wrote:
How much bytea are you dumping for it to take only 0.066s?
The fact that it takes about the same time than dumping the "empty content"
looks very suspicious.
On my desktop machine, if I create a table with 1000 blobs containing strings
of 5 million 'x', which is what I understood you basically did (perhaps I
misunderstood?), then it takes about 200s to dump it with pg_dump -Fc
OK, this is an egg-on-my-face moment with my benchmarks: I added the
pg_dump timing after the "delete" timing, and so I was actually
dumping the database when it was empty! Not very effective, to say
the least.
I've updated my benchmark, and updated the results, as well:
|                          | Delete   | Dump     | Database
size | Dump size |
|---------------------------+-----------+-----------+---------------+-----------|
| Empty content            | 0m0.151s | 0m38.875s | 88
kBÂ Â Â Â Â Â Â Â | 11KÂ Â Â Â Â Â |
| bytea                    | 0m0.505s | 1m59.565s | 57
MBÂ Â Â Â Â Â Â Â | 4.7MÂ Â Â Â Â |
| large object with rule   | 0m31.438s | 2m42.079s | 88
kBÂ Â Â Â Â Â Â Â | 4.7MÂ Â Â Â Â |
| large object with trigger | 0m28.612s | 2m17.156s | 88
kBÂ Â Â Â Â Â Â Â | 4.7MÂ Â Â Â Â |
** 10,000 records
|                          | Delete   | Dump      | Database
size | Dump size |
|---------------------------+-----------+------------+---------------+-----------|
| Empty content            | 0m7.436s | 0m0.089s  | 680
kBÂ Â Â Â Â Â Â | 66KÂ Â Â Â Â Â |
| bytea                    | 1m5.771s | 20m40.842s | 573
MBÂ Â Â Â Â Â Â | 47MÂ Â Â Â Â Â |
| large object with rule   | 5m26.254s | 21m7.695s | 680
kBÂ Â Â Â Â Â Â | 47MÂ Â Â Â Â Â |
| large object with trigger | 5m13.718s | 20m56.195s | 680
kBÂ Â Â Â Â Â Â | 47MÂ Â Â Â Â Â |
It would thus appear that there's a slight edge for dumping
bytea, but nothing super-amazing. Deleting, however, is still
much faster with bytea than large objects.
I've put my benchmark code up on GitHub for people to run and play
with, to see if they can reproduce my results:
https://github.com/reuven/pg-delete-benchmarks
Reuven
--
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner
В списке pgsql-general по дате отправления: