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
