The Basics

Actian Ingres Vector(wise) is very adept at parallelizing the processing of queries over multiple processor cores. You can choose how many cores to use per query in the server configuration and it can be overridden on a per-query basis using a with-clause.

How do you determine the server default for cores-per-query?

If you go to your local vectorwise distribution and change-directory to “$II_SYSTEM/ingres/data/vectorwise/”, you will see a vectorwise.conf file. Keep in mind that Actian is changing the name of Vectorwise to Vector, so I cannot promise that the file name will be exactly the same in the future. If you edit this file, can you will find the parameter “max_parallelism_level” under the engine section. The value of this setting is the maximum number of cores that your queries will use by default when executing (they may use less). I believe you most likely need to restart the server (ingstop/ingstart) in order to see changes to this value take effect.

Setting parallelism on a per-query basis

It is possible to override the parallelism of a specific query via the SQL command used to execute it (though you have to decide for yourself whether this is a good idea or not). For example, you could do: “select a.* from test_table1 a join test_table2 b on a.id = b.id with max_parallel 16;” to make your query use 16 cores instead of the configured amount.

Interestingly enough, if I do this on my server which is configured to use a maximum of 8 cores, and I set the query parallelism to 8, I get a 4x performance improvement. The server only appears to be using 2 of the 8 allowed cores when it executes by default. Telling the query to explicitly use 8 thus yields a large performance improvement. Even more interestingly, if I bump up the 8 to 16 for the query, I get some more performance improvement (around 20%) which seems to indicate that the explicit query setting can use more than what is defined as the maximum in the vectorwise.conf file.