Vertica is slow
Hi
We have 36 node cluster and few hours in a day system is very slow and it takes atleast 30 min to load a file (50MB) to table. Usually it take 2 mins. During this time, could notice performance hit in all the queries too.
like Select count(*) from table etc
We have 20% free disk space in all the nodes. This performance hit happens few times in a day.
Can you please suggest
1
Comments
Vertica is slow? That's surely a contradiction in terms
So, have you looked at what is running during that time frame? That is, check the SESSIONS table, in particular the CURRENT_STATEMENT column. Any long running queries?
Are you using user defined resource pools, or is everyone hogging up the "general" pool?
It is contradicting. It is slow sometimes but not always.
From the sessions table, I could see below query which is running since past 2 days and several sessions are created for same query
'select * from (select 'GLOBAL TEMPORARY' as table_type union all select 'LOCAL TEMPORARY' as table_type union all select 'SYSTEM TABLE' as table_type union all select 'TABLE' as table_type union all select 'VIEW' as table_type order by table_type) as vmd'
What user (user_name) and application (client_type, client_os_user_name) is issuing that query? Seems like a lookup query to populate a drop down list box in an app of some sort.
Any word on this?
I think it maybe coming from a DB client (perhaps a GUI app). We are running into the same issue. Trying to track it down.
Hi, the original query appears to be selecting info on tables and views from the catalog. If that is the case, check for running DDL statements, and also check the LOCKS table for X lock caused by a concurrent DDL statement. Some DDL statements will request eXclusive locks on the catalog and block other queries from running.
@tvictor - Can you be more specific?
Sorry for this very late reply. I either didn't get or didn't see notification on this reply. Anyways, we figured out the issue here. There was a user running a DB UI client (SQL Workbench). This client taking an X lock on the target table to which Vertica Kafka connector was trying to insert (I lock). Killing the offending session relieved the problem. Before killing the session I captured the current query for that session, and it was this ridiculous thing:
select * from
(
select 'GLOBAL TEMPORARY' as table_type union all
select 'LOCAL TEMPORARY' as table_type union all
select 'SYSTEM TABLE' as table_type union all
select 'TABLE' as table_type union all
select 'VIEW' as table_type
order by table_type
) as vmd
It doesn't do anything useful and its bizarre that something would even run this on a database. But it looks like some DB UI clients do this sort of thing. https://community.oracle.com/thread/3682300
I'm guessing the UI client ran some query before (or maybe after this) in a transaction but then since this query was stuck it didn't close the transaction? Not sure, just conjecturing based on what I saw.