PowerBuilder; using FROM dummy, performance problem..?

[hansg]

Hi,

Still searching for a problem of our largest customer. Users complain that the application (PowerBuilder) is teriible slow. Mostly it works fine after a restart of the databaseserver.

We did a lot already. Also running Foxhounds for months. That gives not a clue for the moment.

One very weird thing was that sometimes even a 'select * from dummy' takes seconds. (also visible in Foxhound, because the liveness is timing out)

Question: We use a lot of these constructions in PowerBuilder: SELECT fn_xxxx(:ls_parm1) INTO :li_local_pb_var FROM SYS.DUMMY ; (that is because PowerBuilder needss a FROM clause, this method is advised in the manual)

fn_xxx is a database function in SQL Anywhere. That can be a functions that does a lot and can take several seconds, even minutes, to execute.

When dozens of users call that kind of functions, could it be that this construction leads to a performance penalty? Because they all need the same row of table dummy? If so, we should change that into another construction.

Any ideas on that?

Tia Hans

[chris_keating]

I would argue that the issue is not with DUMMY but the function that is being called. The DUMMY table is virtualized eliminating contention associated with its use. I am not sure how "liveness" is timing out running such a query. If that is occurring, it might be network instruction problems. Liveness timeouts mean that traffic is not flowing correctly.

Can you share the versions involved and how you are timing the operations?

[hansg]

SQL Anywhere 17 17.0.10.6057

The 'select * from dummy' was executed in dbisql. The result takes a while and at the Histroy tab I saw values like 2 thru 7 seconds. This problem occurs only at the season of the customer (nurseries are most busy around spring and autumn). When the database server is restarteed it is fine for some day(s).

There where no locks, cpu around 5%, cache is using 35% of what is available (set as start value) and does not grow, the -gn is standard (20-80) and shows 34 as max in foxhound. The max_query_task is set to 1.

I was thinking about setting the -gn to some extreme value, like 1000, and max_query_task to 16 (they have 24 cores). To see if values (cpu. memory,etc) will rise. Now it looks doing nothing but still bath performance. That is the biggest frustration, that all seems calm, but the users are complaining. Is that changing of the -gn to 1000 and max_query_task a smart idea..?

We have moved the SQL Anywhere to an own (heavier) server already to be sure there are no other processes at the server that messup something. That helps a little.

At the moment there is no problem at the customer side, but in the autumn they will be busy again.

Tia Hans

[Breck_Carter]

Send me your foxhound5.db, I'll give you a(nother) free hour like it says in the Foxhound Help 🙂

[Breck_Carter]

The way someone (Glenn Paulley?) explained it to me, if the FROM clause is omitted SQL Anywhere assumes "FROM DUMMY". AFAIK the PowerBuilder restriction dates from ancient times before PB even supported Watcom SQL, and other RDBMS products required a FROM clause, so PowerScript followed suit.

In your case, the point is this: FROM DUMMY has absolutely nothing to do with any performance issues, not even a little.

[Breck_Carter]

In the (unlikely?) case your workload is starving for intraquery parallelism, max_query_tasks = 1 will hide that fact altogether... there won't be any child connections, and there won't be any indication that childless parents even wanted children in the first place.

So, set it to some value other than 1 to test the starvation hypothesis. If the hypothesis is correct, you should see a lot of child connections using up a lot of CPU. If there are many short-lived child connections, Foxhound will miss most of them because it only records samples every ten seconds... but in that case the child connections that ARE captured will have connection numbers that leap upwards (to account for their siblings that were born and died in between samples).

Setting max_query_tasks = 16 is as good as any other setting (probably better than zero 🙂

[Breck_Carter]

Setting -gn [extreme value] is probably not warranted because you aren't running out of threads. The Cool Kids have always argued against extreme values because you might run out of something-or-other 🙂

Go ahead and set -gn [large value] like 200 if you want, but I'll bet you won't see any difference.

[Breck_Carter]

PS Foxhound should flag leaping connection numbers, but it doesn't, but it will sometime in the future 🙂

[hansg]

Thanks, could to know. Then we can excluded that as a problem.

[VolkerBarth]

As to the elimination of contention when selecting FROM DUMMY, that's also officially documented...