List:General Discussion« Previous MessageNext Message »
From:Alexandre Vieira Date:September 3 2010 3:37pm
Subject:Re: Performance problems on MySQL
View as plain text  
Hi,

The DB is working on /var, which is md2 / md12 / md22.

                  extended device statistics
device       r/s    w/s   kr/s   kw/s wait actv  svc_t  %w  %b

md2          0.1   80.0    0.4  471.4  0.0  1.0   12.2   0  94
md10         0.0    5.7    0.0   78.8  0.0  0.1   19.7   0   9
md11         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
md12         0.0   80.0    0.3  471.4  0.0  0.8    9.9   0  76
md20         0.0    5.7    0.0   78.8  0.0  0.1   21.1   0   9
md21         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
md22         0.0   80.0    0.1  471.3  0.0  0.8   10.6   0  81
sd0          0.2   86.8    0.3  550.5  0.0  0.9   10.6   0  78
sd1          0.2   86.8    0.2  550.4  0.0  1.0   11.3   0  83
sd30         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
nfs1         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
                  extended device statistics
device       r/s    w/s   kr/s   kw/s wait actv  svc_t  %w  %b
md0          0.0    5.6    0.0   83.2  0.0  0.2   28.0   0  10
md1          0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
md2          0.1   84.2    0.7  527.2  0.0  1.0   11.8   0  93
md10         0.0    5.6    0.0   83.2  0.0  0.1   19.0   0   8
md11         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
md12         0.0   84.2    0.3  527.2  0.0  0.8    9.7   0  77
md20         0.0    5.6    0.0   83.2  0.0  0.1   19.9   0   8
md21         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
md22         0.0   84.1    0.4  527.2  0.0  0.9   10.3   0  82
sd0          0.2   91.1    0.3  610.7  0.0  0.9   10.4   0  79
sd1          0.2   91.0    0.4  610.7  0.0  1.0   11.0   0  84
sd30         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
nfs1         0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0

I really can't say why Index_Lenght is 0... It might be something with the
index?

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         |      503836 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'clientinfo';
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
| 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                |
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
| clientinfo | InnoDB |      10 | Compact    | 508170 |             81 |
41500672 |               0 |            0 |         0 |           NULL |
2010-09-01 03:21:36 | NULL        | NULL       | latin1_swedish_ci |
NULL |                | InnoDB free: 276480 kB |
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
1 row in set (0.00 sec)

BR
AJ

On Fri, Sep 3, 2010 at 3:46 PM, Johnny Withers <johnny@stripped> wrote:

> Very confusing...
>
> Why is index_length zero ?
>
> On top of that, there's only 500K rows in the table with a data size of
> 41MB. Maybe InnoDB is flushing to disk too often?
>
> What's the output of iostat -dxk 60 ? (run for a minute+ to get 2 output
> girds)
>
>
>
>   ------------------------------
>
> *Johnny Withers*
> jwithers@stripped
> 601.919.2275 x112
>  [image: eCash Software Systems]
>
>
> On Fri, Sep 3, 2010 at 9:20 AM, Alexandre Vieira <nullpt@stripped> wrote:
>
>> Hi,
>>
>> mysql> SHOW TABLE STATUS LIKE 'clientinfo';
>>
>>
> +------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
>> | 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                |
>>
>>
> +------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
>> | clientinfo | InnoDB |      10 | Compact    | 504762 |             82
>> |    41500672 |               0 |            0 |         0 |           NULL
>> | 2010-09-01 03:21:36 | NULL        | NULL       | latin1_swedish_ci |
>> NULL |                | InnoDB free: 276480 kB |
>>
>>
> +------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
>> 1 row in set (0.02 sec)
>>
>> BR
>> AJ
>>
>>
>> On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers
> <johnny@stripped>wrote:
>>
>>> 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
>>>
>>
>>
>>
>> --
>> Alexandre Vieira - nullpt@stripped
>>
>>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@stripped
>



-- 
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