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 <[email protected]> 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*
> [email protected]
> 601.919.2275 x112
> [image: eCash Software Systems]
>
>
> On Fri, Sep 3, 2010 at 9:20 AM, Alexandre Vieira <[email protected]> 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 <[email protected]>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 <[email protected]>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 <[email protected]>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 <[email protected]>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 - [email protected]
>>>>
>>>>
>>>
>>>
>>> --
>>> -----------------------------
>>> Johnny Withers
>>> 601.209.4985
>>> [email protected]
>>>
>>
>>
>>
>> --
>> Alexandre Vieira - [email protected]
>>
>>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> [email protected]
>
--
Alexandre Vieira - [email protected]
