Thread: Index creation running now for 14 hours

On Wed, Aug 26, 2015 at 1:26 PM, Tory M Blue <tmblue@gmail.com> wrote:
>
> Right now the 100% cpu process which is this index is only using 3.5GB
> and has been for the last 15 hours
>

If 100% cpu, you can do 'sudo perf top' to see what the CPU is busy about.

Regards,
Qingqing

Hi,

On 08/26/2015 11:53 PM, Tory M Blue wrote:
>
>
> On Wed, Aug 26, 2015 at 2:45 PM, Qingqing Zhou
> <zhouqq.postgres@gmail.com <mailto:zhouqq.postgres@gmail.com>> wrote:
>
>     On Wed, Aug 26, 2015 at 1:26 PM, Tory M Blue <tmblue@gmail.com
>     <mailto:tmblue@gmail.com>> wrote:
>      >
>      > Right now the 100% cpu process which is this index is only using
>     3.5GB
>      > and has been for the last 15 hours
>      >
>
>     If 100% cpu, you can do 'sudo perf top' to see what the CPU is busy
>     about.
>
>     Regards,
>     Qingqing
>
>
>
> I appreciate the attempted help, but I know what it's doing, it's
> creating indexes for the last 14+ hours.

Sure, but what exactly was it doing? 'perf top' might give us a hint
which function is consuming most of the time, for example.

 > I've killed it now,  as it was
> about to run my machine out of disk space, stopped it at 97% full, could
> not go any longer.

Which suggests it's using a lot of temp files.

Indexes are built by reading all the necessary data from the table (just
the columns), sorted and then an index is built using the sorted data
(because it can be done very efficiently - much faster than when simply
inserting the tuples into the btree index).

The fact that you ran out of disk space probably means that you don't
have enough space for the sort (it clearly does not fit into
maintenance_work_mem), and there's no way around that - you need enough
disk space.

> I will now clean up the table a bit but will still have 500million rows
> with 6 indexes on it. I will create the indexes after the data is laid
> down vs during,  so it doesn't block my other table replications. I will
> then fire off my index creations in parallel  for my other tables so I
> can actually use the hardware the DB is sitting on.

That's a very bad idea, because each of the index builds will require
disk space for the sort, and you're even more likely to run out of disk
space.

>
> But I guess the answer is, no real way to tell what the box is doing
> when it's creating an index. Yes there was a lock, no I could not find a
> way to see how it's progressing so there was no way for me to gauge when
> it would be done.

Had it been waiting on a lock, it wouldn't consume 100% of CPU.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 08/26/2015 10:26 PM, Tory M Blue wrote:

>
> the table is 90GB without indexes,  285GB with indexes and bloat,  The
> row count is not actually completing.. 125Million rows over 13 months,
> this table is probably close to 600million rows.

You don't need to do SELECT COUNT(*) if you only need an approximate
number. You can look at pg_class.reltuples:

    SELECT reltuples FROM pg_class WHERE relname = 'impressions';

That should be a sufficiently accurate estimate.

> The above is when it had finished copying the table and started on the
> index..
>
> Well as I said I'm running out of storage as the index is creating some
> serious data on the filesystem, I'll have to kill it, try to massage the
> data a bit and increase the maintenance_work mem to use some of my 256GB
> of ram to try to get through this. Right now the 100% cpu process which
> is this index is only using 3.5GB and has been for the last 15 hours

Please post details on the configuration (shared_buffer, work_mem,
maintenance_work_mem and such).

BTW while the the CREATE INDEX is reporting 3.5GB, it most likely wrote
a lot of data into on-disk chunks when sorting the data. So it's
actually using the memory through page cache (i.e. don't increase
maintenance_work_mem too much, you don't want to force the data to disk
needlessly).

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, Aug 26, 2015 at 3:36 PM, Tomas Vondra
<tomas.vondra@2ndquadrant.com> wrote:
>> But I guess the answer is, no real way to tell what the box is doing
>> when it's creating an index. Yes there was a lock, no I could not find a
>> way to see how it's progressing so there was no way for me to gauge when
>> it would be done.
>
>
> Had it been waiting on a lock, it wouldn't consume 100% of CPU.

When things are going out to disk anyway, you're often better off with
a lower maintenance_work_mem (or work_mem). It's actually kind of
bogus than run size is dictated by these settings. Reducing it will
tend to make tuplesort's maintenance of the heap invariant
inexpensive, while not really making the merge phase more painful. I
would try 128MB of maintenance_work_mem. That could be significantly
faster. Check how the I/O load on the system compares with a higher
maintenance_work_mem setting. Often, this will make the sort less CPU
bound, which is good here.

I am currently working on making this a lot better in Postgres 9.6.
Also, note that text and numeric sorts will be much faster in 9.5.

Of course, as Tomas says, if you don't have the disk space to do the
sort, you're not going to be able to complete it. That much is very
clear.

If you're really worried about these costs, I suggest enabling
trace_sort locally, and monitoring the progress of this sort in the
logs.

--
Regards,
Peter Geoghegan