Queries very slow at high concurrency

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!

Comments

  • 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:
    Compiled with: 4.1.2 20080704 (Red Hat 4.1.2-55)
    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.
    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.

    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.



  • Most 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? :-)
  • No  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    ) .

     

  • 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    
  • execution_parallelism
  • Hi,

    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?
  • Am 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.


  • How 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
  • Look 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

  • Is 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)

  • Sorry, Pelle. Forgot the VERBOSE. You can use pastebin or a gist for the full EXPLAIN VERBOSE.
  • Ok, thanks so far! Here's the verbose explain: http://pastebin.com/LRddqFpK

  • 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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file