From: Tory M Blue [mailto:tmb...@gmail.com] Sent: Wednesday, August 26, 2015 3:26 PM To: Igor Neyman <iney...@perceptron.com> Cc: pgsql-performance <pgsql-performance@postgresql.org> Subject: Re: [PERFORM] Index creation running now for 14 hours
On Wed, Aug 26, 2015 at 12:18 PM, Igor Neyman <iney...@perceptron.com<mailto:iney...@perceptron.com>> wrote: From: pgsql-performance-ow...@postgresql.org<mailto:pgsql-performance-ow...@postgresql.org> [mailto:pgsql-performance-ow...@postgresql.org<mailto:pgsql-performance-ow...@postgresql.org>] On Behalf Of Tory M Blue Sent: Wednesday, August 26, 2015 3:14 PM To: pgsql-performance <pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>> Subject: [PERFORM] Index creation running now for 14 hours I'm running 9.3.4 with slon 2.2.3, I did a drop add last night at 9pm, it started this particular tables index creation at 10:16pm and it's still running. 1 single core is at 100% (32 core box) and there is almost zero I/O activity. CentOS 6.6 16398 | clsdb | 25765 | 10 | postgres | slon.remoteWorkerThread_1 | 10.13.200.232 | | 45712 | 2015-08-25 21:12:01.6 19819-07 | 2015-08-25 21:22:08.68766-07 | 2015-08-25 22:16:03.10099-07 | 2015-08-25 22:16:03.100992-07 | f | active | select "_cls".fini shTableAfterCopy(143); analyze "torque"."impressions"; I was wondering if there were underlying tools to see how it's progressing, or if there is anything I can do to bump the performance mid creation? Nothing I can do really without stopping postgres or slon, but that would start me back at square one. Thanks Tory i Check pg_locks in regards to the table in question. Regards, Igor Neyman thanks Igor I did, but not clear what that is telling me, there are 249 rows in there, nothing has a table name , they are all for the PID in the "analyze torque.impressions line that I listed above pid 25765. Here is one for an exclusive lock, but what should I be looking for? There are no other processes on this box other than slon and this index creation. transactionid | | | | | | 93588453 | | | | 4/25823460 | 25765 | ExclusiveL ock | t | f Thanks Tory There are objects OIDs in pg_lock, not names. Find the OID of the table that you create your index for, and search pg_locks for the records referencing your table. It cannot be that all records in pg_locks are for pid running “analyze”, there should be records with pid running your “create index”. What’s the size of the table you are indexing? Also, take a look at pg_stat_activity for long running transactions/queries. Igor Neyman
