My first impression after looking at the table structure is
1 the number of indexes present are very huge and each insert or delete will
act as an extra insert or delete of each and every index created.
2 what type of select stmts are going to hit this table.
3 if it is a primary key select, drop all the remaining indexes.
4 as it is myisam, enable the concurrent insert value to 2, so it insert at the
last and do periodic analyze or optimize.
5 the final option is de normalization.
Thanks,
Suresh Kuna
Sent from BlackBerry® on Airtel
-----Original Message-----
From: Willy Mularto <sangpr...@gmail.com>
Date: Sun, 3 Jan 2010 18:12:08
To: <sureshkumar...@gmail.com>
Cc: <mysql@lists.mysql.com>
Subject: Re: High Overhead On Active Insert And Delete Table
The engine is using MyISAM. And here is the structure:
CREATE TABLE IF NOT EXISTS `ie_push` (
`sql_id` bigint(20) NOT NULL auto_increment,
`momt` enum('MO','MT','DLR') default NULL,
`sender` varchar(20) default NULL,
`receiver` varchar(20) default NULL,
`udhdata` blob,
`msgdata` text,
`time` bigint(20) default NULL,
`smsc_id` varchar(255) default NULL,
`service` varchar(255) default NULL,
`account` varchar(255) default NULL,
`id` bigint(20) default NULL,
`sms_type` bigint(20) default NULL,
`mclass` bigint(20) default NULL,
`mwi` bigint(20) default NULL,
`coding` bigint(20) default NULL,
`compress` bigint(20) default NULL,
`validity` bigint(20) default NULL,
`deferred` bigint(20) default NULL,
`dlr_mask` bigint(20) default NULL,
`dlr_url` varchar(255) default NULL,
`pid` bigint(20) default NULL,
`alt_dcs` bigint(20) default NULL,
`rpi` bigint(20) default NULL,
`charset` varchar(255) default NULL,
`boxc_id` varchar(255) default NULL,
`binfo` varchar(255) default NULL,
`priority` tinyint(4) default NULL,
`custom` varchar(255) default NULL,
`additional` varchar(255) default NULL,
`service_id` varchar(50) default NULL,
`fid` varchar(50) default NULL,
`msgid` varchar(50) default NULL,
`insertdate` datetime default NULL,
PRIMARY KEY (`sql_id`),
KEY `momt` (`momt`),
KEY `sender` (`sender`),
KEY `receiver` (`receiver`),
KEY `time` (`time`),
KEY `service` (`service`),
KEY `account` (`account`),
KEY `id` (`id`),
KEY `sms_type` (`sms_type`),
KEY `mclass` (`mclass`),
KEY `mwi` (`mwi`),
KEY `coding` (`coding`),
KEY `compress` (`compress`),
KEY `validity` (`validity`),
KEY `deferred` (`deferred`),
KEY `dlr_mask` (`dlr_mask`),
KEY `dlr_url` (`dlr_url`),
KEY `pid` (`pid`),
KEY `rpi` (`rpi`),
KEY `alt_dcs` (`alt_dcs`),
KEY `charset` (`charset`),
KEY `boxc_id` (`boxc_id`),
KEY `binfo` (`binfo`),
KEY `priority` (`priority`),
KEY `custom` (`custom`),
KEY `additional` (`additional`),
KEY `service_id` (`service_id`),
KEY `insertdate` (`insertdate`),
KEY `fid` (`fid`),
KEY `msgid` (`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Willy
sangpr...@gmail.com
On Jan 3, 2010, at 6:00 PM, sureshkumar...@gmail.com wrote:
> Hi Willy,
> What is the engine you are using for the table? Paste the table
> structure.
> Thanks,
> Suresh Kuna
> ------Original Message------
> From: Willy Mularto
> To: mysql@lists.mysql.com
> Subject: High Overhead On Active Insert And Delete Table
> Sent: Jan 3, 2010 4:22 PM
>
> Hi list,
> I have a table which is very active in operation INSERT INTO and
> DELETE, approximately there will be around 2millions INSERT and DELETE
> operation per day. And I see the overhead is getting very high, I must
> do OPTIMIZE TABLE query every time. Is there any other option to solve
> this? TIA.
>
>
>
> Willy
> sangpr...@gmail.com
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com
>
>
>
> Sent from BlackBerry® on Airtel
