Home > mailing lists

Thread: My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

From

David A

Date:

11 October 2016, 19:28:37

Hi,

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)

Instance RAM: 60GB
Instance CPU: 16Cores

I'm just wondering - anyone has any thoughts or suggestions about performance?

Any would be much appreciated

(I'm wandering if I just need faster instances/disks or if I'm doing something wrong)

Any one has similar DB sizes? what CPU/RAM/Disks do you have?

Thanks,

David

Scala Academy

Re: My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

From

Francisco Olarte

Date:

11 October 2016, 21:18:35

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.

Re: My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

From

Melvin Davidson

Date:

11 October 2016, 21:33:49

On Tue, Oct 11, 2016 at 2:17 PM, Francisco Olarte <folarte@peoplecall.com> wrote:

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

You have stated a very generic problem and not provided much detail.

Perhaps you could be a "little" more specific and give needed info we might be able to help.

IE:
Which version of PostgreSQL?

What are the memory parameters in postgresql.conf (shared_memory, max_connections, work_mem, maintenance_work_mem, etc..) ?

How big are the tables being moved?

What type of disks

etc.

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

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

From

Rakesh Kumar

Date:

11 October 2016, 22:01:17

>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.

Re: My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

From

Lucas Possamai

Date:

11 October 2016, 22:13:49

Instance RAM: 60GB
Instance CPU: 16Cores

I got a 3TB Postgres Database running with a Dual Socket Octo Core Intel Xeon E5-2630v3 with 128GB of RAM and SATA disks.

I think yes, you could improve your server's RAM. However Melvin is right, there is no enough information.

Lucas

Re: My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

From

Francisco Olarte

Date:

11 October 2016, 22:14:40

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.

Re: My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

From

Francisco Olarte

Date:

11 October 2016, 22:17:14

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.

Re: My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

From

Melvin Davidson

Date:

11 October 2016, 22:51:15

On Tue, Oct 11, 2016 at 3:16 PM, Francisco Olarte <folarte@peoplecall.com> wrote:

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.

>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.
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.

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: My DB has has 5TB, many operations are very slow (on Google Cloud Compute)

From

Francisco Olarte

Date:

12 October 2016, 15:13:45

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.