Обсуждение: My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

On Thu, Oct 6, 2016 at 12:36 AM, David A <david@scalaacademy.com> wrote:
> My DB has has 5TB, it's on Google Cloud Compute, mostly on SSDs, part on
> Standard Persistent Disks.
> Querying is ok, but deleting, moving between tablespaces, dropping tables,
> etc, etc is hugely slow
> (note: I do have a number of indexes)

Are you sure it's slow and not just lengthy? Hostings tend to have and
publish limits on their bandwidths and similar things, and may be you
are just saturating your capacity. If you can at least compare the
time to move a tablespace against the time of copying a similarly
sized file between the same disks someone may be able to say
something.

> Instance RAM: 60GB
> Instance CPU: 16Cores

Cores do not help, postgres is single-threaded. RAM MAY help, but I
suspect your operations are IO bound. Of course, with the sparseness
of the details, one can not say too much.

Francisco Olarte.

>Cores do not help, postgres is single-threaded. RAM MAY help, but I

I hope this is no longer true from 9.6 for those queries where PG can use parallelism.

>suspect your operations are IO bound. Of course, with the sparseness
>of the details, one can not say too much.

Melvin:

On Tue, Oct 11, 2016 at 8:33 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

> FYI, moving between tablespaces requires an exclusive table lock, so it's naturally going to be slow.

Requiring and exclusive table lock does not imply slownes. Just try
'lock table x in exclusive mode' on an idle system. Pretty fast.

The lock may lengthen the operation due to potential delays.

And moving between tablespaces on a big db may well be a fast ( speed
) but long ( time ) process, lots of things have to be done, but they
can be done rather fast. But even if light is fast, it still needs 550
years to reach Antares. You can have a short slow operation ( like
needing a second to get an indexed tuple ) or a fast long one ( like
needing an hour to move a petabyte of data ).

To judge if its going slow we will need a reference, like, how much
time does it take to copy and sync a big uncached file between the
affected volumes. If move does say, 1.5 times slower I wouldn't say it
is that slow ( given copy is optimized for this kind of transfers and
a database not so much ).

Francisco Olarte.

Rakesh:

On Tue, Oct 11, 2016 at 9:00 PM, Rakesh Kumar
<rakeshkumar464@outlook.com> wrote:
>>Cores do not help, postgres is single-threaded. RAM MAY help, but I
> I hope this is no longer true from 9.6 for those queries where PG can use parallelism.

It does, AFAIK, but for queries, not AFAIK for this kind of data
moving ops ( and I doubt it will, as presently you can easily saturate
the channels with a single core for that kind of simple ops, and
normally if you want to optimize this kind of op is better to target
concurrency ( table can be used while moving ) than pure speed .

Francisco Olarte.

Melvin:

On Tue, Oct 11, 2016 at 9:50 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
> >Requiring and exclusive table lock does not imply slownes. Just try
> >'lock table x in exclusive mode' on an idle system. Pretty fast.
> Sure on an idle system, you will get a table lock right away, but OP's statements imply a large busy system.
May be OP, but not PP ( previous poster ).

Had you not merged two replies in one and pruned the context too much,
you could have read yourself ( not too sure, maybe I have my local
copies of mail borked and it was other person ) saying, just before
this:

> FYI, moving between tablespaces requires an exclusive table lock, so it's naturally going to be slow.

English is not my mother tongue, but this seems to imply slowness
being blamed on the table lock, maybe someone more knowledgeable in
the finer details of english language can explain it for to me if it
is not the case.


> And if there are transactions occurring against that table, there is no telling how long it will take.
> Since we do not have enough specific info, I stand by my statement.

I would not expect less. I do not remember where the OP stated a busy
system, but anyway the lock is going to execute fast and but with a
long delay, and counting the time form the issuing of the command to
the time of end is a perfectly reasonable way to do it.

Anyway, ok, exclusive locks cause the slownes.

Francisco Olarte.