Re: Performance problems on MySQL
Hi List, In a 20m interval in our max load I have: OS WAIT ARRAY INFO: reservation count 637, signal count 625 Mutex spin waits 0, rounds 19457, OS waits 428 RW-shared spins 238, OS waits 119; RW-excl spins 13, OS waits 8 (The values are the difference between the start and end of this 20m interval) The machine has 2 CPU's and usually has 40-50% of idle CPU. Our workload consists on lots of parallel simple queries (SELECTs and UPDATEs with a simple condition on the PK) on a 500k record/40MB table with an INDEX on the PK. | innodb_sync_spin_loops | 20 | | innodb_thread_concurrency | 16 | | innodb_thread_sleep_delay | 1 | I've been sampling my innodb status and there are always "16 queries inside InnoDB" and some 20-30 in queue. Therefore lowering thread_sleep_delay won't help. Since I have 47 spin rounds per OS Wait, would innodb gain something with rising sync_spin_loops a little bit? Also, should I be capping thread_concurrency with a 2 CPU machine? Unfortunately this machine only has 2 RAID1 disks. I can't spread the disk load (datafile/logfiles) between disks. extended device statistics r/sw/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device 0.0 83.70.0 379.3 0.0 1.00.1 11.5 1 94 d2 (/var) Usually the iostat busy indicator is near 100%. Any hints on something I could tune to have less "OS Waits" and help with the Disk I/O? = 100906 18:33:40 INNODB MONITOR OUTPUT = Per second averages calculated from the last 47 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 266140, signal count 259267 Mutex spin waits 0, rounds 7407879, OS waits 179189 RW-shared spins 93878, OS waits 46196; RW-excl spins 9473, OS waits 7311 --- LOG --- Log sequence number 62 2833945222 Log flushed up to 62 2833944847 Last checkpoint at 62 2828803314 1 pending log writes, 0 pending chkp writes 18419416 log i/o's done, 37.64 log i/o's/second -- ROW OPERATIONS -- 16 queries inside InnoDB, 27 queries in queue 1 read views open inside InnoDB Main thread id 11, state: sleeping Number of rows inserted 603196, updated 9006533, deleted 111028, read 30145300 0.17 inserts/s, 18.49 updates/s, 0.00 deletes/s, 41.47 reads/s If nothing else can be done I'll advise the client to acquire new HW for this BD. By the way, upgrading from 5.0.45-log to 5.1.50 would make a huge difference in terms of performance? BR AJ On Mon, Sep 6, 2010 at 10:46 AM, Alexandre Vieira wrote: > Hi, > > We're chaning it to INT(9). Apparently someone remembered to change the > type of data in this field from an alphanumeric value to an INT(9). > > I'm going to change this asap. > > Thanks > > BR > AJ > > > On Mon, Sep 6, 2010 at 5:17 AM, mos wrote: > >> At 04:44 AM 9/3/2010, Alexandre Vieira 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, >> Do you have UserId declared as CHAR? It looks numeric to me. If it is >> stored as an integer then don't use the ' ' in the select statement >> otherwise it needs to convert it. >> If UserId values are integers and you have the column defined as CHAR, >> then declare the column UserId as integer or BigInt and the searches should >> be faster than searching on CHAR. >> >> Mike >> >> >> >> >> On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers >>> wrote: >>> >>> > What about an explain of this query: >>> > >>> > >>> > SELECT * FROM clientinfo WHERE userid='182106617'; >>> > >>> > -JW >>> > >>> > >>> > On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira >>> wrote: >>> > >>> >> John, Johnny, >>> >> >>> >> Thanks for the prompt answer. >>> >> >>> >> mysql> SHOW CREATE TABLE clientinfo; >>> >> >>> >> >>> ++--+ >>> >>
Re: Performance problems on MySQL
Hi,
We're chaning it to INT(9). Apparently someone remembered to change the type
of data in this field from an alphanumeric value to an INT(9).
I'm going to change this asap.
Thanks
BR
AJ
On Mon, Sep 6, 2010 at 5:17 AM, mos wrote:
> At 04:44 AM 9/3/2010, Alexandre Vieira 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,
> Do you have UserId declared as CHAR? It looks numeric to me. If it is
> stored as an integer then don't use the ' ' in the select statement
> otherwise it needs to convert it.
> If UserId values are integers and you have the column defined as CHAR, then
> declare the column UserId as integer or BigInt and the searches should be
> faster than searching on CHAR.
>
> Mike
>
>
>
>
> On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers
>> wrote:
>>
>> > What about an explain of this query:
>> >
>> >
>> > SELECT * FROM clientinfo WHERE userid='182106617';
>> >
>> > -JW
>> >
>> >
>> > On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira
>> wrote:
>> >
>> >> John, Johnny,
>> >>
>> >> Thanks for the prompt answer.
>> >>
>> >> mysql> SHOW CREATE TABLE clientinfo;
>> >>
>> >>
>> ++--+
>> >> | Table | Create
>> >> Table
>> >> |
>> >>
>> >>
>> ++--+
>> >> | clientinfo | CREATE TABLE `clientinfo` (
>> >> `userid` varchar(21) NOT NULL default '',
>> >> `units` float default NULL,
>> >> `date_last_query` datetime default NULL,
>> >> `last_acc` int(10) unsigned default NULL,
>> >> `date_last_units` datetime default NULL,
>> >> `notification` int(10) unsigned NOT NULL default '0',
>> >> `package` char(1) default NULL,
>> >> `user_type` varchar(5) default NULL,
>> >> PRIMARY KEY (`userid`)
>> >> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>> >>
>> >>
>> ++--+
>> >> 1 row in set (0.00 sec)
>> >> 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 | 460056 | NULL | NULL | | BTREE |
>> |
>> >>
>> >>
>> +++--+--+-+---+-+--++--++-+
>> >> 1 row in set (0.00 sec)
>> >>
>> >>
>> >> SELECT * FROM clientinfo WHERE userid='182106617';
>> >>
>> >> UPDATE clientinfo SET
>> >>
>> units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0
>> >> WHERE userid='152633876';
>> >>
>> >> INSERT INTO clientinfo VALUES
>> >> ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE
>> >> units=101.0, date_last_query=now(), last_acc=1, date_las
Re: Performance problems on MySQL
At 04:44 AM 9/3/2010, Alexandre Vieira 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,
Do you have UserId declared as CHAR? It looks numeric to me. If it is
stored as an integer then don't use the ' ' in the select statement
otherwise it needs to convert it.
If UserId values are integers and you have the column defined as CHAR, then
declare the column UserId as integer or BigInt and the searches should be
faster than searching on CHAR.
Mike
On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers wrote:
> What about an explain of this query:
>
>
> SELECT * FROM clientinfo WHERE userid='182106617';
>
> -JW
>
>
> On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira wrote:
>
>> John, Johnny,
>>
>> Thanks for the prompt answer.
>>
>> mysql> SHOW CREATE TABLE clientinfo;
>>
>>
++--+
>> | Table | Create
>> Table
>> |
>>
>>
++--+
>> | clientinfo | CREATE TABLE `clientinfo` (
>> `userid` varchar(21) NOT NULL default '',
>> `units` float default NULL,
>> `date_last_query` datetime default NULL,
>> `last_acc` int(10) unsigned default NULL,
>> `date_last_units` datetime default NULL,
>> `notification` int(10) unsigned NOT NULL default '0',
>> `package` char(1) default NULL,
>> `user_type` varchar(5) default NULL,
>> PRIMARY KEY (`userid`)
>> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>>
>>
++--+
>> 1 row in set (0.00 sec)
>> 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 | 460056 | NULL | NULL | |
BTREE | |
>>
>>
+++--+--+-+---+-+--++--++-+
>> 1 row in set (0.00 sec)
>>
>>
>> SELECT * FROM clientinfo WHERE userid='182106617';
>>
>> UPDATE clientinfo SET
>>
units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0
>> WHERE userid='152633876';
>>
>> INSERT INTO clientinfo VALUES
>> ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE
>> units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(),
>> notification=0, package='D', user_type='PRE';
>>
>> DELETE FROM clientinfo WHERE units='155618918';
>>
>> There are no other type of queries.
>>
>> We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.
>>
>> We also run some other applications in the server, but nothing that
>> consumes all the CPU/Memory. The machine has almost 1GB of free memory and
>> 50% of idle CPU time at any time.
>>
>> TIA
>>
>> BR
>> Alex
>>
>>
>> --
>> Alexandre Vieira - nul...@gmail.com
Re: Performance problems on MySQL
On 9/3/2010 3:15 PM, Johnny Withers wrote: It seems that when your index is PRIMARY on InnoDB tables, it's magic and is part of the data thereby it is not included in the index_length field. I have never noticed this. I don't think adding a new index will make a difference. You could try moving your log files to a different disk array than where your data is. If you have binary and query logging enabled, it's probably a good idea. Johnny is correct. The PRIMARY KEY to an InnoDB table is indeed part of the data: http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html That explains why there is no length to this index. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance problems on MySQL
It seems that when your index is PRIMARY on InnoDB tables, it's magic and is part of the data thereby it is not included in the index_length field. I have never noticed this. I don't think adding a new index will make a difference. You could try moving your log files to a different disk array than where your data is. If you have binary and query logging enabled, it's probably a good idea. -- *Johnny Withers* jwith...@ecashsoftware.com 601.919.2275 x112 [image: eCash Software Systems] On Fri, Sep 3, 2010 at 12:45 PM, Alexandre Vieira wrote: > 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 > > >
Re: Performance problems on MySQL
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 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 wrote: > >> Hi, >> >> The DB is working on /var, which is md2 / md12 / md22. >> >> extended device statistics >> device r/sw/s kr/s kw/s wait actv svc_t %w %b >> >> md2 0.1 80.00.4 471.4 0.0 1.0 12.2 0 94 >> md10 0.05.70.0 78.8 0.0 0.1 19.7 0 9 >> md11 0.00.00.00.0 0.0 0.00.0 0 0 >> md12 0.0 80.00.3 471.4 0.0 0.89.9 0 76 >> md20 0.05.70.0 78.8 0.0 0.1 21.1 0 9 >> md21 0.00.00.00.0 0.0 0.00.0 0 0 >> md22 0.0 80.00.1 471.3 0.0 0.8 10.6 0 81 >> sd0 0.2 86.80.3 550.5 0.0 0.9 10.6 0 78 >> sd1 0.2 86.80.2 550.4 0.0 1.0 11.3 0 83 >> sd30 0.00.00.00.0 0.0 0.00.0 0 0 >> nfs1 0.00.00.00.0 0.0 0.00.0 0 0 >> extended device statistics >> device r/sw/s kr/s kw/s wait actv svc_t %w %b >> md0 0.05.60.0 83.2 0.0 0.2 28.0 0 10 >> md1 0.00.00.00.0 0.0 0.00.0 0 0 >> md2 0.1 84.20.7 527.2 0.0 1.0 11.8 0 93 >> md10 0.05.60.0 83.2 0.0 0.1 19.0 0 8 >> md11 0.00.00.00.0 0.0 0.00.0 0 0 >> md12 0.0 84.20.3 527.2 0.0 0.89.7 0 77 >> md
Re: Performance problems on MySQL
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 wrote: > Hi, > > The DB is working on /var, which is md2 / md12 / md22. > > extended device statistics > device r/sw/s kr/s kw/s wait actv svc_t %w %b > > md2 0.1 80.00.4 471.4 0.0 1.0 12.2 0 94 > md10 0.05.70.0 78.8 0.0 0.1 19.7 0 9 > md11 0.00.00.00.0 0.0 0.00.0 0 0 > md12 0.0 80.00.3 471.4 0.0 0.89.9 0 76 > md20 0.05.70.0 78.8 0.0 0.1 21.1 0 9 > md21 0.00.00.00.0 0.0 0.00.0 0 0 > md22 0.0 80.00.1 471.3 0.0 0.8 10.6 0 81 > sd0 0.2 86.80.3 550.5 0.0 0.9 10.6 0 78 > sd1 0.2 86.80.2 550.4 0.0 1.0 11.3 0 83 > sd30 0.00.00.00.0 0.0 0.00.0 0 0 > nfs1 0.00.00.00.0 0.0 0.00.0 0 0 > extended device statistics > device r/sw/s kr/s kw/s wait actv svc_t %w %b > md0 0.05.60.0 83.2 0.0 0.2 28.0 0 10 > md1 0.00.00.00.0 0.0 0.00.0 0 0 > md2 0.1 84.20.7 527.2 0.0 1.0 11.8 0 93 > md10 0.05.60.0 83.2 0.0 0.1 19.0 0 8 > md11 0.00.00.00.0 0.0 0.00.0 0 0 > md12 0.0 84.20.3 527.2 0.0 0.89.7 0 77 > md20 0.05.60.0 83.2 0.0 0.1 19.9 0 8 > md21 0.00.00.00.0 0.0 0.00.0 0 0 > md22 0.0 84.10.4 527.2 0.0 0.9 10.3 0 82 > sd0 0.2 91.10.3 610.7 0.0 0.9 10.4 0 79 > sd1 0.2 91.00.4 610.7 0.0 1.0 11.0 0 84 > sd30 0.00.00.00.0 0.0 0.00.0 0 0 > nfs1 0.00.00.00.0 0.0 0.00.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 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
Re: Performance problems on MySQL
Hi, The DB is working on /var, which is md2 / md12 / md22. extended device statistics device r/sw/s kr/s kw/s wait actv svc_t %w %b md2 0.1 80.00.4 471.4 0.0 1.0 12.2 0 94 md10 0.05.70.0 78.8 0.0 0.1 19.7 0 9 md11 0.00.00.00.0 0.0 0.00.0 0 0 md12 0.0 80.00.3 471.4 0.0 0.89.9 0 76 md20 0.05.70.0 78.8 0.0 0.1 21.1 0 9 md21 0.00.00.00.0 0.0 0.00.0 0 0 md22 0.0 80.00.1 471.3 0.0 0.8 10.6 0 81 sd0 0.2 86.80.3 550.5 0.0 0.9 10.6 0 78 sd1 0.2 86.80.2 550.4 0.0 1.0 11.3 0 83 sd30 0.00.00.00.0 0.0 0.00.0 0 0 nfs1 0.00.00.00.0 0.0 0.00.0 0 0 extended device statistics device r/sw/s kr/s kw/s wait actv svc_t %w %b md0 0.05.60.0 83.2 0.0 0.2 28.0 0 10 md1 0.00.00.00.0 0.0 0.00.0 0 0 md2 0.1 84.20.7 527.2 0.0 1.0 11.8 0 93 md10 0.05.60.0 83.2 0.0 0.1 19.0 0 8 md11 0.00.00.00.0 0.0 0.00.0 0 0 md12 0.0 84.20.3 527.2 0.0 0.89.7 0 77 md20 0.05.60.0 83.2 0.0 0.1 19.9 0 8 md21 0.00.00.00.0 0.0 0.00.0 0 0 md22 0.0 84.10.4 527.2 0.0 0.9 10.3 0 82 sd0 0.2 91.10.3 610.7 0.0 0.9 10.4 0 79 sd1 0.2 91.00.4 610.7 0.0 1.0 11.0 0 84 sd30 0.00.00.00.0 0.0 0.00.0 0 0 nfs1 0.00.00.00.0 0.0 0.00.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 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 wrote: > >> Hi, >> >> mysql> SHOW TABLE STATUS LIKE 'clientinfo'; >> >> +++-++++-+-+--+---++-+-++---+--+++ >> | Name | Engine | Version | Row_format | Rows
Re: Performance problems on MySQL
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 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 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 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 580 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 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 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
Re: Performance problems on MySQL
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 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 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 580 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 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 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
Re: Performance problems on MySQL
What does SHOW TABLE STATUS LIKE 'table_name' Say about this table? -JW On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira 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 580 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 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 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
Re: Performance problems on MySQL
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 580 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 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 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
Re: Performance problems on MySQL
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 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 > > >
Re: Performance problems on MySQL
On 02/09/2010 6:05 p, Alexandre Vieira wrote: Hi Jangita, I'm 15779 innodb_buffer_pool_pages_free from a total of 22400. That's 246MB of 350MB free. | Innodb_buffer_pool_pages_data | 6020 | | Innodb_buffer_pool_pages_dirty| 1837 | | Innodb_buffer_pool_pages_flushed | 673837 | | Innodb_buffer_pool_pages_free | 15779 | | Innodb_buffer_pool_pages_latched | 0 | | Innodb_buffer_pool_pages_misc | 601| | Innodb_buffer_pool_pages_total| 22400 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_ahead_seq | 0 | | Innodb_buffer_pool_read_requests | 48471963 | | Innodb_buffer_pool_reads | 3497 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 21700478 | Why would I need to increase? Thanks BR AJ I'm guessing (just a guess) that you have alot free buffer_pool_size because mysql doesn't use it because it cannot fit information into it at one go so doesn't use it at all? Try and up it; if it doesn't work you could always set it back. -- Jangita | +256 76 91 8383 | Y! & MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance problems on MySQL
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
On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers wrote:
> What about an explain of this query:
>
>
> SELECT * FROM clientinfo WHERE userid='182106617';
>
> -JW
>
>
> On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira wrote:
>
>> John, Johnny,
>>
>> Thanks for the prompt answer.
>>
>> mysql> SHOW CREATE TABLE clientinfo;
>>
>> ++--+
>> | Table | Create
>> Table
>> |
>>
>> ++--+
>> | clientinfo | CREATE TABLE `clientinfo` (
>> `userid` varchar(21) NOT NULL default '',
>> `units` float default NULL,
>> `date_last_query` datetime default NULL,
>> `last_acc` int(10) unsigned default NULL,
>> `date_last_units` datetime default NULL,
>> `notification` int(10) unsigned NOT NULL default '0',
>> `package` char(1) default NULL,
>> `user_type` varchar(5) default NULL,
>> PRIMARY KEY (`userid`)
>> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>>
>> ++--+
>> 1 row in set (0.00 sec)
>> 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 | 460056 | NULL | NULL | | BTREE | |
>>
>> +++--+--+-+---+-+--++--++-+
>> 1 row in set (0.00 sec)
>>
>>
>> SELECT * FROM clientinfo WHERE userid='182106617';
>>
>> UPDATE clientinfo SET
>> units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0
>> WHERE userid='152633876';
>>
>> INSERT INTO clientinfo VALUES
>> ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE
>> units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(),
>> notification=0, package='D', user_type='PRE';
>>
>> DELETE FROM clientinfo WHERE units='155618918';
>>
>> There are no other type of queries.
>>
>> We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.
>>
>> We also run some other applications in the server, but nothing that
>> consumes all the CPU/Memory. The machine has almost 1GB of free memory and
>> 50% of idle CPU time at any time.
>>
>> TIA
>>
>> BR
>> Alex
>>
>>
>> --
>> Alexandre Vieira - nul...@gmail.com
>>
>>
>
>
> --
> -
> Johnny Withers
> 601.209.4985
> joh...@pixelated.net
>
--
Alexandre Vieira - nul...@gmail.com
Re: Performance problems on MySQL
Hi Travis,
Sorry, bad copy/paste. That DELETE statement is wrong.
The application executes:
DELETE FROM clientinfo WHERE userid='x';
BR
AJ
On Thu, Sep 2, 2010 at 5:23 PM, Travis Ard wrote:
> Have you considered adding a secondary index on the units column for your
> delete queries?
>
> DELETE FROM clientinfo WHERE units='155618918';
>
> -Original Message-
> From: Alexandre Vieira [mailto:nul...@gmail.com]
> Sent: Thursday, September 02, 2010 8:46 AM
> To: John Daisley; joh...@pixelated.net
> Cc: mysql@lists.mysql.com
> Subject: Performance problems on MySQL
>
> John, Johnny,
>
> Thanks for the prompt answer.
>
> mysql> SHOW CREATE TABLE clientinfo;
>
> ++--
>
>
>
>
>
>
>
>
> +
> | Table | Create
> Table
> |
>
> ++--
>
>
>
>
>
>
>
>
> +
> | clientinfo | CREATE TABLE `clientinfo` (
> `userid` varchar(21) NOT NULL default '',
> `units` float default NULL,
> `date_last_query` datetime default NULL,
> `last_acc` int(10) unsigned default NULL,
> `date_last_units` datetime default NULL,
> `notification` int(10) unsigned NOT NULL default '0',
> `package` char(1) default NULL,
> `user_type` varchar(5) default NULL,
> PRIMARY KEY (`userid`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>
> ++--
>
>
>
>
>
>
>
>
> +
> 1 row in set (0.00 sec)
> 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 | 460056 | NULL | NULL | | BTREE | |
>
> +++--+--+-+-
> --+-+--++--++-+
> 1 row in set (0.00 sec)
>
>
> SELECT * FROM clientinfo WHERE userid='182106617';
>
> UPDATE clientinfo SET
>
> units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units
> =now(),notification=0
> WHERE userid='152633876';
>
> INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE')
> ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1,
> date_last_units=now(), notification=0, package='D', user_type='PRE';
>
> DELETE FROM clientinfo WHERE units='155618918';
>
> There are no other type of queries.
>
> We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.
>
> We also run some other applications in the server, but nothing that
> consumes
> all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
> idle CPU time at any time.
>
> TIA
>
> BR
> Alex
>
>
> On Thu, Sep 2, 2010 at 1:52 PM, John Daisley
> wrote:
>
> > What is the hardware spec? Anything else running on the box?
> >
> > Why are you replicating but not making use of the slave?
> >
> > Can you post the output of SHOW CREATE TABLE?
> >
> > Regards
> > John
> >
> >
> >
>
>
--
Alexandre Vieira - nul...@gmail.com
RE: Performance problems on MySQL
Have you considered adding a secondary index on the units column for your
delete queries?
DELETE FROM clientinfo WHERE units='155618918';
-Original Message-
From: Alexandre Vieira [mailto:nul...@gmail.com]
Sent: Thursday, September 02, 2010 8:46 AM
To: John Daisley; joh...@pixelated.net
Cc: mysql@lists.mysql.com
Subject: Performance problems on MySQL
John, Johnny,
Thanks for the prompt answer.
mysql> SHOW CREATE TABLE clientinfo;
++--
+
| Table | Create
Table
|
++--
+
| clientinfo | CREATE TABLE `clientinfo` (
`userid` varchar(21) NOT NULL default '',
`units` float default NULL,
`date_last_query` datetime default NULL,
`last_acc` int(10) unsigned default NULL,
`date_last_units` datetime default NULL,
`notification` int(10) unsigned NOT NULL default '0',
`package` char(1) default NULL,
`user_type` varchar(5) default NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
++--
+
1 row in set (0.00 sec)
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 | 460056 | NULL | NULL | | BTREE | |
+++--+--+-+-
--+-+--++--++-+
1 row in set (0.00 sec)
SELECT * FROM clientinfo WHERE userid='182106617';
UPDATE clientinfo SET
units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units
=now(),notification=0
WHERE userid='152633876';
INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE')
ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1,
date_last_units=now(), notification=0, package='D', user_type='PRE';
DELETE FROM clientinfo WHERE units='155618918';
There are no other type of queries.
We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.
We also run some other applications in the server, but nothing that consumes
all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
idle CPU time at any time.
TIA
BR
Alex
On Thu, Sep 2, 2010 at 1:52 PM, John Daisley
wrote:
> What is the hardware spec? Anything else running on the box?
>
> Why are you replicating but not making use of the slave?
>
> Can you post the output of SHOW CREATE TABLE?
>
> Regards
> John
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance problems on MySQL
Hi Jangita, I'm 15779 innodb_buffer_pool_pages_free from a total of 22400. That's 246MB of 350MB free. | Innodb_buffer_pool_pages_data | 6020 | | Innodb_buffer_pool_pages_dirty| 1837 | | Innodb_buffer_pool_pages_flushed | 673837 | | Innodb_buffer_pool_pages_free | 15779 | | Innodb_buffer_pool_pages_latched | 0 | | Innodb_buffer_pool_pages_misc | 601| | Innodb_buffer_pool_pages_total| 22400 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_ahead_seq | 0 | | Innodb_buffer_pool_read_requests | 48471963 | | Innodb_buffer_pool_reads | 3497 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 21700478 | Why would I need to increase? Thanks BR AJ On Thu, Sep 2, 2010 at 4:47 PM, Jangita wrote: > On 02/09/2010 4:46 p, Alexandre Vieira wrote: > >> John, Johnny, >> >> Thanks for the prompt answer. >> >> ... > > We also run some other applications in the server, but nothing that >> consumes >> all the CPU/Memory. The machine has almost 1GB of free memory and 50% of >> idle CPU time at any time. >> >> TIA >> >> BR >> Alex >> > > Increase innodb_buffer_pool_size say to 1GB? > -- > Jangita | +256 76 91 8383 | Y! & MSN: jang...@yahoo.com > Skype: jangita | GTalk: jangita.nyag...@gmail.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=nul...@gmail.com > > -- Alexandre Vieira - nul...@gmail.com
Re: Performance problems on MySQL
On 02/09/2010 4:46 p, Alexandre Vieira wrote: John, Johnny, Thanks for the prompt answer. ... We also run some other applications in the server, but nothing that consumes all the CPU/Memory. The machine has almost 1GB of free memory and 50% of idle CPU time at any time. TIA BR Alex Increase innodb_buffer_pool_size say to 1GB? -- Jangita | +256 76 91 8383 | Y! & MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance problems on MySQL
What is the hardware spec? Anything else running on the box? Why are you replicating but not making use of the slave? Can you post the output of SHOW CREATE TABLE? Regards John On 2 September 2010 12:50, Alexandre Vieira wrote: > Hi list, > > I'm having some performance problems on my 5.0.45-log DB running on Solaris > 8 (V240). > > We only have one table and two apps selecting, updating, inserting and > deleting massively and randomly from this table. > > The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only > one condition on an unique varchar indexed column. > > The table has 500k records and has been OPTIMIZED 32h ago. > > I've ran some sampling and: > > A SELECT costs between 400ms and 600ms. > An UPDATE costs between 800ms and 1300ms. > A DELETE costs between 900ms and 1300ms > An INSERT costs always 900ms 2000ms. > > At any given time the DB is handling 60-80 operations every second. It does > not scale any more than this because all the application connections to the > DB are being used and waiting for the DB to move. Our application queues > requests and it lags our clients. > > The perl mysqltuner only whines about "Query cache disabled" but since I > get > an ~20 updates every second I can't get any query cache hits, so I disabled > it. > > If it makes any difference, we're replicating everything to another server > that don't serve any queries. > > The DB has a 32 hour uptime. > > Any help is most welcome. > > You can find my.cnf, show status and show innodb status below. > > Kind regards > Alex > > ### > my.cnf: > > sql-mode="STRICT_ALL_TABLES" > old_passwords =1 > skip-bdb > max_connections =100 > max_allowed_packet =1M > table_cache =512 > sort_buffer_size=2M > read_buffer_size=4M > read_rnd_buffer_size=8M > thread_cache_size =16 > query_cache_limit =32M > thread_concurrency =8 > max_heap_table_size =28M > tmp_table_size =12M > innodb_buffer_pool_size =350M > innodb_additional_mem_pool_size =15M > innodb_log_buffer_size =6M > innodb_flush_log_at_trx_commit =1 > innodb_lock_wait_timeout=50 > > ### > > mysql> show status where Value NOT LIKE 0; > +---++ > | Variable_name | Value | > +---++ > | Aborted_clients | 88 | > | Aborted_connects | 37590 | > | Binlog_cache_use | 2148392| > | Bytes_received| 1117 | > | Bytes_sent| 8772 | > | Com_change_db | 1 | > | Com_delete| 4 | > | Com_insert| 3 | > | Com_select| 2 | > | Com_show_databases| 1 | > | Com_show_fields | 3 | > | Com_show_status | 2 | > | Com_show_tables | 1 | > | Compression | OFF| > | Connections | 276096 | > | Created_tmp_files | 5 | > | Created_tmp_tables| 4 | > | Flush_commands| 1 | > | Handler_commit| 14 | > | Handler_prepare | 14 | > | Handler_read_key | 8 | > | Handler_read_rnd_next | 263| > | Handler_write | 395| > | Innodb_buffer_pool_pages_data | 6019 | > | Innodb_buffer_pool_pages_dirty| 1858 | > | Innodb_buffer_pool_pages_flushed | 593993 | > | Innodb_buffer_pool_pages_free | 15784 | > | Innodb_buffer_pool_pages_misc | 597| > | Innodb_buffer_pool_pages_total| 22400 | > | Innodb_buffer_pool_read_ahead_rnd | 1 | > | Innodb_buffer_pool_read_requests | 42797013 | > | Innodb_buffer_pool_reads | 3497 | > | Innodb_buffer_pool_write_requests | 19096507 | > | Innodb_data_fsyncs| 4319683| > | Innodb_data_pending_fsyncs| 1 | > | Innodb_data_read | 60231680 | > | Innodb_data_reads | 3514 | > | Innodb_data_writes| 4496721| > | Innodb_data_written | 1259458560 | > | Innodb_dblwr_pages_written| 593993 | > | Innodb_dblwr_writes | 12967 | > | Innodb_log_write_requests | 2111208| > | Innodb_log_writes | 4285654| > | Innodb_os_log_fsyncs | 4303114| > | Innodb_os_log_pending_fsyncs | 1 | > | Innodb_os_log_written | 326489
Re: Performance problems on MySQL
Can you show us the table structure and sample queries? On Thursday, September 2, 2010, Alexandre Vieira wrote: > Hi list, > > I'm having some performance problems on my 5.0.45-log DB running on Solaris > 8 (V240). > > We only have one table and two apps selecting, updating, inserting and > deleting massively and randomly from this table. > > The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only > one condition on an unique varchar indexed column. > > The table has 500k records and has been OPTIMIZED 32h ago. > > I've ran some sampling and: > > A SELECT costs between 400ms and 600ms. > An UPDATE costs between 800ms and 1300ms. > A DELETE costs between 900ms and 1300ms > An INSERT costs always 900ms 2000ms. > > At any given time the DB is handling 60-80 operations every second. It does > not scale any more than this because all the application connections to the > DB are being used and waiting for the DB to move. Our application queues > requests and it lags our clients. > > The perl mysqltuner only whines about "Query cache disabled" but since I get > an ~20 updates every second I can't get any query cache hits, so I disabled > it. > > If it makes any difference, we're replicating everything to another server > that don't serve any queries. > > The DB has a 32 hour uptime. > > Any help is most welcome. > > You can find my.cnf, show status and show innodb status below. > > Kind regards > Alex > > ### > my.cnf: > > sql-mode ="STRICT_ALL_TABLES" > old_passwords =1 > skip-bdb > max_connections =100 > max_allowed_packet =1M > table_cache =512 > sort_buffer_size =2M > read_buffer_size =4M > read_rnd_buffer_size =8M > thread_cache_size =16 > query_cache_limit =32M > thread_concurrency =8 > max_heap_table_size =28M > tmp_table_size =12M > innodb_buffer_pool_size =350M > innodb_additional_mem_pool_size =15M > innodb_log_buffer_size =6M > innodb_flush_log_at_trx_commit =1 > innodb_lock_wait_timeout =50 > > ### > > mysql> show status where Value NOT LIKE 0; > +---++ > | Variable_name | Value | > +---++ > | Aborted_clients | 88 | > | Aborted_connects | 37590 | > | Binlog_cache_use | 2148392 | > | Bytes_received | 1117 | > | Bytes_sent | 8772 | > | Com_change_db | 1 | > | Com_delete | 4 | > | Com_insert | 3 | > | Com_select | 2 | > | Com_show_databases | 1 | > | Com_show_fields | 3 | > | Com_show_status | 2 | > | Com_show_tables | 1 | > | Compression | OFF | > | Connections | 276096 | > | Created_tmp_files | 5 | > | Created_tmp_tables | 4 | > | Flush_commands | 1 | > | Handler_commit | 14 | > | Handler_prepare | 14 | > | Handler_read_key | 8 | > | Handler_read_rnd_next | 263 | > | Handler_write | 395 | > | Innodb_buffer_pool_pages_data | 6019 | > | Innodb_buffer_pool_pages_dirty | 1858 | > | Innodb_buffer_pool_pages_flushed | 593993 | > | Innodb_buffer_pool_pages_free | 15784 | > | Innodb_buffer_pool_pages_misc | 597 | > | Innodb_buffer_pool_pages_total | 22400 | > | Innodb_buffer_pool_read_ahead_rnd | 1 | > | Innodb_buffer_pool_read_requests | 42797013 | > | Innodb_buffer_pool_reads | 3497 | > | Innodb_buffer_pool_write_requests | 19096507 | > | Innodb_data_fsyncs | 4319683 | > | Innodb_data_pending_fsyncs | 1 | > | Innodb_data_read | 60231680 | > | Innodb_data_reads | 3514 | > | Innodb_data_writes | 4496721 | > | Innodb_data_written | 1259458560 | > | Innodb_dblwr_pages_written | 593993 | > | Innodb_dblwr_writes | 12967 | > | Innodb_log_write_requests | 2111208 | > | Innodb_log_writes | 4285654 | > | Innodb_os_log_fsyncs | 4303114 | > | Innodb_os_log_pending_fsyncs | 1 | > | Innodb_os_log_written | 3264897024 | > | Innodb_page_size | 16384 | > | Innodb_pages_created | 2476 | > | Innod
