List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:September 3 2010 2:18pm
Subject:Re: Performance problems on MySQL
View as plain text  
What does

SHOW TABLE STATUS LIKE 'table_name'

Say about this table?

-JW

On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira <nullpt@stripped> wrote:

> Hi,
>
> I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and it
> makes a little difference but not enough for the application to run in real
> time processing.
>
> It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.
>
> MySQL is eating 179MB of RAM and 5,4% of CPU.
>
>   PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
>   6229 mysql     455M  179M sleep   58    0   0:03.11 5,4% mysqld/68
>
> The machine has ~1GB of free memory. MySQL and InnoDB has free pages to
> grow and we have ~50% of free CPU time.
>
> Currently I can't use the replication server since the application running
> on top if this BD can only talk to 1 data source.
> At the moment it's also not possible to change the application in order to
> make it use the DB more wisely.
>
> Basically we have a table with lots of selects, lots of update, lots of
> inserts and deletes. Data manipulation is random, doesn't follow any
> specific pattern. All working concurrently.
>
> A big bottleneck is:
>
> 8 queries inside InnoDB, 28 queries in queue
>
> 1 read views open inside InnoDB
>
> Increasing innodb_thread_concurrency might help without causing any
> problems to the overall performance.
>
> Makes total sense if you read:
> http://peter-zaitsev.livejournal.com/9138.html
>
> Thanks in advance.
>
> BR
> AJ
>
>
> On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers <johnny@stripped>wrote:
>
>> Ok, so I'm stumped?
>>
>> What kind of hardware is behind this thing?
>>
>> -JW
>>
>> On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira <nullpt@stripped>wrote:
>>
>>> Hi Johnny,
>>>
>>> mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';
>>>
>>>
> +----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
>>> | id | select_type | table      | type  | possible_keys | key     |
>>> key_len | ref   | rows | Extra |
>>>
>>>
> +----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
>>> |  1 | SIMPLE      | clientinfo | const | PRIMARY       | PRIMARY |
>>> 23      | const |    1 |       |
>>>
>>>
> +----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
>>> 1 row in set (0.53 sec)
>>>
>>> Thanks
>>>
>>> BR
>>> AJ
>>>
>>>
>>>
>
>
> --
> Alexandre Vieira - nullpt@stripped
>
>


-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@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