List:General Discussion« Previous MessageNext Message »
From:Alexandre Vieira Date:September 2 2010 4:41pm
Subject:Re: Performance problems on MySQL
View as plain text  
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_ard@stripped> 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:nullpt@stripped]
> Sent: Thursday, September 02, 2010 8:46 AM
> To: John Daisley; johnny@stripped
> Cc: mysql@stripped
> 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
> <daisleyjohn@stripped>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 - nullpt@stripped

Thread
Performance problems on MySQLAlexandre Vieira2 Sep
  • Re: Performance problems on MySQLJangita2 Sep
    • Re: Performance problems on MySQLAlexandre Vieira2 Sep
      • Re: Performance problems on MySQLJangita3 Sep
  • RE: Performance problems on MySQLTravis Ard2 Sep
    • Re: Performance problems on MySQLAlexandre Vieira2 Sep
Re: Performance problems on MySQLAlexandre Vieira3 Sep
  • Re: Performance problems on MySQLJohnny Withers3 Sep
    • Re: Performance problems on MySQLAlexandre Vieira3 Sep
      • Re: Performance problems on MySQLJohnny Withers3 Sep
        • Re: Performance problems on MySQLAlexandre Vieira3 Sep
          • Re: Performance problems on MySQLJohnny Withers3 Sep
            • Re: Performance problems on MySQLAlexandre Vieira3 Sep
              • Re: Performance problems on MySQLJohnny Withers3 Sep
                • Re: Performance problems on MySQLAlexandre Vieira3 Sep
                  • Re: Performance problems on MySQLJohnny Withers3 Sep
                    • Re: Performance problems on MySQLMySQL)5 Sep
  • Re: Performance problems on MySQLmos6 Sep
    • Re: Performance problems on MySQLAlexandre Vieira6 Sep
      • Re: Performance problems on MySQLAlexandre Vieira6 Sep