Improve Requests Per Second
I'm trying to get some bench marks from a small test environment (3 vm's, one table will 300 million rows). When I execute queries one at a time I'm getting satisfactory repsonse times (150ms) but when I try increase the throughput I can't do better than 5-6 requests per second. I need to be able to run 20 per second.
Are they any settings or configuration options that I can tweak to improve throughput?
Vertia 7.1 Community Edition.
0
Comments
Hi Ryan,
With regard to Vertica performance, in your environment the first thing I would look at tuning would actually not be Vertica at all; it would be your VMs. What does your virtual environment look like? Are you running three VirtualBox VMs on your laptop? A cluster on an ESX Server? Our sample Amazon CloudFormation?
If at all possible, I would encourage running Vertica on physical hardware. VMs are not as lightweight as you might hope; also not as predictable -- I've seen degradations ranging from a fraction of a percent to 10x or more, depending on workload, configuration of the VM host, and configuration of the virtual network. (10x is very unusual, but not impossible.)
Tuning is very platform-dependent, though. You can look through our documentation or (if applicable) contact your virtualization vendor. If you post more details here, I or others may have suggestions.
Beyond that: 150ms is about 1/6 of a second, roughly speaking. With those response times, you would in fact be getting about 6 queries per second, depending on where your bottleneck is and how you are initiating your queries. So you should be looking to tune query performance. To that end, have you run the Database Designer?
You could also look at Vertica's client-driver load balancing, to make sure you're distributing load evenly across all nodes (Vertica distributes query execution but the initiator still has to set everything up, which can be a big deal for short queries); or, if applicable (for queries that don't require any network operation), into Vertica's Key/Value API.
Adam
Thanks for the reply. Our VM's are on an ESX and I will start to push for physical hardware. The queries are pretty well tuned for the size of the table (150million rows). I haven't looked at client driver clustering, so I'll definitely check that out. Thanks!