MySQL Forums
Forum List  »  InnoDB

randomly Very slow insert for one 2G size table
Posted by: Alex Ng
Date: July 14, 2015 01:40AM

we use 5.1.60-enterprise-commercial-advanced-log, on HP box.

Sometimes we randomly having very slow insert for table following tale, which is 4M rows, and have around 2G size ibd file.

general the insert can be done in 2 seconds, but we met 50s+ recently, really need your help or advices?


the insert is very simple and small, like:
insert into creative_rendition(......) values(....)

creative__xxxxx | CREATE TABLE `creative_rendition` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`creative_id` bigint(20) NOT NULL,
`rendition_type` enum('PREVIEW_ONLY','SOURCE_ONLY','PRODUCTION') NOT NULL DEFAULT 'PRODUCTION' COMMENT 'If "PREVIEW_ONLY" the rendition is only meant for showing within the UI.',
`source_rendition` bigint(20) DEFAULT NULL COMMENT 'FK(creative_rendition.id). If not null, this rendition was derived from the source rendition. If the source rendition changes, this one should be re-derived.',
`primary_creative_rendition_asset_id` bigint(20) DEFAULT NULL COMMENT 'FK(creative_rendition_asset.id).The "primary" asset of the creative. This is what we will deliver. If null, the content type must be set in content_type_id field of this table',
`height` int(11) DEFAULT NULL COMMENT 'The native height/width of the creative rendition. If the rendition has no native height width (e.g., a purely vector creative rendition) then these may be left null.',
`width` int(11) DEFAULT NULL,
`device_pixel_ratio` decimal(6,2) NOT NULL DEFAULT '1.00' COMMENT 'indicates the device pixel ratio this rendition(usually an image) might fit to',
`file_size` bigint(20) DEFAULT NULL COMMENT 'total size of all billable creative rendition assets that saved in FW CDN, in Bytes',
`physical_file_size` bigint(20) DEFAULT NULL COMMENT 'total size of all creative rendition assets, in Bytes',
`aspect_ratio_id` bigint(20) DEFAULT NULL COMMENT 'Aspect ratio is only used in the case that the creative has no height/width (for example its SVG) FK(lu_aspect_ratio.id)',
`bitrate` int(11) DEFAULT NULL COMMENT 'Overall bitrate of the rendition (if applicable) in kbps. Not of any particular file, but the minimum bitrate that must be supported by the users connection in order to use the creative. ',
`frames_per_second` decimal(5,2) DEFAULT NULL,
`preference` int(11) NOT NULL DEFAULT '0' COMMENT 'The preference of the creative rendition. For example if an ad has a Flash and a Silverlight creative, if the advertiser prefers the SilverLight reidtion when both environments are compatible the Silverlight rendition should be given a higher precedence. ',
`content_type_id` bigint(20) DEFAULT NULL COMMENT 'FK(lu_content_type.id). The content type to use when the creative rendition has no assets. If the creative rendition has a root asset this field is ignored.',
`support_click` tinyint(1) DEFAULT '1',
`status` enum('ACTIVE','IN_ACTIVE','IN_VALID') NOT NULL DEFAULT 'ACTIVE' COMMENT 'status of creative rendition',
`validation_info` text,
`qualification` bigint(20) NOT NULL DEFAULT '0' COMMENT 'bit-masked flags for all possible qulifications',
`internal_id` varchar(255) DEFAULT NULL,
`network_id` bigint(20) NOT NULL,
`ssl_ready` tinyint(1) DEFAULT '1',
`https_compatibility` enum('HTTP','HTTPS','BOTH') NOT NULL DEFAULT 'BOTH',
`ui_validation_info` text,
`is_multiple_ads_vast_tag` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'indicating whether a vast 3 tag contains multiple ads',
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `name_creative_id` (`name`,`creative_id`),
KEY `fk_creative_rendition_creative_id` (`creative_id`),
KEY `fk_creative_rendition_content_type_id` (`content_type_id`),
KEY `fk_network_id` (`network_id`),
KEY `source_rendition` (`source_rendition`),
CONSTRAINT `fk_creative_rendition_content_type_id` FOREIGN KEY (`content_type_id`) REFERENCES `lu_content_type` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_creative_rendition_creative_id` FOREIGN KEY (`creative_id`) REFERENCES `creative` (`id`) ON UPDATE CASCADE,
CONSTRAINT `fk_creative_rendition_network_id` FOREIGN KEY (`network_id`) REFERENCES `network` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4187720 DEFAULT CHARSET=utf8 |

Options: ReplyQuote


Subject
Views
Written By
Posted
randomly Very slow insert for one 2G size table
1646
July 14, 2015 01:40AM


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.