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

When creating a table in MySQL with a PK it automatically creates an INDEX,
correct?

The Index_Length: 0 is rather strange..I've created a new INDEX on top of my
PK column on my test system and Index_Length shows a big value different
from 0. Do you think this might have any impact?

mysql> show index from gwtraffic.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         |      548216 |     NULL | NULL   |      | BTREE      |         |
| clientinfo |          1 | userid_idx |            1 | userid      |
A         |      548216 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 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    | 548216 |             62 |
34144256 |               0 |      5783552 |         0 |           NULL |
2010-09-03 17:38:16 | NULL        | NULL       | latin1_swedish_ci |
NULL |                | InnoDB free: 1214464 kB |
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------+
1 row in set (0.00 sec)

I'm trying to stress my test DB but can't measure any different results with
or without the second INDEX.

Regarding the disks.. the DB is updated 20+ times every second. Writing the
log, checkpoint to disk, etc.. can cause that much load?

BR
AJ

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

> I think your MySQL instance is disk bound.
>
> If you look at your iostats, md2, 12 and 22 have a ~10ms wait time before a
> request can be processed. iostat is also reporting those disks are 75%+
> utilized which means they are doing about all they can do.
>
> Anyway you can add more disks? Add faster disks?
>
> I'm not an iostat expert, use my research and recommendations with caution
> =)
>
>
>   ------------------------------
>
> *Johnny Withers*
> jwithers@stripped
> 601.919.2275 x112
>  [image: eCash Software Systems]
>
>
> On Fri, Sep 3, 2010 at 10:37 AM, Alexandre Vieira <nullpt@stripped>wrote:
>
>> 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
>>
>>
>
>
> --
> -----------------------------
> 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