Using ROLLBACK with MyISAM is useless. A ROLLBACK command is used to undo any DML that occurs during a transaction (i.e. START TRANSACTION and COMMIT). The MySQL default storage engine MyISAM does not support transactions.
It is easy with the SHOW GLOBAL STATUS command to see if your application code uses ROLLBACK. By performing two samples you can look at the delta over time. The statpack utility is one product that provides a human friendly display of this delta. As seen below, the use of ROLLBACK in combination with the read/write ratio and the my.cnf –skip-innodb indicate unnecessary database work.
====================================================================================================
Variable Delta/Percentage Per Second Total
====================================================================================================
Statement Activity
====================================================================================================
SELECT: 1,135,589 1,309.79 189,279,510 (49.62%)
INSERT: 6,171 7.12 431,987 (0.11%)
UPDATE: 4,800 5.54 334,620 (0.09%)
DELETE: 312 0.36 17,910 (0.00%)
REPLACE: 0 0.00 0 (0.00%)
INSERT ... SELECT: 121 0.14 4,042 (0.00%)
REPLACE ... SELECT: 11 0.01 109 (0.00%)
Multi UPDATE: 0 0.00 30 (0.00%)
Multi DELETE: 0 0.00 28 (0.00%)
COMMIT: 0 0.00 0 (0.00%)
ROLLBACK: 1,154,987 1,332.16 191,382,775 (50.17%)
If the ROLLBACK command doesn’t do anything you may be tempted to consider this doesn’t do much harm, think again. In the following example of statements analyzed via TCP packets, the ROLLBACK attributed to 21% of the execution time of all SQL in this sample.
# Profile # Rank Query ID Response time Calls R/Call Item # ==== ================== ================ ===== ======== ================ # 1 0x4ED092EFA577DAB7 0.0106 24.8% 1 0.0106 SELECT p # 2 0xC9ECBBF2C88C2336 0.0102 23.8% 52 0.0002 SELECT r_c # 3 0x19C8068B5C1997CD 0.0092 21.6% 138 0.0001 ROLLBACK # 4 0x448E4AEB7E02AF72 0.0091 21.3% 52 0.0002 SELECT r_t # 5 0x56438040F4B2B894 0.0015 3.6% 2 0.0008 SELECT h_c # 6 0x164962ED9B451586 0.0012 2.9% 9 0.0001 SELECT r # 7 0x8FDE1484818AAACE 0.0008 1.9% 8 0.0001 SELECT p_c
In a well tuned system, the greatest time to execute an SQL statement is not the running of the SQL inside the MySQL kernel, it is the network latency of making the call, and the time taken to return the resultset requested.
In this extreme case on a production system, 1/2 the statements executed where unnecessary.
Uncovering this issue was three commands and less then 5 minutes of my time. The statpack report uncovered 4 additional red flags at the same time.
If you are not monitoring your production system, start now. For assistance on what to monitor and analysis please contact me for more information.
