On 06/24/2011 02:55 PM, Shaun Thomas wrote:
On 06/24/2011 11:18 AM, Greg Smith wrote:
sync=14525.296 s, total=14786.868 s
What!? 6% of 8GB is just shy of 500MB. That's not a small
amount, exactly, but it took 14525 seconds to call syncs for those
writes? What kind of ridiculous IO w
On 06/24/2011 11:18 AM, Greg Smith wrote:
sync=14525.296 s, total=14786.868 s
What!? 6% of 8GB is just shy of 500MB. That's not a small
amount, exactly, but it took 14525 seconds to call syncs for those
writes? What kind of ridiculous IO would cause something like that?
That's even
I called VACUUM on the database after abandoning the original all-in-one
update.
And I have a daily cron script that executes the following statement:
sudo -u postgres /usr/bin/vacuumdb -U postgres --all --analyze
But I had not considered using CLUSTER - I will certainly look into that.
Thank
Harry Mantheakis wrote:
> I have stopped the all-in-one-go update from executing, and now I
> am executing a series of statements, each constrained to update no
> more than 100,000 records at a time.
>
> Interesting fact: updating 100,000 rows takes 5 seconds. Quick.
One last thing -- all the
> try a series of smaller examples...
Mark, that was the tip the saved me!
Many thanks.
Harry Mantheakis
London, UK
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Thank you so much for all the feedback, Kevin - much appreciated.
I have stopped the all-in-one-go update from executing, and now I am
executing a series of statements, each constrained to update no more
than 100,000 records at a time.
Interesting fact: updating 100,000 rows takes 5 seconds.
On 06/24/2011 10:43 AM, Devrim GÜNDÜZ wrote:
Has anyone measured the cost of creating empty WAL segments while the
database is running?
Actually, when is the new file created? Just after one segment is filled
up, or some time before then? What happens during WAL segment creation?
If there are pe
Harry Mantheakis wrote:
>> It will be a lot faster if you can drop all indices...
>
> This is counter-intuitive - because the WHERE clause is matching
> the only two indexed fields, and my understanding is that querying
> on indexed fields is faster than querying on fields that are not
> indexe
Hi,
Has anyone measured the cost of creating empty WAL segments while the
database is running?
Actually, when is the new file created? Just after one segment is filled
up, or some time before then? What happens during WAL segment creation?
If there are pending transactions to be committed, do w
Harry Mantheakis wrote:
> IOW how do I convert - guesstimate! - these numbers into
> (plausible) time values?
They are abstract and it only matters that they are in the right
ratios to one another so that the planner can accurately pick the
cheapest plan. With the default settings, seq_page_c
Mark Thornton wrote:
> On 23/06/11 16:05, Harry Mantheakis wrote:
>> UPDATE
>> table_A
>> [ ... ]
>> FROM
>> table_B
>> WHERE
>> table_B.id = table_A.id
> I frequently get updates involving a FROM clause wrong --- the
> resulting table is correct but the running time is quadratic.
The most
On 23/06/11 16:05, Harry Mantheakis wrote:
Hello
I am attempting to run an update statement that copies two fields from
one table to another:
UPDATE
table_A
SET
(
field_1
, field_2
) = (
table_B.field_1
, table_B.field_2
)
FROM
table_B
WHERE
table_B.id = table_A.id
;
I frequently get
Thanks again Claudio.
It does not look like its locked/blocked - but your hint about doing
this task in smaller batches is a good one, and it would be easy enough
to organise.
I am going to let this task run over the week-end, and then decide.
Either way, I shall update this thread.
Much o
24.06.11 14:16, Harry Mantheakis написав(ла):
> EXPLAIN the statement
Here is the EXPLAIN result:
--
QUERY PLAN
--
Hash Join (cost=2589312.08..16596998.47 row
On Fri, Jun 24, 2011 at 1:19 PM, Harry Mantheakis
wrote:
>
>> there are lots of ways in which it could be struggling...
>
> I have been monitoring the server with IOSTAT -d and IOSTAT -c and I cannot
> see anything alarming.
If iostat doesn't show disk load, either iostat doesn't work well
(which
Thank you Claudio.
> there are lots of ways in which it could be struggling...
I have been monitoring the server with IOSTAT -d and IOSTAT -c and I
cannot see anything alarming.
> It will be a lot faster if you can drop all indices...
This is counter-intuitive - because the WHERE clause is m
Thank you Kevin.
> SET field_1 = table_B.field_1, field_2 = table_B.field_2
I will try that, if I have to next time.
> add something like this toyour WHERE clause,
> to prevent unnecessary updates:
>
> AND (table_B.field_1 IS DISTINCT FROM table_A.field_1
>OR table_B.field_2 IS DISTINCT F
17 matches
Mail list logo
