Understanding Informix Network Performance Features

Understanding Informix Network Performance Features

• 1.
  © 2011 IBMCorporationDecember 16, 2015 Improve the Speed and Performance John F. Miller III, IBM Scott Lashley, IBM
• 2.
  Information Management –IDS 11 © 2011 IBM Corporation2 Network Messaging
• 3.
  Information Management –IDS 11 © 2011 IBM Corporation3 Optimizing Database Network Messaging • Data Buffer Management • Optimize Open-Fetch-Close (OPTOFC) • Deferred Prepare • Auto Free
• 4.
  Information Management –IDS 11 © 2011 IBM Corporation4 Examining SQL Network Messages – Network messages sent between the application and the database engine to accomplish SQL operations PREPARE stmt FROM “select * from t where a > ?”; DECLARE cursor_1 from :stmnt; OPEN cursor_1 USING var_1; FETCH cursor_1 into results_1; CLOSE cursor_1; FREE cursor_1; FREE stmt; PREPARE stmt FROM “select * from t where a > ?”; DECLARE cursor_1 from :stmnt; OPEN cursor_1 USING var_1; FETCH cursor_1 into results_1; CLOSE cursor_1; FREE cursor_1; FREE stmt;
• 5.
  Information Management –IDS 11 © 2011 IBM Corporation5 PREP/DEC Small Sent - Medium Returned OPEN Small Sent - Medium Returned FETCH Small Sent - Medium Returned CLOSE Small Sent - Small Returned FREE Small Sent - Small Returned PREP/DEC Small Sent - Medium Returned OPEN Small Sent - Medium Returned FETCH Small Sent - Medium Returned CLOSE Small Sent - Small Returned FREE Small Sent - Small Returned Normal Message Traffic – 128 KB of data returned by the select – No blob data and rows smaller than 4KB Total number of messages sent = 72 32x
• 6.
  Information Management –IDS 11 © 2011 IBM Corporation6 Improving SQL Network Messages – FET_BUF_SIZE  Set the buffer size for the fetch/insert buffers  Size is specified in bytes up to 32KB  Requires no application changes  Yields similar performance as fetch array – OPTOFC – Optimize Open Fetch Close  Requires only error checking changes  Delays sending the open until the first fetch is requested by the application  The engine will close the cursor when the last row is processed – Deferred Prepare  Control whether a client process postpones sending a PREPARE statement to the database server until the OPEN or EXECUTE statement is sent – Auto Free  Frees the cursor when the cursor is closed
• 7.
  Information Management –IDS 11 © 2011 IBM Corporation7 PREP/DEC Small Sent - Medium Returned OPEN/FETCH Medium Sent - 32KB Returned FETCH Small Sent - 32KB Returned FETCH/CLOSE Medium Sent - 32KB Returned FREE Small Sent - Small Returned PREP/DEC Small Sent - Medium Returned OPEN/FETCH Medium Sent - 32KB Returned FETCH Small Sent - 32KB Returned FETCH/CLOSE Medium Sent - 32KB Returned FREE Small Sent - Small Returned 2x Total number of messages sent = 12 OPTOFC Message Traffic OPTOFC enabled Fetch buffer set at 32KB
• 8.
  Information Management –IDS 11 © 2011 IBM Corporation8 PREP/DEC Small Sent - Medium Returned OPEN Small Sent - Medium Returned FETCH Small Sent - Medium Returned CLOSE Small Sent - Small Returned PREP/DEC Small Sent - Medium Returned OPEN Small Sent - Medium Returned FETCH Small Sent - Medium Returned CLOSE Small Sent - Small Returned Total number of messages sent = 8 Small Select Statements Traffic Improvement PREP/DEC/OPEN/FETCH/CLOSE Medium Sent - 32KB ReturnedPREP/DEC/OPEN/FETCH/CLOSE Medium Sent - 32KB Returned Total number of messages sent = 2 OPTOFC enabled Fetch buffer set at 32KB Defer Prepare enabled
• 9.
  Information Management –IDS 11 © 2011 IBM Corporation9 Are My Application Using OPTOFC? session effective #RSAM total used dynamic id user user tty pid hostname threads memory memory explain 145 informix - 20 30096 talo 1 274432 270896 off tid name rstcb flags curstk status 232 sqlexec 44d28d78 Y--P--- 12272 cond wait cmd_cond - Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 145 V 46853040 270336 2728 259 6 name free used name free used overhead 0 6576 resident 0 72 scb 0 144 opentable 0 8656 filetable 0 1344 ru 0 600 misc 0 1088 log 0 16536 sqscb info scb sqscb optofc pdqpriority sqlstats optcompind directives 45ce0028 4620c028 0 0 0 2 1 Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers Explain 145 EXEC PROCEDURE sysadmin CR Not Wait 0 0 9.24 Off
• 10.
  Information Management –IDS 11 © 2011 IBM Corporation10 Network Performance Improvements  Caching network services  Multiple listener threads for a single server name  Multiple file descriptor servers  Previous network improvements – Dynamic start and stop of listener threads – Pre-allocate users session
• 11.
  Information Management –IDS 11 © 2011 IBM Corporation11 Network Performance - Caching Network Services  Database caching of Host, Services, Users and Groups  Avoids going to the operating system for each network call  Administrator defined timeout value set for network caches  ONCONFIG example NS_CACHE host=900,service=900,user=900,group=900  Each cache is dynamically configurable
• 12.
  Information Management –IDS 11 © 2011 IBM Corporation12 Network Performance – Multiple Listeners  Able to define multiple listener threads for a single DBSERVERNAME and/or DBSERVERALIAS  Add the number of listeners to the end of the alias  EXAMPLE – To start three listener threads for the idsserver – Modify the ONCONFIG as follows DBSERVERNAME idsserver-3 12 Page 12
• 13.
  Information Management –IDS 11 © 2011 IBM Corporation13 Network Performance Results  My simple network performance tests – 200 users connecting and disconnecting  Connection throughput on an AIX server improved by 480%  Connection throughput on a Linux server improved by 720% Computer Type Without Improvements Utilizing Improvements AIX 64 2m 5s 27s Linux 64 10m 11s 1m 20s
• 14.
  Information Management –IDS 11 © 2011 IBM Corporation14