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
