Global Catalog X lock issues
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
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
0
Comments
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
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
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) .