MySQL Forums
Forum List  »  MyISAM

Re: Slow UPDATE's on a large table
Posted by: Srdan Dukic
Date: June 27, 2010 09:16PM

Hi,

The perl code which does the update is below.
...
$dbh->do('LOCK TABLE BaseMessage WRITE');
$query = "UPDATE BaseMessage SET statusId = 3 WHERE baseMessageId= ?";
$updateSth = $dbh->prepare($query) or die "Prepare failed. ERROR: $DBI::errstr\n";
# Update the staus of the records to 'Processed'
foreach $messageId (@processedMessages) {
        $updateSth->execute($messageId) or die "Unable to execute query: $query. ERROR: $DBI::errstr\n";
}
$updateSth->finish();
$dbh->do('UNLOCK TABLES');
...
As you can see the "LOCK" goes on before the loop which runs several update statements.

* SHOW CREATE TABLE tbl\G -- engine, indexes
CREATE TABLE `BaseMessage` (
  `baseMessageId` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `fAdd` varchar(255) NOT NULL,
  `tAdd` varchar(255) NOT NULL,
  `xBG` varchar(255) DEFAULT NULL,
  `envSnd` varchar(255) NOT NULL,
  `rAdd` varchar(255) NOT NULL,
  `eId` int(11) NOT NULL,
  `bcId` int(11) NOT NULL,
  `bcaEId` bigint(20) unsigned NOT NULL,
  `sbsId` bigint(20) unsigned NOT NULL,
  `aId` int(11) NOT NULL,
  `tmpltId` int(11) NOT NULL,
  `rcptDmn` varchar(255) NOT NULL,
  `bcTypeId` int(1) NOT NULL,
  `statusId` int(4) DEFAULT NULL,
  `fName` varchar(255) NOT NULL,
  `eceMssageId` varchar(255) NOT NULL,
  `xBinding` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`baseMessageId`)
) ENGINE=MyISAM AUTO_INCREMENT=15822526 DEFAULT CHARSET=latin1

* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| 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 |
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| BaseMessage | MyISAM |      10 | Dynamic    | 13812671 |            217 |  3005761480 | 281474976710655 |    141750272 |         0 |       15822526 | 2010-03-18 16:11:06 | 2010-06-25 03:22:10 | NULL       | latin1_swedish_ci |     NULL |                |         |
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+

* SHOW VARIABLES LIKE '%buffer%'; -- cache size
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| bulk_insert_buffer_size | 8388608    |
| innodb_buffer_pool_size | 8388608    |
| innodb_log_buffer_size  | 1048576    |
| join_buffer_size        | 131072     |
| key_buffer_size         | 1073741824 |
| myisam_sort_buffer_size | 8388608    |
| net_buffer_length       | 16384      |
| preload_buffer_size     | 32768      |
| read_buffer_size        | 262144     |
| read_rnd_buffer_size    | 524288     |
| sort_buffer_size        | 524288     |
| sql_buffer_result       | OFF        |
+-------------------------+------------+

The update works by finding the 'BaseMessageId' and updating it's value. I would've though that the primary key index is the best index for this kind of update. Is there a more adequate index I could be using?

Options: ReplyQuote


Subject
Views
Written By
Posted
14750
June 24, 2010 10:11PM
6260
June 27, 2010 02:57PM
Re: Slow UPDATE's on a large table
5432
June 27, 2010 09:16PM
5600
June 27, 2010 09:47PM
4233
June 27, 2010 10:36PM
4150
June 27, 2010 11:52PM
3896
June 28, 2010 08:38PM
4031
July 04, 2010 08:28PM


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.