We have a highly loaded database. The SQL Anywhere is running as a service.
-n D4w -x tcpip(port=...) -tq ... -gns -cs -cc+ -cr+ -cv+ -z -ze -gnl 500 -gn 500 -gnh 1024 -cl 30G -gl all -os 524288 -o ... -oe ... -zoc ...
Processors in use by server: 64 logical processor(s) on 32 core(s) on 2 physical processor(s)
Running Windows 2016 Build 17763 on X86_64
Server built for X86_64 processor architecture
33661272K of memory used for caching
Minimum cache size: 31531008K, maximum cache size: 120791248K
Using a maximum page size of 16384 bytes
Multiprogramming level: minimum:500, current:500, maximum:1024
Automatic tuning of multiprogramming level is enabled
In rare cases, during a high workload, the following occurs:
- suddenly the server stops accepting connections;
- the multiprogramming level is increasing dramatically;
- the server continues to work;
- there are no deadlocks and no thread deadlocks;
- application freezes;
- only restarting the SQL Anywhere helps, which happens without problems.
In doing so, we see the following statistics:
12:53:25. OS Available: 29094544K, Working Set: 84910476K, Cache Target: 112079368K, Cached: 98%
13:10:25. OS Available: 27356144K, Working Set: 87092580K, Cache Target: 112594518K, Cached: 98%
14:17:38. OS Available: 11311352K, Working Set: 101673596K, Cache Target: 111615479K, Cached: 98%
14:30:44. Database server shutdown requested via server console
14:41:11. OS Available: 80640144K, Working Set: 34198640K, Cache Target: 94075029K, Cached: 98%
13:40:00. CurrentCacheSize=113055220 PeakCacheSize=113055220 MinCacheSize=31531008 MaxCacheSize=120791248
13:50:00. CurrentCacheSize=113055220 PeakCacheSize=113055220 MinCacheSize=31531008 MaxCacheSize=120791248
14:00:00. CurrentCacheSize=113055220 PeakCacheSize=113055220 MinCacheSize=31531008 MaxCacheSize=120791248
14:10:00. CurrentCacheSize=113055220 PeakCacheSize=113055220 MinCacheSize=31531008 MaxCacheSize=120791248
14:20:00. CurrentCacheSize=112090776 PeakCacheSize=113055220 MinCacheSize=31531008 MaxCacheSize=120791248
14:30:05. CurrentCacheSize=112090776 PeakCacheSize=113055220 MinCacheSize=31531008 MaxCacheSize=120791248
14:30:44. Database server shutdown requested via server console
14:40:00. CurrentCacheSize=33661256 PeakCacheSize=33661312 MinCacheSize=31531008 MaxCacheSize=120791248
05:33:09. MPL[curr=500 prev=500] concurrency[2] throughput[curr=837 prev=332] algorithm=0
14:16:31. MPL[curr=500 prev=500] concurrency[303] throughput[curr=20642 prev=31619] algorithm=0
14:17:02. MPL[curr=500 prev=500] concurrency[656] throughput[curr=15373 prev=20642] algorithm=0
14:17:33. MPL[curr=500 prev=500] concurrency[858] throughput[curr=8327 prev=15373] algorithm=0
14:18:04. MPL[curr=500 prev=500] concurrency[682] throughput[curr=25696 prev=8327] algorithm=0
14:18:35. MPL[curr=502 prev=500] concurrency[642] throughput[curr=11032 prev=25696] algorithm=0
14:19:07. MPL[curr=501 prev=502] concurrency[603] throughput[curr=662 prev=11032] algorithm=0
14:19:42. MPL[curr=500 prev=501] concurrency[890] throughput[curr=16686 prev=662] algorithm=0
14:20:13. MPL[curr=500 prev=500] concurrency[484] throughput[curr=94936 prev=16686] algorithm=0
14:20:44. MPL[curr=500 prev=500] concurrency[478] throughput[curr=59421 prev=94936] algorithm=0
14:21:15. MPL[curr=500 prev=500] concurrency[1109] throughput[curr=6439 prev=59421] algorithm=0
14:21:46. MPL[curr=500 prev=500] concurrency[1327] throughput[curr=1360 prev=6439] algorithm=0
14:22:17. MPL[curr=500 prev=500] concurrency[1394] throughput[curr=158 prev=1360] algorithm=0
14:22:48. MPL[curr=500 prev=500] concurrency[1465] throughput[curr=51 prev=158] algorithm=0
14:23:19. MPL[curr=500 prev=500] concurrency[1406] throughput[curr=1785 prev=51] algorithm=0
14:23:50. MPL[curr=508 prev=500] concurrency[1380] throughput[curr=1716 prev=1785] algorithm=0
14:24:21. MPL[curr=504 prev=508] concurrency[1375] throughput[curr=1645 prev=1716] algorithm=1
14:24:52. MPL[curr=503 prev=504] concurrency[1448] throughput[curr=259 prev=1645] algorithm=0
14:25:23. MPL[curr=513 prev=503] concurrency[1503] throughput[curr=1581 prev=259] algorithm=0
14:25:54. MPL[curr=683 prev=513] concurrency[1433] throughput[curr=23532 prev=1581] algorithm=0
14:26:25. MPL[curr=910 prev=683] concurrency[1540] throughput[curr=17316 prev=23532] algorithm=0
14:26:56. MPL[curr=820 prev=910] concurrency[1647] throughput[curr=0 prev=17316] algorithm=1
14:27:27. MPL[curr=806 prev=820] concurrency[1791] throughput[curr=0 prev=0] algorithm=0
14:27:59. MPL[curr=796 prev=806] concurrency[1892] throughput[curr=694 prev=0] algorithm=0
14:28:30. MPL[curr=786 prev=796] concurrency[1736] throughput[curr=1231 prev=694] algorithm=0
14:29:01. MPL[curr=776 prev=786] concurrency[1804] throughput[curr=1340 prev=1231] algorithm=0
14:29:32. MPL[curr=766 prev=776] concurrency[1842] throughput[curr=581 prev=1340] algorithm=1
14:30:03. MPL[curr=782 prev=766] concurrency[1894] throughput[curr=957 prev=581] algorithm=0
14:30:34. MPL[curr=1024 prev=782] concurrency[1653] throughput[curr=38244 prev=957] algorithm=0
14:30:44. Database server shutdown requested via server console
14:32:19. MPL[curr=500 prev=500] concurrency[131] throughput[curr=21109 prev=19880] algorithm=0
05:33:09. Adjusting the multiprogramming level to 500
14:16:31. Adjusting the multiprogramming level to 500
14:17:02. Adjusting the multiprogramming level to 500
14:17:33. Adjusting the multiprogramming level to 500
14:18:04. Adjusting the multiprogramming level to 502
14:18:35. Adjusting the multiprogramming level to 502
14:19:07. Adjusting the multiprogramming level to 500
14:19:42. Adjusting the multiprogramming level to 500
14:20:13. Adjusting the multiprogramming level to 500
14:20:44. Adjusting the multiprogramming level to 500
14:21:15. Adjusting the multiprogramming level to 500
14:21:46. Adjusting the multiprogramming level to 500
14:22:17. Adjusting the multiprogramming level to 500
14:22:48. Adjusting the multiprogramming level to 500
14:23:19. Adjusting the multiprogramming level to 508
14:23:50. Adjusting the multiprogramming level to 508
14:24:21. Adjusting the multiprogramming level to 504
14:24:52. Adjusting the multiprogramming level to 513
14:25:23. Adjusting the multiprogramming level to 683
14:25:54. Adjusting the multiprogramming level to 910
14:26:25. Adjusting the multiprogramming level to 910
14:26:56. Adjusting the multiprogramming level to 820
14:27:27. Adjusting the multiprogramming level to 806
14:27:59. Adjusting the multiprogramming level to 796
14:28:30. Adjusting the multiprogramming level to 786
14:29:01. Adjusting the multiprogramming level to 776
14:29:32. Adjusting the multiprogramming level to 782
14:30:03. Adjusting the multiprogramming level to 1024
14:30:34. Adjusting the multiprogramming level to 1024
14:30:44. Database server shutdown requested via server console
14:32:19. Adjusting the multiprogramming level to 500
Are there any recommendations on how we can avoid such freezing?
asked
14 Oct '22, 05:49
Ilia63
1.2k●51●57●82
accept rate:
44%
What about CPU of DB server? Is it busy during the time, when the load is high?
I have no clue - but might this be similar to this (yet unsolved) issue:
https://sqlanywhere-forum.sap.com/questions/37889/is-dynamic-tuning-of-the-mpl-no-longer-safe-in-sql-anywhere-170106089
Thanks Vlad. I requested this information.
Thanks Barth. The "symptoms" are very similar ( unfortunately (((
ps SQL Anywhere Network Server Version 17.0.10.5963
I don't know the answer (obviously), but if the server gets so much load, it tries to increase the mpl to handle it, this doesn't help, then increases again and again, and finally dies. CPU is a good indicator that this might happen.