Global Catalog X lock issues

Mark_1

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 this X lock - 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

John_1

Hi Mark, Sorry got late to this post. Please check if analyze_histogram is running as this process holds Locks on the Global Catalog, potentially for a long time. Removing this batch job may help

John

Daniel_Leybovic

Hi John!

Very nice that you are reading old questions too, +100500
but... topic starter didn't updated his topic (bad practice, will not get answers any more). Topic starter already found an work around for his issue.
 http://vertica-forums.com/viewtopic.php?f=3&t=1805&p=6125

Mark_1

Thanks John - Good to know will bear in mind if I hit this again in future. To be honest I forgot I posted here as well as on the forum - hence why I updated the forum post. Mark

eli_revach_1

Hi ,
Their is nothing you can do to improve it , you need bigger transactions and less concurency ( this is probebly what you done ) , vertica is not OLTP database . 
The Global catalog lock is related to commit that need to be sync on each one of the catalogs your cluster have . Vertica note like high concurency of commits ( this improve when you have  bigger transactions and less concurency) .

Ben_Vandiver

Version 7.0 should demonstrate better commit performance; it holds the global catalog X lock for a shorter duration for each commit.