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
