Hi! We recently migrated an analytics software to Vertica and it seems our setup has issues with concurring requests / queries. We use lots of AJAX widgets and thus have usually about 15-30 concurring queries at a time. The higher the concurrency is, the longer all queries take. We are looking for a hint on how to reduce the query time. A quite basic query like:
SELECT * FROM account
runs in less than 50ms, at a concurrency of 25 (same query) the last query takes more than 500ms, at a concurrency of 50 it goes up to over 1300ms and so on. We played around with the resource pool configuration and found that for this test case a "Max Concurrency" of 1 is the fastest way (less than 900ms with 50 concurrent queries) but this still feels way too much, especially for such a basic query.
When we tested it with a more complex query that normally takes less than 350ms to execute, at a concurrency of 25 we get the first result after around 4000ms and the last after almost 10 seconds with "Max Concurrency" set to 1.
The box is a 8 core all SSD server with 16gb of RAM. CPU usage never gets higher than 400%, memory usage is below 10%.
We'd be happy about any input on how to optimize the performance of our setup. Thanks in advance!
Queries very slow at high concurrency
We did some more tests:
vcpuperf is a little slower than good, but this should not be the bottleneck since the box is more bored than anything over all:
We can't find any system bottlenecks on the management console. With 300 concurring queries CPU load never exceeds 50%, everything else doesn't even show an amplitude on the graphs.
vcpuperf is a little slower than good, but this should not be the bottleneck since the box is more bored than anything over all:
Compiled with: 4.1.2 20080704 (Red Hat 4.1.2-55)vioperf is more than good, probably because of the SSD drives, network performance shouldn't matter since we're running the text on the database server itself.
Expected time on Core 2, 2.53GHz: ~9.5s
Expected time on Nehalem, 2.67GHz: ~9.0s
Expected time on Xeon 5670, 2.93GHz: ~8.0s
This machine's time:
CPU Time: 12.490000s
Real Time:12.530000s
Some machines automatically throttle the CPU to save power.
This test can be done in <100 microseconds (60-70 on Xeon 5670, 2.93GHz).
Low load times much larger than 100-200us or much larger than the corresponding high load time
indicate low-load throttling, which can adversely affect small query / concurrent performance.
This machine's high load time: 129 microseconds.
This machine's low load time: 260 microseconds.
We can't find any system bottlenecks on the management console. With 300 concurring queries CPU load never exceeds 50%, everything else doesn't even show an amplitude on the graphs.
+2Most of the resourse pool seting is impact your query memory seting , your problem is cpu , your querys run in parallel and eat your cpu resources , try to play with the max parallel parameter in the redource pool , this will provid you an ability to have more concurent querys with more optimal execution time.
You're kidding, right? :-)
+2No i am not ! ,
I see your CPU stats above , however we face similar situation and overcome it with the parallel setting i advice you to give it a try ( it may related thread management , using parallel setting you reduce their count ) .
I see your CPU stats above , however we face similar situation and overcome it with the parallel setting i advice you to give it a try ( it may related thread management , using parallel setting you reduce their count ) .
Okay eli, thanks for your comment. But as we said stated, we played around with pool configuration already, maybe we didn't make this clear enough. Our problem is that compared to pretty much all other databases, concurrent queries are inacceptably slow and get slower the higher the concurrency is. No matter what pooling we use
Eli, for your suggested parameter, do you mean
- max_concurrency
- execution_parallelism
- other ?
Tx
- max_concurrency
- execution_parallelism
- other ?
Tx
+2execution_parallelism
Hi,
instead of 'select * from' have you tried 'select c1,c2 from' and how does that stack up in concurrency?
instead of 'select * from' have you tried 'select c1,c2 from' and how does that stack up in concurrency?
Yes, the problem remains 
Just a thought but have you seen the MC for review of the cluster utilization etc while your concurrent sessions are active?
We don't have a cluster on this test database. The utilization is generally around max 50%, cpu wise. What MC do you mean? Max Currency?
+2Am I reading your problem correctly that you have a single node? What version of Vertica are you running?
Hi Norbert, we're running 7.1.1 on a single node (test server). Our live server (also 7.1.1 with 3 nodes) has the same problems with concurrency though.
+2How much memory does each query acquire? If you only have 16 GB of memory, and each query acquires 4 GB, you can see your concurrency won't be very high.
The simple query (e.g. SELECT somecolumn FROM account) consumes only a few MB, so that can't be the bottleneck. There's always more than enough free memory available as well as CPU, IO and everything else. At least the console says so
+2Look at the memory acquired under the query_requests table for your query.
request | memory_acquired_mb
------------------------------------------------------+--------------------
SELECT account_id, name FROM account WHERE state = 1 | 100
------------------------------------------------------+--------------------
SELECT account_id, name FROM account WHERE state = 1 | 100
+2Is that the actual query that you're running? It's different from your SELECT * FROM account. If you can, please post the EXPLAIN of the statement.
Yes, sorry. We tried several different queries. Let's stick to the one last mentioned :-) Here goes the EXPLAIN:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------
QUERY PLAN DESCRIPTION:
------------------------------
EXPLAIN SELECT account_id, name FROM account WHERE state = 1;
Access Path:
+-STORAGE ACCESS for account [Cost: 52, Rows: 46] (PATH ID: 1)
| Projection: public.account_tmp_super
| Materialize: account.account_id, account.name
| Filter: (account.state = 1)
------------------------------
-----------------------------------------------
PLAN: BASE QUERY PLAN (GraphViz Format)
-----------------------------------------------
digraph G {
graph [rankdir=BT, label = "BASE QUERY PLAN\nQuery: EXPLAIN SELECT account_id, name FROM account WHERE state = 1;\n\nAll Nodes Vector: \n\n node[0]=v_biddy_node0001 (initiator) Up\n", labelloc=t, labeljust=l ordering=out]
0[label = "Root \nOutBlk=[UncTuple(2)]", color = "green", shape = "house"];
1[label = "NewEENode \nOutBlk=[UncTuple(2)]", color = "green", shape = "box"];
2[label = "StorageUnionStep: account_tmp_super\nUnc: Integer(8)\nUnc: Varchar(255)", color = "purple", shape = "box"];
3[label = "ScanStep: account_tmp_super\n(account.state = 1)\naccount_id\nstate (not emitted)\nname\nUnc: Integer(8)\nUnc: Varchar(255)", color = "brown", shape = "box"];
1->0 [label = "V[0] C=2",color = "black",style="bold", arrowtail="inv"];
2->1 [label = "0",color = "blue"];
3->2 [label = "0",color = "blue"];
}
(27 rows)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------
QUERY PLAN DESCRIPTION:
------------------------------
EXPLAIN SELECT account_id, name FROM account WHERE state = 1;
Access Path:
+-STORAGE ACCESS for account [Cost: 52, Rows: 46] (PATH ID: 1)
| Projection: public.account_tmp_super
| Materialize: account.account_id, account.name
| Filter: (account.state = 1)
------------------------------
-----------------------------------------------
PLAN: BASE QUERY PLAN (GraphViz Format)
-----------------------------------------------
digraph G {
graph [rankdir=BT, label = "BASE QUERY PLAN\nQuery: EXPLAIN SELECT account_id, name FROM account WHERE state = 1;\n\nAll Nodes Vector: \n\n node[0]=v_biddy_node0001 (initiator) Up\n", labelloc=t, labeljust=l ordering=out]
0[label = "Root \nOutBlk=[UncTuple(2)]", color = "green", shape = "house"];
1[label = "NewEENode \nOutBlk=[UncTuple(2)]", color = "green", shape = "box"];
2[label = "StorageUnionStep: account_tmp_super\nUnc: Integer(8)\nUnc: Varchar(255)", color = "purple", shape = "box"];
3[label = "ScanStep: account_tmp_super\n(account.state = 1)\naccount_id\nstate (not emitted)\nname\nUnc: Integer(8)\nUnc: Varchar(255)", color = "brown", shape = "box"];
1->0 [label = "V[0] C=2",color = "black",style="bold", arrowtail="inv"];
2->1 [label = "0",color = "blue"];
3->2 [label = "0",color = "blue"];
}
(27 rows)
+2Sorry, Pelle. Forgot the VERBOSE. You can use pastebin or a gist for the full EXPLAIN VERBOSE.
Sorry MC = management console - you can download & install that on another node
Ah ok. Iam watching the MC while the concurrent queries are active, yes. CPU load is the only factor that goes up, but never above 50%
can you post "mpstat 1" output for some significant seconds during the concurrency situation? may be there is lot of context switches.. Some people may have better ideas on that than I do
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
