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
