Hi all,
I have a SP that among other things inserts data into a temp table. That part of the SP is below:-
DROP TEMPORARY TABLE IF EXISTS Tmp ;
CREATE TEMPORARY TABLE Tmp
(
ST TIME,
LoggedIn TINYINT UNSIGNED
);
WHILE StartTime < EndTime DO
INSERT INTO Tmp (ST, LoggedIn)
SELECT StartTime,
IF(StartTime BETWEEN TIME_FORMAT(CONCAT(InHour, ':', InMinute), '%H:%i' ) AND
TIME_FORMAT(ADDTIME(CONCAT(InHour, ':', InMinute), SEC_TO_TIME(Minutes * 60)), '%H:%i') , 1, 0)
FROM AdjTime
WHERE StrID = p_StrID AND DOB BETWEEN p_StartDate AND p_EndDate;
SET StartTime = ADDTIME(StartTime, '00:01' );
END WHILE;
SET StartTime = TmpST;
The performance of this SP is very good on our development Server but bloody terrible on our production server taking around 50 secs compared to less than 1 on the development server. Both Servers are basically the same spec and both have 4GB Ram with the production server using MySQL as it's main task is to produce reports from the MySQL data. The production server is Windows 2008 R2 X64 while the development is Windows 2003 R2 X64.
The INNODB database schema is **exactly** the same on both servers so it cannot be related to the DB design.
INNODB STATUS while the INSERTS are going on the production server is below:-
| InnoDB | |
=====================================
101019 13:54:46 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 57748, signal count 56246
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 51931, OS waits 25814; RW-excl spins 33937, OS waits 31934
------------
TRANSACTIONS
------------
Trx id counter 0 69579054
Purge done for trx's n:o < 0 69578152 undo n:o < 0 0
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 69578922, not started, OS thread id 2536
MySQL thread id 100988, query id 7954173 203.161.96.116 intouchaus
---TRANSACTION 0 69578752, not started, OS thread id 3744
MySQL thread id 100952, query id 7954169 CPE-124-187-43-78.lns11.cha.bigpond.net.au 124.187.43.78 intouchaus
---TRANSACTION 0 69578737, not started, OS thread id 3724
MySQL thread id 100950, query id 7954180 C-61-68-213-41.bri.connect.net.au 61.68.213.41 intouchaus
---TRANSACTION 0 69578818, not started, OS thread id 3872
MySQL thread id 100930, query id 7954174 CPE-124-182-240-82.lns4.way.bigpond.net.au 124.182.240.82 intouchaus
---TRANSACTION 0 69573972, not started, OS thread id 2844
MySQL thread id 100602, query id 7928890 C-59-100-81-164.bri.connect.net.au 59.100.81.164 intouchaus
---TRANSACTION 0 69574209, not started, OS thread id 1452
MySQL thread id 100391, query id 7931266 203.206.181.166 root
---TRANSACTION 0 0, not started, OS thread id 3096
MySQL thread id 99944, query id 7954184 203.206.181.166 root
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, OS thread id 2528
MySQL thread id 99869, query id 7909896 203.206.181.166 root
---TRANSACTION 0 69562472, not started, OS thread id 3612
MySQL thread id 99334, query id 7918793 203.206.181.166 root
---TRANSACTION 0 69558597, not started, OS thread id 3552
MySQL thread id 97252, query id 7853100 localhost 127.0.0.1 intouchpos4us
---TRANSACTION 0 69579053, ACTIVE (PREPARED) 0 sec, OS thread id 3964 preparing
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1216, 3 row lock(s), undo log entries 1
MySQL thread id 99870, query id 7954183 203.206.181.166 root closing tables
INSERT INTO Tmp (ST, LoggedIn)
SELECT NAME_CONST('StartTime',_binary'08:36:00' COLLATE 'binary'),
IF( NAME_CONST('StartTime',_binary'08:36:00' COLLATE 'binary') BETWEEN TIME_FORMAT(CONCAT(InHour, ':', InMinute), '%H:%i' ) AND
TIME_FORMAT(ADDTIME(CONCAT(InHour, ':', InMinute), SEC_TO_TIME(Minutes * 60)), '%H:%i') , 1, 0)
FROM AdjTime
WHERE StrID = NAME_CONST('p_StrID',851) AND DOB BETWEEN NAME_CONST('p_StartDate',_binary'2010-10-18' COLLATE 'binary') AND NAME_CONST('p_EndDate',_binary'2010-10-18' COLLATE 'binary')
---TRANSACTION 0 69579051, ACTIVE 0 sec, OS thread id 2120, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 1
MySQL thread id 101010, query id 7954175 C-59-100-67-30.bri.connect.net.au 59.100.67.30 intouchaus freeing items
insert into speedofservice values(0, '1042', 147, 137, 611942)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 1; buffer pool: 0
61562 OS file reads, 2337876 OS file writes, 2119309 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 23.99 writes/s, 23.99 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
8 inserts, 8 merged recs, 1 merges
Hash table size 4425293, node heap has 651 buffer(s)
23.66 hash searches/s, 35.32 non-hash searches/s
---
LOG
---
Log sequence number 5 3653922168
Log flushed up to 5 3653921894
Last checkpoint at 5 3653883442
1 pending log writes, 0 pending chkp writes
2013151 log i/o's done, 23.99 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2367944346; in additional pool allocated 11471872
Dictionary memory allocated 427280
Buffer pool size 131072
Free buffers 64701
Database pages 65720
Modified db pages 6
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 61552, created 4168, written 283070
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 1852, state: sleeping
Number of rows inserted 1884992, updated 134, deleted 686052, read 169820418
12.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 11.66 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Also the INNODB variables are:-
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb%';
+-----------------------------------------+------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 16777216 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 2147483648 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | C:\MySQLDatabases\ |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 120 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 220200960 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | .\ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_stats_on_metadata | ON |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 16 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_legacy_cardinality_algorithm | ON |
+-----------------------------------------+------------------------+
35 rows in set
Any ideas on why my INSERTS for this SP are so slow would be appreciated.
TIA
Greg