netezza concurrent transaction related

系统默认值:
nzsystem showRegistry|grep Concurrent
host.graMaxConcurrent              = 48

一般更改的话, 只会改小:

To change this value,you need to stop or pause the server.Then execute the below  command(to set it to 25)

nzsystem set -arg host.gkMaxConcurrent=25

Then Resume or start the server.

不过,这个值在实际环境中该设置多大,还需要测试:

The question is - "why" should you adjust the concurrency. The best concurrency number for a TF-24 turns out to be around 13, where the best one for a TF-48 turns out to be around 22. Why is this?

A long time ago someone asked my why - when they ran their ETL jobs end-to-end that they ran in 20 minutes each, but when running them concurrently they took over two hours to complete. This is because they are fighting each other for resources.

In Netezza, the more queries that are running, the more they fight with each other for resources. Netezza is not transactional. so concurrency is not measured by how many queries run at the same time, but by how many queries it can close per-minute. It is a "throughput" machine, not a transactional machine.

You will find that if you raise the concurrency number, many of your faster queries may actually return slower. And if you lower the concurrency number, many of your slowest queries may return much faster. It is counter-intuitive.

What you are looking for is a "sweet spot" for your solution. A good place to start is with 13 for a TF-24. This is because more of the machine is available to the query to help it close faster. This offers the "illusion of concurrency" when queries can close at a rapid rate even though they are not all running at the same time. Raise this limit  higher or lower depending on how your actual queries behave.

select * from _v_qrystat;
qs_state=3 的是处于 running 的状态, qs_state=1 的是处于 pending 的状态.
qs_priority=4 的是 HIGH PRIORITY
qs_priority=3 的是 NORMAL PRIORITY