USE_COROUTINES or LOAD_BALANCING_CHK - 64bit MaxDB 7.6 ?

[nelis]

Hi,

From a performance point of view which would be the most efficient to use, USE_COROUTINES or LOAD_BALANCING_CHK on MaxDB 7.6 ?

I understand how LOAD_BALANCING_CHK(task switching) works but haven't been able to find much info on USE_COROUTINES, perhaps someone can clear that up for me ?

For info, I am running MaxDB 64bit on Quad dual core AMD HP Blade system with 18 GB ram. Currently MAXCPU is set to only 4 but I intend on increasing it to at least 6 as this is a dual core system, leaving 1 CPU for Operating System use. We are using SLES9 64bit currently but will be upgrading to SLES10sp1 in near future.

Any advice/comments ?

Many thanks.

Regards,

Nelis

[roland_mallmann]

Hi there,

first of all, are you considering this because you have a performance issue you want to solve, or is this just to further optimize an already fine running system?

I'm guessing here that you're running an OLTP instance (or several)? If that's a correct assumption, I can tell you that we currently do not have many customers running an OLTP instance with Load Balancing enabled. We are however, very interested in any results.

As to whether to choose Coroutines or Load Balancing is dependent on the current performance bottlenecks of the DB / machine.

Kind regards,

Roland

[nelis]

Hi Roland,

Thanks for the reply.

We are running several OLTP systems. I was just wondering whether activating Load Balancing in MaxDB would give better results especially when it comes to long running SQL statements that will take up a task for extended periods. I noticed some financial reports were timing out which led me to this investigation and thought I would check first from a performance tuning perspective if there is anything I could do to improve the situation. I know there are many variables which could cause these reports to time out but I'm just covering all bases before I look at the application.

I found some more information on Load Balancing and it seems it could be quite heavy on resources as each task in the kernel is run in it's own thread and places a lot of overhead on the dispatcher(from what I understand).

Nelis

[markus_doehr2]

LOAD_BALANCING will not give your better results on long running statements, not by default.

If the system does e. g. a select on table MKPF for a range of one year, that data must be read from the disk, no matter, if the same or a different UKT is used during the reading process.

What you can do to improve read performance is, take an SQL trace, evaluate it and check, if you can add an index for that selection. Depending on the strategy used after that index has been created, you can use the parameter OPTIMIZE_PARALLEL_SERVERS to make the index be read in parallel.

Admittedly, this can be cumbersome if you have many such programs but I can tell you, that after we found out the majority of those selections and created several indices on those tables, we see only very few timeouts during normal daytime, even on a rather big system (~ 1.5 TB).

"Other databases" do (amongst other techniques) prefetching to make reading faster with the drawback, that physically many more I/O is made and those databases appear to be "faster". This can lead to situations, where one user can max out the physical I/O on your storage subsystem totally and make others users suffer.

If you have the possibility, I highly suggest attending the "MaxDB Internals" course, it will dive very deep into the internals of how the database works and make you understand much better what can be done for tuning.

--

Markus

[roland_mallmann]

Hi Nelis,

I fully agree with Markus' answer above; if you've got transactions/reports running into timeouts, it's far more likely that you've got some long running SQL statements. Finding out exactly which SQL statements are timing out and making sure that the statistics of the involved tables are new enough is a good start here.

Enabling Load Balancing (and thereby disabling the Coroutines) should only be done when you've managed to single down a CPU bottleneck.

Regards,

Roland

[nelis]

Markus, thanks again for the reply and Roland too.

I was thinking along the lines that if Load Balancing was enabled then when long running SQL statements hold tasks for long running periods that anything queued would then be switched over to another free task and therefore execution times would be shortened, at least for anything that gets queued..

I will start to look at the reports themselves now and do as you suggest, take a SQL trace.

Unfortunately, even though I will be doing MaxDB as part of my certification they do not offer it in my country as I don't think there is a big enough demand. I will try purchase the course materials though.

Regards,

Nelis

[markus_doehr2]

As far as I understood LOAD_BALANCING (Roland, please correct me if I´m wrong) it will do the following:

Several UKTs are bound to a single operating system thread. If you have now two statements, that are processed on the same CPU (so one is "Running" and one is "Runnable"), the load balancing will put the UKT on a different CPU so it can be scheduled by the operating system scheduler again instead of waiting for the first UKT to finish work or do I/O.

What you describe is some long running I/O, if an UKT is in "IO Wait" state, another UKT in the same thread can be running though, no matter if you use load balancing or not.

So yes, using LB will put a higher load on the (database) dispatcher since the full context must be switched to a different CPU/thread; this must be done as an atomic operation thus implementing syscalls and hence being expensive.

Hope I didn´t mix up anything here...

--

Markus