Re: "Sending Data" is taking time post migration from 5.1 to 5.6

[SAIKIRAN CHEVITI]

Hi Peter,

Thank you for the suggestion. Let me explain the problem from the beginning so that you get a clear picture on this. It is a lengthy post. Please go through and provide your suggestions.

DB Server details
*******************
Below are the details of the server where the upgraded was done from Mysql 5.1 to MySQL 5.6.
OS version : Solaris 10 sparc
UI Language : Perl script
MySQL version : MySQL upgraded from 5.1 (logical upgrade- Installed 5.6 on same sever and restored 5.1 dump in 5.6)
Memory : 64 GB

We upgraded MYSQL form 5.1 to 5.6 in solaris 10 by using the dump upgrade. Converted all mysql tables from MYISAM to INNODB after the restoration, as mysql 5.6 have default storage engine as INNODB. My database size is 4.5 GB and added the parameters for the innodb.

Settings in 5.6
*****************
innodb_file_per_table= ON
innodb_flush_method=O_DIRECT
innodb_log_file_size=512M
innodb_buffer_pool_size=5G
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
Innodb_thread_concurrency=8
innodb_additional_mem_pool_size=512M

Current Situation
********************
In our application there are 3 modules and 2 of them are working fine after the 5.6 upgrade. In these modules, we only have simple select and update statements on less number of records. In the third module we have relatively more number of calculations for which we use temporary tables and update data from the master tables ( Some of the master tables are relatively big). This is where we are having performance issues. In the update statements, internally it is executing join condition with the existing master tables (which contains .5 million to 2miliion+ records ) with the temporary tables which contains nearly 100+ records.

Observations
****************
-The same queries in which the temporary table does not have any index gets updated in less than a second in 5.1 for similar size of records
-Without index on the Temporary table in 5.6 .. each update statement takes 15 minutes
-So to speed up the execution I have created some indexes on temporary tables . After creating the index on the Temporary table in 5.6 .. each update statement takes 25 seconds
-But 25 seconds is also too much because we have around 20+ such update statements in a single transaction
-We enabled profiling, from the profiling I have observed the sending data takes around 24 seconds
-We replaced the temporary table with a regular table, created indexes on the applicable columns and executed the query then also I didn't see any improvement in the performance. it still takes 25 sec for each update

As the application is creating some temporary tables while doing the transactions so I have created tmp_table_size and max_heap_table_size to 512 M .

explain plan with regular table in 5.6 with Indexes
====================================================
mysql> explain UPDATE BCT_VA_RES1_TMP,BCT_PROD SET BCT_VA_RES1_TMP.DESCRIPTION=BCT_PROD.DESCRIPTION WHERE BCT_VA_RES1_TMP.HEAD_PRODNO=BCT_PROD.PRODNO AND BCT_VA_RES1_TMP.HEAD_RSTATE=BCT_PROD.RSTATE AND BCT_VA_RES1_TMP.Level=0;
+----+-------------+-----------------+------+----------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+----------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | BCT_PROD | ALL | NULL | NULL | NULL | NULL | 560092 | NULL |
| 1 | SIMPLE | BCT_VA_RES1_TMP | ref | idx1,idx2,idx3 | idx1 | 153 | func | 1 | Using where |
+----+-------------+-----------------+------+----------------+------+---------+------+--------+-------------+

profiling for the query:
==================

mysql> show profile for query 9;
+---------------------------+-----------+
| Status | Duration |
+---------------------------+-----------+
| starting | 0.000332 |
| checking permissions | 0.000042 |
| checking permissions | 0.000026 |
| Opening tables | 0.000157 |
| checking permissions | 0.000372 |
| checking permissions | 0.000044 |
| init | 0.000141 |
| updating main table | 0.000086 |
| System lock | 0.000055 |
| optimizing | 0.000073 |
| statistics | 0.000204 |
| preparing | 0.000250 |
| executing | 0.000027 |
| Sending data | 24.088705 |
| updating reference tables | 0.025347 |
| end | 0.000049 |
| end | 0.000034 |
| removing tmp table | 0.000051 |
| end | 0.000025 |
| query end | 0.010404 |
| closing tables | 0.000097 |
| freeing items | 0.001646 |
| logging slow query | 0.000027 |
| cleaning up | 0.000087 |
+---------------------------+-----------+
24 rows in set, 1 warning (0.01 sec)

Queries
*********
- Can you please help me on identifying why "sending data" is taking too much time while fetching the data from the database. Is there any specific reasons behind this ?
- Are there any tools to identify what is responsible for this "Sending data"?
- Mydatabase size is 4.7Gb. I have copy the dump from the production and when I am doing restoration it is taking 5-6 hours for restore. in general it shoul be around 1 to 1.30 hrs.

Highly appreciate your support on this.