Обсуждение: Very slow bytea extraction
Hi, before writing to this list I've tried to solve the problem discussing it in the pgsql-performance mailing list but I wasn't able to solve it. The problem is as follows. My test db only contains a single table with some varying-text field and a bytea field. When inserting data into this table everyting works fine. The problem is that it takes too much time for the db to answer a simple select query on the bytea field. The test table has 36 records and each bytea field contains a 250KB object. A (SELECT byteafield FROM table) takes more than 1 minute to execute. The table is called FILE Here is the output of explain analyze SELECT * FROM "FILE" "Seq Scan on "FILE" (cost=0. 00..1.36 rows=36 width=235) (actual time=0.023..0.107 rows=36 loops=1) Total runtime:0.337 ms". I'm accessing the db with jdbc but the problem persists even with psql or pgAdmin. Postgresql version: 8.2.3 OS: WIN XP (32-bit) Thanks in advance for your help Massimo
"msmbarabino@virgilio.it" <msmbarabino@virgilio.it> writes:
> The test table has 36 records and each bytea field
> contains a 250KB object. A (SELECT byteafield FROM table) takes more
> than 1 minute to execute.
Hm, it takes about 4 seconds here, on a fairly old and slow machine:
$ time psql -A -c "select * from file" regression | wc
38 33699 26653873
real 0m4.00s
user 0m1.37s
sys 0m0.62s
In psql the trick is to use unaligned display mode, else psql itself
eats a whole lot of time trying to nicely format those 250KB rows:
$ time psql -c "select * from file" regression | wc
40 33700 28134681
real 0m57.10s
user 0m53.55s
sys 0m0.67s
I surmise that your problem is likewise on the client side, but you
weren't very specific about what client code you were using. Look
for bottlenecks associated with processing of very wide rows...
regards, tom lane
>In psql the trick is to use unaligned display mode, else psql itself >eats a whole lot of time trying to nicely format those 250KB rows: Even using unaligned display mode (\a) it still takes more than 1 minute > >I surmise that your problem is likewise on the client side, but you >weren't very specific about what client code you were using. Look >for bottlenecks associated with processing of very wide rows... Every client I use is affected by the problem. The main client is a java program that connects to the db through jdbc. Even writing a simple program that only performs a select query doesn't solve the problem. A select query issued using pgAdmin takes the same time and so does the show data command in pgAdmin Maybe this is a problem related to the win32 version of postgres (I haven't tried to run this db under linux) Regards Massimo
"msmbarabino@virgilio.it" <msmbarabino@virgilio.it> writes:
>> In psql the trick is to use unaligned display mode, else psql itself
>> eats a whole lot of time trying to nicely format those 250KB rows:
> Even using unaligned display mode (\a) it still takes more than 1
> minute
Hmm ... what does VACUUM VERBOSE of that table show?
regards, tom lane
>Hmm ... what does VACUUM VERBOSE of that table show? > Here is the output of VACUUM VERBOSE INFO: vacuuming "public.FILE" INFO: scanned index "FILE_pkey" to remove 54 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.01 sec. INFO: "FILE": removed 54 row versions in 5 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "FILE_pkey" now contains 34 row versions in 2 pages DETAIL: 54 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "FILE": found 54 removable, 34 nonremovable row versions in 5 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 5 pages contain useful free space. 0 pages are entirely empty. CPU 0.00 s/0.00u sec elapsed 0.07 sec. INFO: vacuuming "pg_toast. pg_toast_45181" INFO: index "pg_toast_45181_index" now contains 16608 row versions in 51 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0. 00u sec elapsed 0.26 sec. INFO: "pg_toast_45181": found 0 removable, 16608 nonremovable row versions in 4 153 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 2 pages contain useful free space. 0 pages are entirely empty. CPU 0.23 s/0.07u sec elapsed 4.93 sec. VACUUM Regards Massimo
"msmbarabino@virgilio.it" <msmbarabino@virgilio.it> writes:
>> Hmm ... what does VACUUM VERBOSE of that table show?
> Here is the
> output of VACUUM VERBOSE
Well, that eliminates the theory that you've got a lot of dead tuples
slowing it down. I come back to the conclusion that it's probably a
client-side problem. Have you tried watching with "top" or similar tool
to check which process is eating all the time?
regards, tom lane
> >Well, that eliminates the theory that you've got a lot of dead tuples >slowing it down. I come back to the conclusion that it's probably a >client-side problem. Have you tried watching with "top" or similar tool >to check which process is eating all the time? > No, actually I haven't yet. I have noticed that during the time the query executes there is neither so much CPU load nor network (communication with db) load. The client seems to hang, then after a minute or so the query returns data. I also thought this problem could be related to db encoding but changing it didn't help at all. If it's a client-related problem, I can't understand why such different clients behave the same way Regards Massimo
> >No, >actually I haven't yet. >I have noticed that during the time the query >executes there is neither so much CPU load nor network (communication >with db) load. The client seems to hang, then after a minute or so the >query returns data. >I also thought this problem could be related to db >encoding but changing it didn't help at all. >If it's a client-related >problem, I can't understand why such different clients behave the same >way Checking Windows Task Manager, after the query has been submitted, none of the postgresql processes or client processes seems to be using CPU. The only process that uses CPU is the System Idle Process (98 % CPU usage). Both client and server seem to be doing nothing. Regards Massimo