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