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

On Tue, Oct 11, 2016 at 2:17 PM, Francisco Olarte <folarte(at)peoplecall(dot)com>
wrote:

> On Thu, Oct 6, 2016 at 12:36 AM, David A <david(at)scalaacademy(dot)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(at)postgresql(dot)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.