Vertica is slow

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.