Draft: Change engine from MyIsam to Innodb in order to remove locks and use transactions
This is an inelegant but effective way to avoid locks which are replaced with transactions, supported in InnoDB and not in MyIsam. The solution should be in any case to re-design the DB schema, but we found it unacceptable to have tables locked more 100 seconds, so we implemented this quick and dirty fix in production.
Main benefit is in the performance of the Device and Attribute Property Commands as can be seen in the statistics that follow.
Note that migration script actually can present some issues, for instance in a very old Tango DB we had rows with duplicated primary keys in the ..._hist tables, mostly for data which were deleted leaving something behind.
- MyIsam statistics
| command | average | minimum | maximum | calls |
|---|---|---|---|---|
| DbDeleteDeviceProperty | 4.643 | 0.347 | 2361.601 | 1022 |
| DbExportDevice | 6.391 | 2.675 | 23888.923 | 12880 |
| DbGetClassPropertyList | 0.370 | 0.171 | 1.235 | 52 |
| DbGetDeviceAttributeProperty2 | 6.653 | 0.295 | 49621.180 | 1993827 |
| DbGetDeviceClassList | 8.326 | 7.280 | 23.734 | 319 |
| DbGetDeviceDomainList | 13.291 | 8.089 | 32.633 | 912 |
| DbGetDeviceExportedList | 9.306 | 7.909 | 28.186 | 850 |
| DbGetDeviceFamilyList | 9.565 | 7.684 | 33.212 | 1700 |
| DbGetDeviceMemberList | 9.605 | 7.705 | 35.940 | 3839 |
| DbGetDeviceProperty | 6.543 | 0.151 | 84862.495 | 4076736 |
| DbGetDevicePropertyList | 2.563 | 0.148 | 9010.928 | 36490 |
| DbGetHostServerList | 8.597 | 6.745 | 21.878 | 94 |
| DbGetServerList | 10.246 | 7.429 | 84898.347 | 3061035 |
| DbImportDevice | 0.483 | 0.188 | 103722.537 | 388367765 |
| DbImportEvent | 0.286 | 0.189 | 0.508 | 16 |
| DbInfo | 27.911 | 25.360 | 35.608 | 13 |
| DbMySqlSelect | 14.057 | 0.821 | 8063.775 | 1374 |
| DbPutClassProperty | 76.515 | 13.985 | 259.127 | 1558 |
| DbPutDeviceAttributeProperty2 | 19.605 | 0.142 | 45960.407 | 656130 |
| DbPutDeviceProperty | 110.455 | 3.312 | 110751.931 | 319314 |
| DbUnExportServer | 89.503 | 26.734 | 49363.203 | 1472 |
- InnoDB statistics
| command | average | minimum | maximum | calls |
|---|---|---|---|---|
| DbDeleteDeviceProperty | 0.690 | 0.288 | 12.705 | 1392 |
| DbExportDevice | 6.963 | 4.160 | 45.531 | 12438 |
| DbGetClassPropertyList | 0.470 | 0.226 | 1.146 | 14 |
| DbGetDeviceAttributeProperty2 | 2.572 | 0.290 | 1112.218 | 720643 |
| DbGetDeviceClassList | 5.528 | 4.742 | 8.484 | 428 |
| DbGetDeviceDomainList | 10.362 | 5.281 | 25.371 | 517 |
| DbGetDeviceExportedList | 6.111 | 5.032 | 11.376 | 691 |
| DbGetDeviceFamilyList | 6.231 | 4.711 | 15.608 | 1013 |
| DbGetDeviceMemberList | 6.469 | 4.682 | 24.399 | 1619 |
| DbGetDeviceProperty | 0.462 | 0.153 | 395.454 | 1871076 |
| DbGetDevicePropertyList | 0.595 | 0.211 | 6.964 | 1118 |
| DbGetHostServerList | 6.841 | 5.565 | 13.505 | 517 |
| DbGetServerList | 6.185 | 4.492 | 49.274 | 897907 |
| DbImportDevice | 0.349 | 0.222 | 3092.801 | 496343826 |
| DbImportEvent | 0.273 | 0.156 | 1.065 | 250 |
| DbMySqlSelect | 4.570 | 0.224 | 22.818 | 1165 |
| DbPutClassProperty | 11.379 | 5.946 | 62.913 | 4017 |
| DbPutDeviceAttributeProperty2 | 14.267 | 0.147 | 1015.986 | 83890 |
| DbPutDeviceProperty | 26.225 | 0.104 | 1135.535 | 14788 |
| DbUnExportServer | 101.966 | 16.100 | 617.688 | 1658 |