Re: Question on disk contention

From: Fabio Pardi <f(dot)pardi(at)portavita(dot)eu>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Question on disk contention
Date: 2018-05-31 14:20:31
Message-ID: f6eaddef-15ba-9343-f487-8293a8fb451c@portavita.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

As far as I know, the OS cache is shared, and shared_buffers too.

Back to the matter of contention, your statement i think might be true only in cases when you are querying data which does not fit in RAM.

Under those circumstances, the OS or Postgres might need to evict blocks from RAM to make room to new blocks fetched from disk, while the concurrent query is trying to do the same. Then the disk contention might happen.

regards,

fabio pardi

On 31/05/18 16:09, Melvin Davidson wrote:
>
>
> On Thu, May 31, 2018 at 10:04 AM, Ron <ronljohnsonjr(at)gmail(dot)com <mailto:ronljohnsonjr(at)gmail(dot)com>> wrote:
>
> On 05/31/2018 08:52 AM, Melvin Davidson wrote:
>>
>>
>> On Thu, May 31, 2018 at 1:13 AM, Charles Clavadetscher <clavadetscher(at)swisspug(dot)org <mailto:clavadetscher(at)swisspug(dot)org>> wrote:
>>
>> Hi Melvin
>>
>> As an answer to a previous post you wrote:
>>
>> "Also, your main problem is that when you have two exact same queries executing at the same time, they will cause contention in
>> the disk, and neither one will make much progress."
>>
>> Could you elaborate a little more on the meaning of "contention in the disk"?
>> What is it that happens?
>>
>> Thank you and have a good day.
>> Regards
>> Charles
>>
>>
>>
>> >Could you elaborate a little more on the meaning of "contention in the disk"?
>> >What is it that happens?
>>
>> To simplify, you have two users/jobs, both wanting the exact same information. So the system instructs the disk to get
>> that information from the disk, which causes the disk head to "seek" to the position of the first eligible row and
>> continues positioning to other eligible rows. Now the job is not exclusive, so the system temporarily switches to the
>> other job, which causes the disk to go back to the first row and work from there. The switching back and forth continues,
>> so that instead of one job finishing quickly, they both have to take turns waiting for needed information. That takes
>> a lot longer,
>>
>> Try this, Select a table that has a lot of rows, ideally 1M+. Then start a query with a WHERE clause and see how long
>> it takes. Then submit the same query from 5 separate connections simultaneously and see how long that takes.
>
> Why isn't the OS caching the disk blocks, and why isn't Postgres using the cached data?
>
>
> --
> Angular momentum makes the world go 'round.
>
>
>
>> Why isn't the OS caching the disk blocks, and why isn't Postgres using the cached data?
> It does, but the cache is for each connection/job. They are not shared.
> --
> *Melvin Davidson**
> Maj. Database & Exploration Specialist**
> Universe Exploration Command – UXC***
> Employment by invitation only!

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dennis Gearon 2018-05-31 14:22:00 Re: Insert UUID GEN 4 Value
Previous Message Igor Neyman 2018-05-31 14:16:23 RE: Question on disk contention