The HP Vertica 7.0.1 Community Edition Virtual Machine is a very effective tool to test drive HP Vertica. However, it is not without caveats. The immediately obvious issue is that since you're using a VM, you're unable to take advantage of any of the performance gains that come through clustering. Impressive as they are, many of the query performance tests that HP Vertica touts, are run against systems that are between 5 and 8 clustered nodes. This morning I found out the hard way that some things may not be possible using the VM, and would be best handled in a clustered environment.
Over the weekend I collected over 500 thousands tweets to analyze through HP Vertica Pulse. As stated in the HP Vertica Marketplace, "HP Vertica Pulse is an entity extraction and sentiment analysis tool that automatically analyzes short text to help you understand what your community is talking about and how they feel about those topics." When I executed a query using the SentimentAnalysis() function limited to 10 results, the query returned fairly quickly. However the results were not very useful since the attributes that bubbled to the top were not very interesting, and in no particular order. I then issued the query again, but this time I added an ORDER BY clause to observe the returned attributes more closely aligned. To my surprise, the query ran for an inordinate amount of time, and seemed to be stressing the host machine. I did the only thing I thought to do, and issued a quit command (\q), but the query continued to execute.
So what's the workaround? Close the long running session using the close_session() function.
I opened a new terminal session, and executed the following query:
dbadmin=> SELECT session_id FROM sessions;
which returned two open sessions. I then simply passed the session_id to the close_session() function, and the session stopped --returning my host computer to a more stable state:
dbadmin=> SELECT close_session('vertica-5028:0x121a1');
I hope this helps you if you are ever execute a query and have it run amok while working within the HP Vertica Community Edition VM.Labels: Community Edition, HP Vertica, vsql