Global Catalog X Locks on Copy

[honkytonk]

Hi,

We have a process which attempts to create around 150 sessions at the same time with each session inserting a small amount of rows (say around 40) into one of 5 tables. It's currently taking around 5 mins for these inserts to complete.

The copy command being used initially had the DIRECT keyword, however have since taken that off which has helped I/O but I think the issue is with an X lock that gets taken out on the Global Catalog. When I do a select * from locks I see all the copy statements waiting on these X locks - anything I can do reduce this locking time? The devs don't want to batch up the rows and therefore less COPY commands, but is this the only way to go?

We're running Vertica 6.1.3-3 on a 7 node cluster.

Thanks
Mark

[scutter]

Are these loads using sequences with a small cache? That can cause lots of lock contention.

—Sharon

[id10t]

Hi!

[DELETED]

[honkytonk]

Apologies I hadn't realised this thread had been replied to:

No sequences in use and yes it's up to 150 separate sessions so 150 rows in the sessions table. The default parameter had been increased.

We ended up changing the logic so we had fewer sessions copying more rows each. This removed the X lock waits