RE: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
SHOW GLOBAL STATUS LIKE 'Innodb%'; Then do some math -- usually dividing by Uptime. That will give you some insight in how hard the I/O is working, and how full the buffer_pool is. > -Original Message- > From: Rafał Radecki [mailto:radecki.ra...@gmail.com] > Sent: Friday, June 21, 2013 4:59 AM > To: mysql@lists.mysql.com > Subject: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs? > > Hi All. > > I've searched but with no luck... what do exactly these variables mean: > > 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs > > ? > I am wondering if my innodb_buffer_pool setting is not to low. Does 'file > reads' show number of times innodb files have been read into memory from > server's start? What about file writes/fsyncs? > > Best regards, > Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
Hello Rafał, On 6/24/2013 4:26 AM, Rafał Radecki wrote: As I can see the changes in these values are use by percona cacti monitoring templates to graph "InnoDB I/O". Can anyone answer the question finally? ;) 2013/6/21 Hartmut Holzgraefe On 21.06.2013 13:59, Rafał Radecki wrote: Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? these are the total number of reads/writes/fsyncs (number of system calls actually?) since the server started (or maybe last FLUSH call?) and not very meaningful by themselves without knowing the time span it took to come up to those counter values. The per second values on the following line are much more interesting. I don't understand how Hartmut's answer was insufficient. The InnoDB engine must get data from the disk (reads), send data to the disk (writes), and occasionally tell the operating system that it must flush its buffers to disk to ensure durability (fsync). Why are you so interested in these numbers? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
As I can see the changes in these values are use by percona cacti monitoring templates to graph "InnoDB I/O". Can anyone answer the question finally? ;) 2013/6/21 Hartmut Holzgraefe > On 21.06.2013 13:59, Rafał Radecki wrote: > > Hi All. > > > > I've searched but with no luck... what do exactly these variables mean: > > > > 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs > > > > ? > > these are the total number of reads/writes/fsyncs (number of system > calls actually?) since the server started (or maybe last FLUSH call?) > and not very meaningful by themselves without knowing the time span > it took to come up to those counter values. > > The per second values on the following line are much more interesting. > > > http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/ > > has a pretty good description of the SHOW ENGINE INNODB STATUS output, > even though it is not too detailed in this specific section. > > > -- > Hartmut Holzgraefe > Principal Support Engineer (EMEA) > SkySQL AB - http://www.skysql.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > >
SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? I am wondering if my innodb_buffer_pool setting is not to low. Does 'file reads' show number of times innodb files have been read into memory from server's start? What about file writes/fsyncs? Best regards, Rafal Radecki.
Re: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
On 21.06.2013 13:59, Rafał Radecki wrote: > Hi All. > > I've searched but with no luck... what do exactly these variables mean: > > 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs > > ? these are the total number of reads/writes/fsyncs (number of system calls actually?) since the server started (or maybe last FLUSH call?) and not very meaningful by themselves without knowing the time span it took to come up to those counter values. The per second values on the following line are much more interesting. http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/ has a pretty good description of the SHOW ENGINE INNODB STATUS output, even though it is not too detailed in this specific section. -- Hartmut Holzgraefe Principal Support Engineer (EMEA) SkySQL AB - http://www.skysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How NOT to log SHOW INNODB STATUS in the query log.
On 1/27/06, Imran Chaudhry <[EMAIL PROTECTED]> wrote: > > Hi; > > My query.log is full of 'show innodb status' queries. > > How do I get this ascii log file not to log these. OR some help with a > > grep script to copy the file without these lines. > > I noticed the same in the logs of a 4.1 test server. I put it down to > MySQL Administrator which was monitoring the server at the time. I > believe MYSQL Admin issues these SHOW STATUS commands periodically to > refresh it's status info screen. > > Out of curiosity were/are you using MySQL Administrator or something > like a monitoring app with your MySQL servers? Yep! It's a test environment, and MySQL-Administrator is often open for long periods in the background. Thanks; -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How NOT to log SHOW INNODB STATUS in the query log.
> Hi; > My query.log is full of 'show innodb status' queries. > How do I get this ascii log file not to log these. OR some help with a > grep script to copy the file without these lines. I noticed the same in the logs of a 4.1 test server. I put it down to MySQL Administrator which was monitoring the server at the time. I believe MYSQL Admin issues these SHOW STATUS commands periodically to refresh it's status info screen. Out of curiosity were/are you using MySQL Administrator or something like a monitoring app with your MySQL servers? Im -- http://www.ImranChaudhry.info MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How NOT to log SHOW INNODB STATUS in the query log.
Aye. -v. thanks! -nat On 1/26/06, Pooly <[EMAIL PROTECTED]> wrote: > 2006/1/25, Nathan Gross <[EMAIL PROTECTED]>: > > Hi; > > My query.log is full of 'show innodb status' queries. > > How do I get this ascii log file not to log these. OR some help with a > > grep script to copy the file without these lines. > > > If you have a linux box (or any acceptable shell) > cat query.log | grep -i -v 'show innodb status' > query_clean.log > > grep -i : case insensitive > grep -v : everything but the patterm given > cat : well a cat is a cat... > > > Thanks > > -nat > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How NOT to log SHOW INNODB STATUS in the query log.
2006/1/25, Nathan Gross <[EMAIL PROTECTED]>: > Hi; > My query.log is full of 'show innodb status' queries. > How do I get this ascii log file not to log these. OR some help with a > grep script to copy the file without these lines. If you have a linux box (or any acceptable shell) cat query.log | grep -i -v 'show innodb status' > query_clean.log grep -i : case insensitive grep -v : everything but the patterm given cat : well a cat is a cat... > Thanks > -nat > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How NOT to log SHOW INNODB STATUS in the query log.
Hi; My query.log is full of 'show innodb status' queries. How do I get this ascii log file not to log these. OR some help with a grep script to copy the file without these lines. Thanks -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
"show innodb status" doesn't work
I use Mysql 4.0.22. When I press "show innodb status",I got the error message: ERROR 1105: Unknow error what's the problem? Is the innodb enable in 4.0.22 by default? How can I check if the innodb enable or not? _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show InnoDB Status
Greeting, Is there any comprehensive documentation on "show Innodb status" command? I found the mysql help page a bit short and the whole exercise has left me with many questions unanswered hence any tips from pratical experience that would help in Innodb performance tuning would be greatly appreciated! Cheers Manoj
show innodb status
Does anyone how to interpret the output of 'SHOW INNODB STATUS' ? It prints quite a bit of stuff but I haven't been able to find any documentation explaining what everything means. Specifically: Total memory allocated 462835256; in additional pool allocated 1385472 Buffer pool size 24576 Free buffers 24512 Database pages 64 How does it get these numbers? I've set innodb_buffer_pool_size to 384M and 20M for additional_mem_pool_size. thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: "Show Innodb Status": Output truncated
Frank, CREATE TABLE innodb_monitor(a INT) TYPE=InnoDB; causes the output to be written to the .err file untruncated. DROP the table to stop the printouts. There is a limit in the mysql client that restricts the output length to 64 kB. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: ""Dr. Frank Ullrich"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, December 27, 2004 1:21 PM Subject: "Show Innodb Status": Output truncated Hi, this is on 4.0.22-standard. The output of "show innodb status" is truncated so that the sections FILE I/O, INSERT BUFFER AND ADAPTIVE HASH INDEX, LOG, BUFFER POOL AND MEMORY and ROW OPERATIONS are missing. We have more than 600 concurrent connections so that the TRANSCATIONS section becomes long. What causes that behaviour and can you work around the problem? Regards, Frank. -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
"Show Innodb Status": Output truncated
Hi, this is on 4.0.22-standard. The output of "show innodb status" is truncated so that the sections FILE I/O, INSERT BUFFER AND ADAPTIVE HASH INDEX, LOG, BUFFER POOL AND MEMORY and ROW OPERATIONS are missing. We have more than 600 concurrent connections so that the TRANSCATIONS section becomes long. What causes that behaviour and can you work around the problem? Regards, Frank. -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help interpreting SHOW INNODB Status Message
Emmett, if you have an index on a column, say c CHAR(10) and the index contains adjacent records 'aabbaguu' and 'abbaguu', then a 'gap' is the gap between those values in the alphabetical order. If you try to insert, say, 'aac' to the index, it would fall in the 'gap'. Note that the 'next-key' locking information in InnoDB is carried by existing records in the index. You cannot lock just some subinterval of a 'gap'. Regards, Heikki - Alkuperäinen viesti - Lähettäjä: "Emmett Bishop" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Lähetetty: Wednesday, December 08, 2004 12:16 AM Aihe: Re: Help interpreting SHOW INNODB Status Message Heikki, Thanks for the input. Right now we can't move to 4.1, we're stuck with 4.0.20 for sometime. So, it there someway that through improving the schema these issues could be addressed? I added an index on these tables reflecting the way that data is usually looked up (always by session_guid and label). Every user has a unique session_guid that looks something like: 7E6E4C38-4819-11D9-9802-84B0A828F8C4 What I don't understand is what the gap is. I can understand in the example on the mysql site (select * from table where id > 100 for update) how that would lock all records with id > 100. How does it work in the case of a string based index? Suppose there were two different users, one with guid 7E6E4C38-4819-11D9-9802-84B0A828F8C4 and the other with guid 5G4F4H10-4819-11D9-9802-84B0A828F8C4 (note, these two guids are fairly representative in form). The first user was performing a delete and the second was performing an insert what gap would be created? What kind of schema change might reduce/eliminate the contention? Thanks, Tripp --- Heikki Tuuri <[EMAIL PROTECTED]> wrote: Emmett, ok, trx (1) is trying to lock gap G, but trx (2) already has a lock on it. And trx (2) is trying to insert a new record into that same gap G. Even a waiting lock request on a gap prevents new inserts into it => a deadlock. In 4.1.7, you might avoid this deadlock. In 4.1.7, a waiting gap lock does not need to wait just because someone else has a granted lock on the same gap. But, of course, your transactions might deadlock a bit later. DELETEs and INSERTs in the same gap very easily lead to a deadlock. In 4.1, you can also set innodb_locks_unsafe_for_binlog in my.cnf. That removes almost all gap locking. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: "Emmett Bishop" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, December 07, 2004 5:44 PM Subject: Re: Help interpreting SHOW INNODB Status Message > Heikki, > > We're using MySQL 4.0.20. Here's the full output: > > = > 041207 8:33:26 INNODB MONITOR OUTPUT > = > Per second averages calculated from the last 15 > seconds > -- > SEMAPHORES > -- > OS WAIT ARRAY INFO: reservation count 49506785, signal > count 48823098 > Mutex spin waits 963371106, rounds 3338424450, OS > waits 2853796 > RW-shared spins 80886102, OS waits 38728129; RW-excl > spins 2488320, OS waits 302033 > > LATEST FOREIGN KEY ERROR > > 041206 23:52:41 Transaction: > TRANSACTION 0 10799828, ACTIVE 2 sec, process no > 31424, OS thread id 2942950320 inserting, thread > declared inside InnoDB 500 > mysql tables in use 1, locked 1 > 6 lock struct(s), heap size 1024, undo log entries 1 > MySQL thread id 88051, query id 14858689 host ip user > update > INSERT INTO s_filter (fname, lname, email, > eff_oldest_dt, eff_latest_dt, move_oldest_dt, > move_latest_dt, agent_id, no_agent_sw, > exclude_holds_sw, custom_message_sw, pics_only_sw, > price, wiz_city_name, subdivision, tot_finished_sqft, > bedrooms, garage_spaces, bathrooms, area, subarea, > year, property_t > Foreign key constraint fails for table > `ahf_test/s_filter`: > , > CONSTRAINT `s_filter_ibfk_1` FOREIGN KEY > (`session_guid`) REFERENCES `s_session` > (`session_guid`) > Trying to add in child table, in index > `idx_session_guid` tuple: > DATA TUPLE: 2 fields; > 0: len 36; hex > 3745364534482d343831392d313144392d393830322d383442304138323846384334; > asc 7E6E4C38-4819-11D9-9802-84B0A828F8C4;; 1: len 6; > hex 0048a79b; ascH ;; > > But in parent table `ahf_test/s_session`, in index > `PRIMARY`, > the closest match we can find is record: > PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE
Re: Help interpreting SHOW INNODB Status Message
Heikki, Thanks for the input. Right now we can't move to 4.1, we're stuck with 4.0.20 for sometime. So, it there someway that through improving the schema these issues could be addressed? I added an index on these tables reflecting the way that data is usually looked up (always by session_guid and label). Every user has a unique session_guid that looks something like: 7E6E4C38-4819-11D9-9802-84B0A828F8C4 What I don't understand is what the gap is. I can understand in the example on the mysql site (select * from table where id > 100 for update) how that would lock all records with id > 100. How does it work in the case of a string based index? Suppose there were two different users, one with guid 7E6E4C38-4819-11D9-9802-84B0A828F8C4 and the other with guid 5G4F4H10-4819-11D9-9802-84B0A828F8C4 (note, these two guids are fairly representative in form). The first user was performing a delete and the second was performing an insert what gap would be created? What kind of schema change might reduce/eliminate the contention? Thanks, Tripp --- Heikki Tuuri <[EMAIL PROTECTED]> wrote: > Emmett, > > ok, trx (1) is trying to lock gap G, but trx (2) > already has a lock on it. > And trx (2) is trying to insert a new record into > that same gap G. > > Even a waiting lock request on a gap prevents new > inserts into it => a > deadlock. > > In 4.1.7, you might avoid this deadlock. In 4.1.7, a > waiting gap lock does > not need to wait just because someone else has a > granted lock on the same > gap. But, of course, your transactions might > deadlock a bit later. DELETEs > and INSERTs in the same gap very easily lead to a > deadlock. > > In 4.1, you can also set > > innodb_locks_unsafe_for_binlog > > in my.cnf. That removes almost all gap locking. > > Best regards, > > Heikki Tuuri > Innobase Oy > Foreign keys, transactions, and row level locking > for MySQL > InnoDB Hot Backup - a hot backup tool for InnoDB > which also backs up MyISAM > tables > http://www.innodb.com/order.php > > > - Original Message - > From: "Emmett Bishop" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.myodbc > Sent: Tuesday, December 07, 2004 5:44 PM > Subject: Re: Help interpreting SHOW INNODB Status > Message > > > > Heikki, > > > > We're using MySQL 4.0.20. Here's the full output: > > > > = > > 041207 8:33:26 INNODB MONITOR OUTPUT > > = > > Per second averages calculated from the last 15 > > seconds > > -- > > SEMAPHORES > > -- > > OS WAIT ARRAY INFO: reservation count 49506785, > signal > > count 48823098 > > Mutex spin waits 963371106, rounds 3338424450, OS > > waits 2853796 > > RW-shared spins 80886102, OS waits 38728129; > RW-excl > > spins 2488320, OS waits 302033 > > > > LATEST FOREIGN KEY ERROR > > > > 041206 23:52:41 Transaction: > > TRANSACTION 0 10799828, ACTIVE 2 sec, process no > > 31424, OS thread id 2942950320 inserting, thread > > declared inside InnoDB 500 > > mysql tables in use 1, locked 1 > > 6 lock struct(s), heap size 1024, undo log entries > 1 > > MySQL thread id 88051, query id 14858689 host ip > user > > update > > INSERT INTO s_filter (fname, lname, email, > > eff_oldest_dt, eff_latest_dt, move_oldest_dt, > > move_latest_dt, agent_id, no_agent_sw, > > exclude_holds_sw, custom_message_sw, pics_only_sw, > > price, wiz_city_name, subdivision, > tot_finished_sqft, > > bedrooms, garage_spaces, bathrooms, area, subarea, > > year, property_t > > Foreign key constraint fails for table > > `ahf_test/s_filter`: > > , > > CONSTRAINT `s_filter_ibfk_1` FOREIGN KEY > > (`session_guid`) REFERENCES `s_session` > > (`session_guid`) > > Trying to add in child table, in index > > `idx_session_guid` tuple: > > DATA TUPLE: 2 fields; > > 0: len 36; hex > > > 3745364534482d343831392d313144392d393830322d383442304138323846384334; > > asc 7E6E4C38-4819-11D9-9802-84B0A828F8C4;; 1: len > 6; > > hex 0048a79b; ascH ;; > > > > But in parent table `ahf_test/s_session`, in index > > `PRIMARY`, > > the closest match we can find is record: > > PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; > info > > bits 32 > > 0: len 30; hex > > > 37454139364146452d343743442d313144392d393830322d383442304138; > > asc 7EA96AFE-47CD-11D9-9802-84B0A8;...(truncated); > 1: > > len 6; hex 00a49aaf; asc ;
Re: Help interpreting SHOW INNODB Status Message
Emmett, ok, trx (1) is trying to lock gap G, but trx (2) already has a lock on it. And trx (2) is trying to insert a new record into that same gap G. Even a waiting lock request on a gap prevents new inserts into it => a deadlock. In 4.1.7, you might avoid this deadlock. In 4.1.7, a waiting gap lock does not need to wait just because someone else has a granted lock on the same gap. But, of course, your transactions might deadlock a bit later. DELETEs and INSERTs in the same gap very easily lead to a deadlock. In 4.1, you can also set innodb_locks_unsafe_for_binlog in my.cnf. That removes almost all gap locking. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: "Emmett Bishop" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, December 07, 2004 5:44 PM Subject: Re: Help interpreting SHOW INNODB Status Message Heikki, We're using MySQL 4.0.20. Here's the full output: = 041207 8:33:26 INNODB MONITOR OUTPUT = Per second averages calculated from the last 15 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 49506785, signal count 48823098 Mutex spin waits 963371106, rounds 3338424450, OS waits 2853796 RW-shared spins 80886102, OS waits 38728129; RW-excl spins 2488320, OS waits 302033 LATEST FOREIGN KEY ERROR 041206 23:52:41 Transaction: TRANSACTION 0 10799828, ACTIVE 2 sec, process no 31424, OS thread id 2942950320 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 6 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 88051, query id 14858689 host ip user update INSERT INTO s_filter (fname, lname, email, eff_oldest_dt, eff_latest_dt, move_oldest_dt, move_latest_dt, agent_id, no_agent_sw, exclude_holds_sw, custom_message_sw, pics_only_sw, price, wiz_city_name, subdivision, tot_finished_sqft, bedrooms, garage_spaces, bathrooms, area, subarea, year, property_t Foreign key constraint fails for table `ahf_test/s_filter`: , CONSTRAINT `s_filter_ibfk_1` FOREIGN KEY (`session_guid`) REFERENCES `s_session` (`session_guid`) Trying to add in child table, in index `idx_session_guid` tuple: DATA TUPLE: 2 fields; 0: len 36; hex 3745364534482d343831392d313144392d393830322d383442304138323846384334; asc 7E6E4C38-4819-11D9-9802-84B0A828F8C4;; 1: len 6; hex 0048a79b; ascH ;; But in parent table `ahf_test/s_session`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32 0: len 30; hex 37454139364146452d343743442d313144392d393830322d383442304138; asc 7EA96AFE-47CD-11D9-9802-84B0A8;...(truncated); 1: len 6; hex 00a49aaf; asc ;; 2: len 7; hex 0340082073; asc@ s;; 3: len 8; hex 8000123a34f916a6; asc:4 ;; LATEST DETECTED DEADLOCK 041207 7:32:14 *** (1) TRANSACTION: TRANSACTION 0 10812524, ACTIVE 0 sec, process no 31424, OS thread id 2916936624 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 89288, query id 15070554 host ip user updating DELETE FROM s_customer WHERE session_guid = '0B10634A-485A-11D9-9802-84B0A828F8C4' AND label = 'CustomerDTO' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6682 n bits 120 index `idx_session_label` of table `ahf_test/s_customer` trx id 0 10812524 lock_mode X locks gap before rec waiting Record lock, heap no 13 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 32354435463135432d343835382d313144392d393830322d383442304138; asc 25D5F15C-4858-11D9-9802-84B0A8;...(truncated); 1: len 11; hex 437573746f6d657244544f; asc CustomerDTO;; 2: len 6; hex 0048b228; ascH (;; *** (2) TRANSACTION: TRANSACTION 0 10812523, ACTIVE 0 sec, process no 31424, OS thread id 2926173104 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 7 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 89289, query id 15070556 host ip user update INSERT INTO s_customer (customer_id, address_id, agent_id, pref_id, lname, fname, email, home_phone, work_phone, comments, house_for_sale, address1, city, state, zip, subdivision_list, price_lower, price_upper, tot_finished_sqft, bedrooms, bathrooms, garage_spaces, style, eff_dt, move_dt, customer_i *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 6682 n bits 120 index `idx_session_label` of table `ahf_test/s_customer` trx id 0 10812523 lock_mode X locks gap before rec Record lock, heap no 13 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 32354435463135432d343835382d313144392d39
Re: Help interpreting SHOW INNODB Status Message
Heikki, We're using MySQL 4.0.20. Here's the full output: = 041207 8:33:26 INNODB MONITOR OUTPUT = Per second averages calculated from the last 15 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 49506785, signal count 48823098 Mutex spin waits 963371106, rounds 3338424450, OS waits 2853796 RW-shared spins 80886102, OS waits 38728129; RW-excl spins 2488320, OS waits 302033 LATEST FOREIGN KEY ERROR 041206 23:52:41 Transaction: TRANSACTION 0 10799828, ACTIVE 2 sec, process no 31424, OS thread id 2942950320 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 6 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 88051, query id 14858689 host ip user update INSERT INTO s_filter (fname, lname, email, eff_oldest_dt, eff_latest_dt, move_oldest_dt, move_latest_dt, agent_id, no_agent_sw, exclude_holds_sw, custom_message_sw, pics_only_sw, price, wiz_city_name, subdivision, tot_finished_sqft, bedrooms, garage_spaces, bathrooms, area, subarea, year, property_t Foreign key constraint fails for table `ahf_test/s_filter`: , CONSTRAINT `s_filter_ibfk_1` FOREIGN KEY (`session_guid`) REFERENCES `s_session` (`session_guid`) Trying to add in child table, in index `idx_session_guid` tuple: DATA TUPLE: 2 fields; 0: len 36; hex 3745364534482d343831392d313144392d393830322d383442304138323846384334; asc 7E6E4C38-4819-11D9-9802-84B0A828F8C4;; 1: len 6; hex 0048a79b; ascH ;; But in parent table `ahf_test/s_session`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 32 0: len 30; hex 37454139364146452d343743442d313144392d393830322d383442304138; asc 7EA96AFE-47CD-11D9-9802-84B0A8;...(truncated); 1: len 6; hex 00a49aaf; asc ;; 2: len 7; hex 0340082073; asc@ s;; 3: len 8; hex 8000123a34f916a6; asc:4 ;; LATEST DETECTED DEADLOCK 041207 7:32:14 *** (1) TRANSACTION: TRANSACTION 0 10812524, ACTIVE 0 sec, process no 31424, OS thread id 2916936624 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 89288, query id 15070554 host ip user updating DELETE FROM s_customer WHERE session_guid = '0B10634A-485A-11D9-9802-84B0A828F8C4' AND label = 'CustomerDTO' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6682 n bits 120 index `idx_session_label` of table `ahf_test/s_customer` trx id 0 10812524 lock_mode X locks gap before rec waiting Record lock, heap no 13 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 32354435463135432d343835382d313144392d393830322d383442304138; asc 25D5F15C-4858-11D9-9802-84B0A8;...(truncated); 1: len 11; hex 437573746f6d657244544f; asc CustomerDTO;; 2: len 6; hex 0048b228; ascH (;; *** (2) TRANSACTION: TRANSACTION 0 10812523, ACTIVE 0 sec, process no 31424, OS thread id 2926173104 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 7 lock struct(s), heap size 1024, undo log entries 1 MySQL thread id 89289, query id 15070556 host ip user update INSERT INTO s_customer (customer_id, address_id, agent_id, pref_id, lname, fname, email, home_phone, work_phone, comments, house_for_sale, address1, city, state, zip, subdivision_list, price_lower, price_upper, tot_finished_sqft, bedrooms, bathrooms, garage_spaces, style, eff_dt, move_dt, customer_i *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 6682 n bits 120 index `idx_session_label` of table `ahf_test/s_customer` trx id 0 10812523 lock_mode X locks gap before rec Record lock, heap no 13 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 32354435463135432d343835382d313144392d393830322d383442304138; asc 25D5F15C-4858-11D9-9802-84B0A8;...(truncated); 1: len 11; hex 437573746f6d657244544f; asc CustomerDTO;; 2: len 6; hex 0048b228; ascH (;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6682 n bits 120 index `idx_session_label` of table `ahf_test/s_customer` trx id 0 10812523 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 13 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 32354435463135432d343835382d313144392d393830322d383442304138; asc 25D5F15C-4858-11D9-9802-84B0A8;...(truncated); 1: len 11; hex 437573746f6d657244544f; asc CustomerDTO;; 2: len 6; hex 0048b228; ascH (;; *** WE ROLL BACK TRANSACTION (1) TRANSACTIONS Trx id counter 0 10819864 Purge done for trx's n:o < 0 10819842 undo n:o < 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 31424, OS thread id 2925570992 MySQL thread id 89423, q
Re: Help interpreting SHOW INNODB Status Message
Emmett, - Original Message - From: "Emmett Bishop" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, December 07, 2004 6:01 AM Subject: Help interpreting SHOW INNODB Status Message Howdy all, We're having concurrency problems with a table in our database and I'm not sure if I'm interpreting the following chunk of output from SHOW INNODB STATUS correctly. From what I gather, the row could not be inserted because the table was locked. I think that this insert was a victim of the next key locking stategy used by INNODB based on statement "lock_mode X locks gap before rec". We don't use SELECT FOR UPDATE statements so I'm wondering how there could be a lock. If someone was performing a regular SELECT statement (they would be using the index session_guid, label to retrieve the records) would that cause the insert statement to get locked out? What confuses me is how there could be two or more different guids involved in any one query (the session guid is always in the where clause of any select statement against this table, as is the label, though the labels are almost all the same, a value of "SelectedCustomer"). Any ideas as to how I can clean this up so that the contention is reduced? BTW, we're using a transaction isolation level of REPEATABLE READ. please post the COMPLETE output of SHOW INNODB STATUS. Do not cut anything off. What MySQL version you are using? Thanks in advance, Tripp LATEST DETECTED DEADLOCK 041206 17:13:50 *** (1) TRANSACTION: TRANSACTION 0 10790587, ACTIVE 151 sec, process no 31424, OS thread id 2949241776 inserting mysql tables in use 1, locked 1 LOCK WAIT 46 lock struct(s), heap size 5504, undo log entries 158 MySQL thread id 85684, query id 14714501 host ip user update INSERT INTO s_contact_log (log_id, customer_id, entry, author, log_dt, log_type, office_id, session_guid, label, sort_id) VALUES (228072,38755, 'test entry' - *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2263 n bits 264 index `idx_session_label` of table `ahf_test/s_contact_log` trx id 0 10790587 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 130 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 35384143373041392d343745312d313144392d393830322d383442304138; asc 58AC70A9-47E1-11D9-9802-84B0A8;...(truncated); 1: len 16; hex 53656c6563746564437573746f6d6572; asc SelectedCustomer;; 2: len 6; hex 004880b1; asc H ;; Here's the structure of the table: CREATE TABLE `s_contact_log` ( `session_guid` varchar(36) NOT NULL default '', `label` varchar(50) NOT NULL default '', `log_id` int(11) NOT NULL default '0', `office_id` int(11) NOT NULL default '0', `customer_id` int(11) NOT NULL default '0', `entry` text NOT NULL, `author` varchar(60) NOT NULL default '', `log_dt` date NOT NULL default '-00-00', `sort_id` int(11) NOT NULL default '0', `log_type` int(11) NOT NULL default '0', KEY `idx_session_guid` (`session_guid`), KEY `idx_session_label` (`session_guid`,`label`), CONSTRAINT `s_contact_log_ibfk_1` FOREIGN KEY (`session_guid`) REFERENCES `s_session` (`session_guid`) ) TYPE=InnoDB Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help interpreting SHOW INNODB Status Message
Howdy all, We're having concurrency problems with a table in our database and I'm not sure if I'm interpreting the following chunk of output from SHOW INNODB STATUS correctly. >From what I gather, the row could not be inserted because the table was locked. I think that this insert was a victim of the next key locking stategy used by INNODB based on statement "lock_mode X locks gap before rec". We don't use SELECT FOR UPDATE statements so I'm wondering how there could be a lock. If someone was performing a regular SELECT statement (they would be using the index session_guid, label to retrieve the records) would that cause the insert statement to get locked out? What confuses me is how there could be two or more different guids involved in any one query (the session guid is always in the where clause of any select statement against this table, as is the label, though the labels are almost all the same, a value of "SelectedCustomer"). Any ideas as to how I can clean this up so that the contention is reduced? BTW, we're using a transaction isolation level of REPEATABLE READ. Thanks in advance, Tripp LATEST DETECTED DEADLOCK 041206 17:13:50 *** (1) TRANSACTION: TRANSACTION 0 10790587, ACTIVE 151 sec, process no 31424, OS thread id 2949241776 inserting mysql tables in use 1, locked 1 LOCK WAIT 46 lock struct(s), heap size 5504, undo log entries 158 MySQL thread id 85684, query id 14714501 host ip user update INSERT INTO s_contact_log (log_id, customer_id, entry, author, log_dt, log_type, office_id, session_guid, label, sort_id) VALUES (228072,38755, 'test entry' - *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2263 n bits 264 index `idx_session_label` of table `ahf_test/s_contact_log` trx id 0 10790587 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 130 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32 0: len 30; hex 35384143373041392d343745312d313144392d393830322d383442304138; asc 58AC70A9-47E1-11D9-9802-84B0A8;...(truncated); 1: len 16; hex 53656c6563746564437573746f6d6572; asc SelectedCustomer;; 2: len 6; hex 004880b1; asc H ;; Here's the structure of the table: CREATE TABLE `s_contact_log` ( `session_guid` varchar(36) NOT NULL default '', `label` varchar(50) NOT NULL default '', `log_id` int(11) NOT NULL default '0', `office_id` int(11) NOT NULL default '0', `customer_id` int(11) NOT NULL default '0', `entry` text NOT NULL, `author` varchar(60) NOT NULL default '', `log_dt` date NOT NULL default '-00-00', `sort_id` int(11) NOT NULL default '0', `log_type` int(11) NOT NULL default '0', KEY `idx_session_guid` (`session_guid`), KEY `idx_session_label` (`session_guid`,`label`), CONSTRAINT `s_contact_log_ibfk_1` FOREIGN KEY (`session_guid`) REFERENCES `s_session` (`session_guid`) ) TYPE=InnoDB __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW INNODB STATUS
On Mon, 26 Jul 2004 18:03:25 -0700, Matt Solnit <[EMAIL PROTECTED]> wrote: > How is it possible to have a hit rate of 1000/1000? Doesn't the buffer > get inOn Mon, 26 Jul 2004 18:03:25 -0700, Matt Solnit <[EMAIL PROTECTED]> wrote: > > How is it possible to have a hit rate of 1000/1000? Doesn't the buffer > get initialized by cache misses? That is a number after rounding so it may not be exactly 100%, and ISTR it is one of the states that is either reset every time you read or every so many seconds so any misses before then won't be included. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW INNODB STATUS
Mark, How is it possible to have a hit rate of 1000/1000? Doesn't the buffer get initialized by cache misses? -- Matt <[EMAIL PROTECTED]> Re: INNODB SHOW STATUS From: Marc Slemko (marcsznep.com) Date: Wed Apr 21 2004 - 10:29:44 CDT On Tue, 20 Apr 2004, Emmett Bishop wrote: > Howdy all, > > Quick question about what I'm seeing in the BUFFER > POOL AND MEMORY section... > > I've configured the innodb_buffer_pool_size to be 128M > and when I do a show variables like 'innodb%' I see > > | innodb_buffer_pool_size | 134217728 | > > So that looks good. However, I see the following in > the BUFFER POOL AND MEMORY section of the output from > the innodb monitor: > > -- > BUFFER POOL AND MEMORY > -- > Total memory allocated 152389988; in additional pool > allocated 1048576 > Buffer pool size 8192 > Free buffers 0 > Database pages 7947 > Modified db pages 0 > Pending reads 0 > Pending writes: LRU 0, flush list 0, single page 0 > Pages read 20345325, created 9857, written 763089 > 0.00 reads/s, 0.00 creates/s, 0.00 writes/s > Buffer pool hit rate 1000 / 1000 > > Why does it say the buffer pool size is only 8M? > Shouldn't it be 128M? Also, could someone explain the > hit rate? I remember seeing in someone's recent post > that the 1000/1000 is good, but I don't know what that > means. Can someone suggest a good resouce that > explains the contents of Innodb show status in detail. > The page on www.mysql.com gives a very cursory > overview of the output. Buffer pool size, free buffers, database pages, and modified database pages are in 16k pages. The buffer pool hit rate simply says the fraction of page reads satisfied from the innodb buffer cache, in this case 1000/1000 == 100%. Unfortunately, I'm not really aware of a better reference. Perhaps some of this is explained in "High Performance MySQL", but I don't have a copy yet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW INNODB STATUS output as SHOW STATUS?
On Fri, Jan 03, 2003 at 12:04:47AM +0200, Heikki Tuuri wrote: > > I think Sinisa suggested some of the InnoDB statistics could be > included in SHOW STATUS. Of course not all, as SHOW INNODB STATUS > often prints several kilobytes of data. Right... > But, what is the problem in scanning the text string returned by > SHOW INNODB STATUS, and picking the information you want? I can > promise that the output format of interesting stats will stay rather > constant. The "problem" is then everyone has to do it. And I can guarantee that it'll be done at least 10 times in each language (C, C++, Perl, PHP, Phython, etc...) It's just so easy to treat the results like a normal result set when you're using SHOW STATUS. But you already knew that. :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 18 days, processed 648,525,199 queries (397/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SHOW INNODB STATUS output as SHOW STATUS?
Jean-Luc, - Original Message - From: "Jean-Luc Fontaine" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, January 02, 2003 2:14 PM Subject: SHOW INNODB STATUS output as SHOW STATUS? > Hello, > > I was wondering whether it would be possible, in a future release, to > have SHOW INNODB STATUS give a real table output with 1 row per > variable, as in the MySQL SHOW STATUS query? > > When that is done, I could quickly write and release a myinnodbstatus > module for the moodss (http://jfontain.free.fr/mysql/) MySQL > monitoring software. > > Is anybody interested in this feature? I think Sinisa suggested some of the InnoDB statistics could be included in SHOW STATUS. Of course not all, as SHOW INNODB STATUS often prints several kilobytes of data. But, what is the problem in scanning the text string returned by SHOW INNODB STATUS, and picking the information you want? I can promise that the output format of interesting stats will stay rather constant. > Many thanks and Happy New Year to all! The same to you! > Jean-Luc Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
