MySQL Forums
Forum List  »  Performance

mysql performance tuning
Posted by: pankaj buddhe
Date: January 06, 2022 09:02AM

HI All, 
My Application is getting hung up due to Mysql slow operation. Application working mostly on these two tables "eventDetails" and "objectDetails" schema below.I need to insert 20 row data in a single bulk insert call and this action is done by 30 processes i.e  20 * 30 = 600 row insert call.If I run a single process (20 * 1 = 20 row insert) then it takes 0.5 sec to execute. If I increase the process count upto 30 (20 * 30 = 600 row insert) then 20 row insertion operation takes more than 3.5 sec. 

What is the threshold of mysql that can ingest an insert query (bulk insert) in one sec according to the following hardware configuration ?  
How to increase the performance of mysql ?
What hardware configuration is required to support my crateria? 

schema

| eventDetails | CREATE TABLE `eventDetails` (
  `eventUID` binary(16) NOT NULL,
  `UID` binary(16) NOT NULL,
  `eventId` int NOT NULL,
  `eventTimestamp` datetime NOT NULL,
  `eventType` int NOT NULL,
  `sessionId` bigint DEFAULT NULL,
  PRIMARY KEY (`eventUID`),
  KEY `eventDetails_UID_fk` (`UID`),
  KEY `eventDetails_eventType_fk` (`eventType`),
  CONSTRAINT `eventDetails_eventType_fk` FOREIGN KEY (`eventType`) REFERENCES `eventTypes` (`eventId`) ON DELETE CASCADE,
  CONSTRAINT `eventDetails_UID_fk` FOREIGN KEY (`UID`) REFERENCES `channel` (`channelUID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |


| objectDetails | CREATE TABLE `objectDetails` (
  `eventUID` binary(16) NOT NULL,
  `objectId` int NOT NULL,
  `objWidth` float DEFAULT NULL,
  `objHeight` float DEFAULT NULL,
  `objColor` int DEFAULT NULL,
  `objDirection` double DEFAULT NULL,
  `objLifeTime` double DEFAULT NULL,
  `objSpeed` double DEFAULT NULL,
  `objSizePercentage` float DEFAULT NULL,
  `objXposPercentage` float DEFAULT NULL,
  `objYposPercentage` float DEFAULT NULL,
  `objClass` int DEFAULT NULL,
  `objBBLeft` float DEFAULT NULL,
  `objBBTop` float DEFAULT NULL,
  `objBBRight` float DEFAULT NULL,
  `objBBBottom` float DEFAULT NULL,
  PRIMARY KEY (`eventUID`),
  KEY `objectDetails_objColor_fk` (`objColor`),
  KEY `objectDetails_objClass_fk` (`objClass`),
  CONSTRAINT `objectDetails_eventUID_fk` FOREIGN KEY (`eventUID`) REFERENCES `eventDetails` (`eventUID`) ON DELETE CASCADE,
  CONSTRAINT `objectDetails_objClass_fk` FOREIGN KEY (`objClass`) REFERENCES `objectClasses` (`objectClassId`) ON DELETE CASCADE,
  CONSTRAINT `objectDetails_objColor_fk` FOREIGN KEY (`objColor`) REFERENCES `objectColors` (`objectColorId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |


Hardware config 
Architecture: x86_64
CPU(s): 72
Model name: Intel(R) Xeon(R) CPU E5-2695 v4 @ 2.10GHz
Ram : 47.0 GiB
Disk type : Hard Drive 
OS: Ubuntu 20.04 

Mysql Ver : mysql Ver 8.0.27-0
Mysql Configuration 
innodb_flush_method = O_DSYNC
innodb_buffer_pool_size=38G
sync_binlog=500
join_buffer_size=256K
innodb_log_file_size=4G
innodb_buffer_pool_instances=8


Thanks 
Pankaj

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql performance tuning
269
January 06, 2022 09:02AM
102
January 06, 2022 09:53AM


Sorry, only registered users may post in this forum.

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.