Re: Stored Procedure too slow
Hello Rick,
I'll try to provide all the answers this time:
1) How many rows did you attempt to insert? 1 row at time
2) What's the table type? Innodb
3) This is the output of SHOW CREATE PROCEDURE AddPhysNode:
Procedure sql_mode "Create Procedure" character_set_client collation_connection "Database Collation"
AddPhysNode STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION "CREATE DEFINER=`root`@`localhost` PROCEDURE `AddPhysNode`(
IN _Accepted BIT,
IN _Creation BIGINT,
IN _IsOnline BIT,
IN _Label VARCHAR(255),
IN _MacAddress LONGBLOB,
IN _NSAP LONGBLOB,
IN _NetID INT,
IN _Type SMALLINT,
IN _ProtType SMALLINT,
OUT _NewID INT,
OUT _CreatedNew BIT
)
BEGIN
-- DECLARE netIDFromQuery INT;
DECLARE oldLabel VARCHAR(255);
DECLARE oldNsap LONGBLOB;
SET _NewID = 0;
SET _CreatedNew = 0;
SELECT Id, Label, NSAP INTO _NewID, oldLabel, oldNsap
FROM `PhysicalNode`
WHERE (MacAddress = _MacAddress) AND
(ProtType = _ProtType);
IF (_NewID > 0) THEN
-- change the label only if the node changed
-- nsap and the label has not been changed from the user
IF ((oldNsap = _NSAP) AND
(SUBSTRING(oldLabel,0,5) = 'NSAP:')) THEN
SET oldLabel = _Label;
END IF;
UPDATE `PhysicalNode`
SET `Accepted` = _Accepted,
`IsOnline` = _IsOnline,
`Label` = oldLabel,
`NSAP` = _NSAP,
`NetworkId` = _NetID
WHERE Id = _NewID;
ELSE
INSERT INTO `PhysicalNode` (
`Accepted`, `Creation`, `IsOnline`, `Label`, `MacAddress`,
`NSAP`, `NetworkId`, `ProtType`, `Type`)
VALUES (
_Accepted, _Creation, _IsOnline, _Label, _MacAddress,
_NSAP, _NetID, _ProtType, _Type);
SET _CreatedNew = 1;
SET _NewID = @@IDENTITY;
END IF;
END" utf8 utf8_general_ci latin1_swedish_ci
4) This is the output of SHOW CREATE TABLE PhysicalNode:
Table,"Create Table"
PhysicalNode,"CREATE TABLE `physicalnode` (
`AbsCoordX` double DEFAULT NULL,
`AbsCoordY` double DEFAULT NULL,
`AbsCoordZ` double DEFAULT NULL,
`Accepted` bit(1) NOT NULL,
`AdditionalArgs` varchar(4000) DEFAULT NULL,
`BatteryLevel` smallint(6) DEFAULT NULL,
`CoordX` double DEFAULT NULL,
`CoordY` double DEFAULT NULL,
`CoordZ` double DEFAULT NULL,
`Creation` bigint(20) NOT NULL,
`CurrentPwrSrc` smallint(6) DEFAULT NULL,
`ExpectedLifeTime` bigint(20) DEFAULT NULL,
`Id` int(11) NOT NULL AUTO_INCREMENT,
`IsOnline` tinyint(1) NOT NULL,
`Label` varchar(255) NOT NULL,
`LastBatteryChange` bigint(20) DEFAULT NULL,
`LastMaintenance` bigint(20) DEFAULT NULL,
`LastStatusTime` bigint(20) DEFAULT NULL,
`MacAddress` longblob NOT NULL,
`NSAP` longblob NOT NULL,
`NetworkId` int(11) NOT NULL,
`ProtType` smallint(6) NOT NULL,
`SignalRange` float DEFAULT NULL,
`Type` smallint(6) NOT NULL,
`WakeupPolicy` smallint(6) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `PhysNode_MacAndType` (`MacAddress`(128),`ProtType`),
KEY `NetworkId` (`NetworkId`),
CONSTRAINT `physicalnode_ibfk_1` FOREIGN KEY (`NetworkId`) REFERENCES `network` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1"
5) This is the output of show table status like 'PhysicalNode':
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
physicalnode InnoDB 10 Compact 0 0 16384 0 32768 5397020672 1 "2012-12-04 13:44:31" NULL NULL latin1_swedish_ci NULL
6) Is this Windows or Linux? Windows 7 x64
7) This is the output of SHOW VARIABLES LIKE 'innodb%':
Variable_name,Value
innodb_adaptive_flushing,ON
innodb_adaptive_hash_index,ON
innodb_additional_mem_pool_size,3145728
innodb_autoextend_increment,8
innodb_autoinc_lock_mode,1
innodb_buffer_pool_instances,1
innodb_buffer_pool_size,112197632
innodb_change_buffering,all
innodb_checksums,ON
innodb_commit_concurrency,0
innodb_concurrency_tickets,500
innodb_data_file_path,ibdata1:10M:autoextend
innodb_data_home_dir,
innodb_doublewrite,ON
innodb_fast_shutdown,1
innodb_file_format,Antelope
innodb_file_format_check,ON
innodb_file_format_max,Antelope
innodb_file_per_table,OFF
innodb_flush_log_at_trx_commit,1
innodb_flush_method,
innodb_force_load_corrupted,OFF
innodb_force_recovery,0
innodb_io_capacity,200
innodb_large_prefix,OFF
innodb_lock_wait_timeout,50
innodb_locks_unsafe_for_binlog,OFF
innodb_log_buffer_size,2097152
innodb_log_file_size,56623104
innodb_log_files_in_group,2
innodb_log_group_home_dir,.\
innodb_max_dirty_pages_pct,75
innodb_max_purge_lag,0
innodb_mirrored_log_groups,1
innodb_old_blocks_pct,37
innodb_old_blocks_time,0
innodb_open_files,300
innodb_purge_batch_size,20
innodb_purge_threads,0
innodb_random_read_ahead,OFF
innodb_read_ahead_threshold,56
innodb_read_io_threads,4
innodb_replication_delay,0
innodb_rollback_on_timeout,OFF
innodb_rollback_segments,128
innodb_spin_wait_delay,6
innodb_stats_method,nulls_equal
innodb_stats_on_metadata,ON
innodb_stats_sample_pages,8
innodb_strict_mode,OFF
innodb_support_xa,ON
innodb_sync_spin_loops,30
innodb_table_locks,ON
innodb_thread_concurrency,10
innodb_thread_sleep_delay,10000
innodb_use_native_aio,ON
innodb_use_sys_malloc,ON
innodb_version,1.1.8
innodb_write_io_threads,4
8) This is the output of SHOW VARIABLES LIKE 'autocommit':
autocommit ON
9) Are the MacAddresses sequential or random? RANDOM, and may be of different lenght
10) This is the output of SHOW TABLE STATUS LIKE 'PhysicalNode':
Name,Engine,Version,Row_format,Rows,Avg_row_length,Data_length,Max_data_length,Index_length,Data_free,Auto_increment,Create_time,Update_time,Check_time,Collation,Checksum,Create_options,Comment
physicalnode,InnoDB,10,Compact,0,0,16384,0,32768,5397020672,1,"2012-12-04 13:44:31",NULL,NULL,latin1_swedish_ci,NULL,,
11) Yes, I've got an ordinary disk drive, no RAID.
12) MAcAddress is a column that can store an address of several bytes, up to 128 bytes, not only the mac address typical of TCP/IP stack. We tried to use VARBINARY but we had some issues in storing them, so we turned on longblob...
Would be better if I define MacAddress as a VARBINARY(128)?
please let me know if you need further information.
Thank you very much for your support
Best
cghersi