MySQL Forums
Forum List  »  Performance

Inserts very slow
Posted by: Greg Hines
Date: October 18, 2010 09:56PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Inserts very slow
3827
October 18, 2010 09:56PM
964
October 19, 2010 11:17PM
1137
October 20, 2010 02:48PM
880
October 20, 2010 08:40PM
885
October 20, 2010 08:50PM
874
October 20, 2010 09:49PM
884
October 20, 2010 11:59PM
847
October 21, 2010 03:07PM
803
October 21, 2010 08:42PM
837
October 21, 2010 09:26PM
814
October 24, 2010 03:05PM
929
October 24, 2010 07:13PM
909
October 25, 2010 08:25PM
871
October 27, 2010 06:01PM
953
October 28, 2010 08:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.