MySQL Update Issue

Hi,

I have some really terrible experience with MySQL database. I am experiencing issues with one of my database tables which receives lots of UPDATES.

The update queries are ok and working fine but the challenge is that same table receives lots of update on different rows regularly. Sometimes when there is heavy queue some of the update queries will just not execute. - I guess that's caused by timeout. This has causes a lot of losses and my management is not happy.

Can someone help please advise me on what to do.

 

what is table engine? If its MyISAM then change it to InnoDB.

If its InnoDB then share table structure and queries with us to help you optimize them

 

I have changed table engine to InnoDB on test server.

Here's the table structure:

CREATE TABLE `bill` (
`bid` int(11) NOT NULL auto_increment,
`uid` int(11) NOT NULL default '0',
`unitBals` decimal(11,2) NOT NULL default '0.00',
`lastusedate` varchar(12) NOT NULL default '',
PRIMARY KEY (`bid`),
KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14150 ;


here's a sample query
UPDATE bill SET unitBals=1000 WHERE uid=14151;

Hope data will not be affected if I should change the live table to InnoDB too

 

Whenever update query is executed, MyISAM table gets completely locked by default slowing down subsequent concurrent calls whereas InnoDB table gets only row level locking and hence allows multiple update / select queries as well.

I believe changing engine will solve your problem. If it doesn't then please share record count in that table and also the queries.

 

This is really helpful to know but on my live server, there is not InnoDB storage engine option.

I have: MyISAM, HEAP, MEMORY, MERGE and MRG_MyISAM which of this can work well as innodb?

 

nah. in that case concurrent update queries needs to be well optimized to solve the problem for now. However, i strongly recommend that if you are gonna have to many concurrent update / select queries, get InnoDB engine enabled on live server.

Please share queries to see if they can be optimized enough to solve the problem on temporary basis using MyISAM engine.

 

here's a sample query
UPDATE bill SET unitBals=1000 WHERE uid=14151;

I've already sent ticket to hosting company for enabling InnoDB, I believe that will be resolved soon

 

Your query is pretty simple and can't be optimized further.

 

Perhaps, I should just give support time to reply my ticket. I believe that will be done pretty soon.
Thank you so much for the assistant.