[postgres-xl-general] Truncating Tables >1M records takes a long time

Matthew Tamayo-Rios matthew.t.rios at gmail.com
Wed Nov 22 16:52:33 PST 2017


We are using prepared statements to insert data using the JDBC driver.

> public void storeAll( Map<K, V> map ) {
>     K key = null;
>
>     try ( Connection connection = hds.getConnection();
>             PreparedStatement insertRow = prepareInsert( connection ) ) {
>
>         for ( Entry<K, V> entry : map.entrySet() ) {
>             key = entry.getKey();
>             bind( insertRow, key, entry.getValue() );
>             insertRow.addBatch();
>         }
>
>         insertRow.executeBatch();
>     } catch ( SQLException e ) {
>         logger.error( "Error executing SQL during store all for key {}", key, e );
>     }
> }
>
> Assuming that I understand what you mean by backend, the backend is a Java
microservice that is copying over row by row and it runs for maybe 20 - 30
minutes. It is not currently running and hasn't been for over 24 hours.

=> select * from pg_prepared_xacts;
 transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)

Also off topic, but that linked to this issue: http://lists.postgres-
xl.org/pipermail/postgres-xl-developers-postgres-xl.org/
2017-November/000570.html

If we have spare cycles we'll take a look and see if its a spot where we
can contribute. Setting up an ipsec mesh is a PITA.
ᐧ

On Wed, Nov 22, 2017 at 6:12 PM, Tomas Vondra <tomas.vondra at 2ndquadrant.com>
wrote:

> Hi,
>
> On 11/22/2017 11:32 PM, Matthew Tamayo-Rios wrote:
> > I'm using PGXL 9.5r1.6, based on PG 9.5.8 (Postgres-XL 9.5r1.6)
> >
> > Also, it looks like something is broken with delete in general-- I tried
> > deleting a single row and it is hanging indefinitely. GTM seems to be
> > fine and all other operations (select, insert, update) seem to be
> > working fine. In fact, after doing some more troubleshooting (detailed
> > below) it seems that deleting even a single row from the very large
> > tables hangs indefinitely making the only way to clear them dropping the
> > entire database (which works).
> >
> > Delete hangs even on a single id for large tables.
> >
> >     delete from ids where id = '30095031-49e0-44eb-87a9-98b640773ef2';
> >
> >
> > Log from coordinator just has:
> >
> >     STATEMENT:  delete from ids where id =
> >     30095031-49e0-44eb-87a9-98b640773ef2;
> >
> >
> > Data node has no log entries;
> >
> > Successfully created a test table and deleted a record from it:
> >
> >     openlattice=> create table test( id uuid, c1 int, c2 int, PRIMARY
> >     KEY (id) ) distribute by hash(id);
> >     CREATE TABLE
> >     => insert into test VALUES( '30095031-49e0-44eb-87a9-98b640773ef2',
> >     21, 23 );
> >     INSERT 0 1
> >     => insert into test VALUES( '30095031-49e0-55eb-87a9-98b640773ef2',
> >     21, 23 );
> >     INSERT 0 1
> >     => delete from test where id = '30095031-49e0-44eb-87a9-
> 98b640773ef2';
> >     DELETE 1
> >
> >
> > I also checked locks and I am seeing a bunch of prepared statements
> > blocking each other, but no inserts have been done in 24 hours.
> >
> > blocked_pid | blocked_user | blocking_pid | blocking_user |
> >       blocked_statement                     |
> > current_statement_in_blocking_process
> > -------------+--------------+--------------+---------------+
> ----------------------------------------------------------+-
> ---------------------------------------------------------
> >        95377 |                |       95434 |                   | INSERT
> > INTO ids VALUES ($1, $2, $3, $4)                  | INSERT INTO ids
> > VALUES ($1, $2, $3, $4)
> >        95377 |                |       95405 |                   | INSERT
> > INTO ids VALUES ($1, $2, $3, $4)                  | INSERT INTO ids
> > VALUES ($1, $2, $3, $4)
> >        95377 |                |       95378 |                   | INSERT
> > INTO ids VALUES ($1, $2, $3, $4)                  | INSERT INTO ids
> > VALUES ($1, $2, $3, $4)
> >
> > I omitted user above. So it looks like tables with heavy write usage are
> > getting blocked by prepared statements holding onto locks for too long.
> > Any ideas on what would cause that? These tables are being copied from
> > cassandra via a short lived ETL process in preparation for a migration
> > so their shouldn't be locks being held.
> >
>
> So you're doing the inserts using prepared statements? I wonder if that
> might be preventing restriction to subset of nodes (if we don't know the
> value, we can't compute the hash and restrict the list of nodes).
>
> Also, what do the backends holding the locks do? Are they waiting for
> some other lock (in that case, what does the backend holding that lock
> do), or are they stuck for some other reason?
>
> What does pg_stat_activity say?
>
> Can you attach gdb to the stuck backends and get the backtrace? That is,
> something like 'gdb -p $PID' and then 'bt'. I suspect it might be the
> GTM issue I reported a while back (see the stack at the beginning):
>
> http://lists.postgres-xl.org/pipermail/postgres-xl-
> developers-postgres-xl.org/2017-November/000571.html
>
> Another idea is that perhaps there are some old prepared transaction.
> Can you check pg_prepared_xacts on the nodes?
>
> regards
>
> --
> Tomas Vondra                  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20171122/442c6e10/attachment.htm>


More information about the postgres-xl-general mailing list