Vertica performance
Hi
I'm testing a vertica query performance.
I loaded 900,000,000 data that each data length about 300 bytes.
[TABLE]
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
-----------+-------+--------+----------+------+---------+----------+-------------+-------------
abcdefghi | t1 | major | char(50) | 50 | | f | f |
abcdefghi | t1 | minor | char(50) | 50 | | f | f |
abcdefghi | t1 | param | char(50) | 50 | | f | f |
abcdefghi | t1 | node | char(50) | 50 | | f | f |
abcdefghi | t1 | item | char(50) | 50 | | f | f |
abcdefghi | t1 | timeAt | int | 8 | | f | f |
abcdefghi | t1 | val | float | 8 | | f | f |
then i check the time by linux time command.
> time vsql database -f query file > /dev/null
This query select 72,000 data.
[QUERY]
select node,item,timeAt,val from t1
where
major='MAJOR0002' and minor='MINOR0003' and param='PARAM0004'
and
node in (
'NODE0010'
,'NODE0011'
,'NODE0012'
,'NODE0013'
,'NODE0014'
,'NODE0015'
,'NODE0016'
,'NODE0017'
,'NODE0018'
,'NODE0019'
)
and
timeAt >= 1434942000 and timeAt < 1435028400
order by node,item,timeAt
;
The result is 12-6 sec.
At top command, while running query, the CPU run queue is 4-6.
No swap, a little io.
My test machine has 4 core and no hyper thread.
And I don't any configuration specially.
I think this is a CPU bottle neck.
How do i improve this performance.
* When i tested the same query on other database,
that time is a 1-2 seconds.
Best Regards
Comments
Have you tried run DBD for this particular query?
I think you have a low cardinality fields for what a good projection design I am pretty confident that 1 you can reduce your storage footprint and get a much better performance.
http://docdev.verticacorp.com/doc/7.1.2/HTML/#Authoring/GettingStartedGuide/UsingDatabaseDeisgner/RunningDBDwithAT.htm?Highlight=database designer
To run for a specific query do the incremental option not the comprehensive.
Please let us know if this helps.
Eugenia