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 | 10000 | 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/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device 0.0 83.7 0.0 379.3 0.0 1.0 0.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 <nul...@gmail.com> 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 <mo...@fastmail.fm> 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 <joh...@pixelated.net> >>> 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 <nul...@gmail.com> >>> 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 >>> >> >> >> -- >> 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 > > -- Alexandre Vieira - nul...@gmail.com