RE: Question on disk contention

Hi Melvin

From: Melvin Davidson [mailto:melvin6925(at)gmail(dot)com]
Sent: Donnerstag, 31. Mai 2018 15:53
To: Charles Clavadetscher <clavadetscher(at)swisspug(dot)org>
Cc: pgsql-generallists.postgresql.org <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Question on disk contention

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.

Thank you very much for your answer.

Regards

Charles

--

Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!