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