Hi Travis, Sorry, bad copy/paste. That DELETE statement is wrong.
The application executes: DELETE FROM clientinfo WHERE userid='xxxxxxxxx'; BR AJ On Thu, Sep 2, 2010 at 5:23 PM, Travis Ard <travis_...@hotmail.com> wrote: > Have you considered adding a secondary index on the units column for your > delete queries? > > DELETE FROM clientinfo WHERE units='155618918'; > > -----Original Message----- > From: Alexandre Vieira [mailto:nul...@gmail.com] > Sent: Thursday, September 02, 2010 8:46 AM > To: John Daisley; joh...@pixelated.net > Cc: mysql@lists.mysql.com > Subject: Performance problems on MySQL > > John, Johnny, > > Thanks for the prompt answer. > > mysql> SHOW CREATE TABLE clientinfo; > > +------------+-------------------------------------------------------------- > > ---------------------------------------------------------------------------- > > ---------------------------------------------------------------------------- > > ---------------------------------------------------------------------------- > > ---------------------------------------------------------------------------- > ----------------------------------------------------------------+ > | Table | Create > Table > | > > +------------+-------------------------------------------------------------- > > ---------------------------------------------------------------------------- > > ---------------------------------------------------------------------------- > > ---------------------------------------------------------------------------- > > ---------------------------------------------------------------------------- > ----------------------------------------------------------------+ > | clientinfo | CREATE TABLE `clientinfo` ( > `userid` varchar(21) NOT NULL default '', > `units` float default NULL, > `date_last_query` datetime default NULL, > `last_acc` int(10) unsigned default NULL, > `date_last_units` datetime default NULL, > `notification` int(10) unsigned NOT NULL default '0', > `package` char(1) default NULL, > `user_type` varchar(5) default NULL, > PRIMARY KEY (`userid`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | > > +------------+-------------------------------------------------------------- > > ---------------------------------------------------------------------------- > > ---------------------------------------------------------------------------- > > ---------------------------------------------------------------------------- > > ---------------------------------------------------------------------------- > ----------------------------------------------------------------+ > 1 row in set (0.00 sec) > mysql> SHOW INDEX FROM clientinfo; > > +------------+------------+----------+--------------+-------------+--------- > --+-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | > > +------------+------------+----------+--------------+-------------+--------- > --+-------------+----------+--------+------+------------+---------+ > | clientinfo | 0 | PRIMARY | 1 | userid | > A | 460056 | NULL | NULL | | BTREE | | > > +------------+------------+----------+--------------+-------------+--------- > --+-------------+----------+--------+------+------------+---------+ > 1 row in set (0.00 sec) > > > SELECT * FROM clientinfo WHERE userid='182106617'; > > UPDATE clientinfo SET > > units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units > =now(),notification=0 > WHERE userid='152633876'; > > INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE') > ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1, > date_last_units=now(), notification=0, package='D', user_type='PRE'; > > DELETE FROM clientinfo WHERE units='155618918'; > > There are no other type of queries. > > We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM. > > We also run some other applications in the server, but nothing that > consumes > all the CPU/Memory. The machine has almost 1GB of free memory and 50% of > idle CPU time at any time. > > TIA > > BR > Alex > > > On Thu, Sep 2, 2010 at 1:52 PM, John Daisley > <daisleyj...@googlemail.com>wrote: > > > What is the hardware spec? Anything else running on the box? > > > > Why are you replicating but not making use of the slave? > > > > Can you post the output of SHOW CREATE TABLE? > > > > Regards > > John > > > > > > > > -- Alexandre Vieira - nul...@gmail.com
