Re: Difference between AS/400 and any normal web server

Hello Steve,

Am 22.08.2022 um 06:39 schrieb Steve Richter <stephenrichter@xxxxxxxxx>:

These apps have acceptable response time most of the time. When the system bogs down the apps hit the PHP 30 second limit.

Funnily, your observation matches mine on Linux, although some years ago.

The Exec limit is configurable, but raising it will make the overall behavior you described worse, not better.

Where the PHP server just gives up. Which leaves the javascript client code hanging. And although PHP timed out, DB2 continues to run the SQL procedure until completion.

I'm not wondering, seen the same with MySQL on Linux. Can running procedures be interrupted at all?

And the web user presses the F5 key to reload the page. Which starts another instance of the now very long running SQL procedure. Has the potential to be a real trainwreck mess.

Indeed!

I have observed that there's a certain limit of complexity about SQL queries vs. records in the tables being queried in MySQL. If this threshold is exceeded you get the described results. I'm sure this is a thing for every (SQL) database, including DB2 on IBM i. I told the programmer back then to lessen complexity of queries and better prepare some intermediate results in semi-temporary tables by scheduled/cron jobs, if there is no real-time insight possible. That was a huge relief. I left the company before the boomerang could hit me again, though.

This is where my assertion about an "intelligent", custom made application server as a solution — and not a problem — stems from. Done properly, it relieves the database from parallel hammering of very similar, very complex queries. If done properly, it generates more, but rather simple queries instead of complex long-running ones, doing JOINS and IFs on the cached data instead of overwhelming the database with complexity, in turn probably triggering locking issues because of long running SQL statements. A database is a generic engine and is limited in caching to adhere certain tests such as ACID and WHERE clauses to be the same. It can never handle the same load as easily as a caching application server doing frontend to SQL translations, being designed by someone knowing the greater goal and thus with a better understanding which data can be re-used and which is, e. g., user specific and can't be cached for multiple users.

Drawback is, a proper application server is a very specific thing, needs careful design and a lot of custom programming and thus costs a lot of money. Sometimes more than just throwing faster hardware at a problem. Thus delaying the problem to reappear with higher load. :-)

:wq! PoC