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 <joh...@pixelated.net> 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* > jwith...@ecashsoftware.com > 601.919.2275 x112 > [image: eCash Software Systems] > > > On Fri, Sep 3, 2010 at 9:20 AM, Alexandre Vieira <nul...@gmail.com> 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 <joh...@pixelated.net>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 <nul...@gmail.com>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 <joh...@pixelated.net>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 <nul...@gmail.com>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 - nul...@gmail.com >>>> >>>> >>> >>> >>> -- >>> ----------------------------- >>> Johnny Withers >>> 601.209.4985 >>> joh...@pixelated.net >>> >> >> >> >> -- >> Alexandre Vieira - nul...@gmail.com >> >> > > > -- > ----------------------------- > Johnny Withers > 601.209.4985 > joh...@pixelated.net > -- Alexandre Vieira - nul...@gmail.com