From: Maitrayi Sabaratnam Date: November 3 2010 9:16am Subject: bzr push into mysql-5.1-telco-7.1 branch (maitrayi.sabaratnam:3940 to 3941) List-Archive: http://lists.mysql.com/commits/122618 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3941 Maitrayi Sabaratnam 2010-11-03 WORKLOG5567 ndbinfo: disk page buffer modified: mysql-test/suite/ndb/r/ndbinfo.result scripts/mysql_system_tables.sql storage/ndb/src/kernel/blocks/dbinfo/Dbinfo.cpp storage/ndb/src/kernel/blocks/pgman.cpp storage/ndb/src/kernel/blocks/pgman.hpp storage/ndb/src/kernel/vm/Ndbinfo.hpp storage/ndb/src/kernel/vm/NdbinfoTables.cpp storage/ndb/tools/ndbinfo_sql.cpp 3940 Jonas Oreland 2010-11-03 [merge] ndb - merge 70 to 71 modified: storage/ndb/src/kernel/blocks/qmgr/QmgrMain.cpp === modified file 'mysql-test/suite/ndb/r/ndbinfo.result' === modified file 'mysql-test/suite/ndb/r/ndbinfo.result' --- a/mysql-test/suite/ndb/r/ndbinfo.result 2010-08-18 10:20:02 +0000 +++ b/mysql-test/suite/ndb/r/ndbinfo.result 2010-11-03 09:12:49 +0000 @@ -35,9 +35,10 @@ 7 resources resources usage (a.k.a superpool) 8 counters monotonic counters 9 nodes node status +10 diskpagebuffer disk page buffer info SELECT COUNT(*) FROM ndb$tables; COUNT(*) -10 +11 SELECT * FROM ndb$tables WHERE table_id = 2; table_id table_name comment 2 test for testing @@ -47,11 +48,12 @@ 7 resources resources usage (a.k.a superpool) 8 counters monotonic counters 9 nodes node status +10 diskpagebuffer disk page buffer info SELECT * FROM ndb$tables WHERE table_name = 'LOGDESTINATION'; table_id table_name comment SELECT COUNT(*) FROM ndb$tables t1, ndb$tables t2 WHERE t1.table_id = t1.table_id; COUNT(*) -100 +121 SELECT table_id, table_name, comment from ndb$tables WHERE table_id > 2 AND table_id <= 5 ORDER BY table_id; @@ -66,6 +68,7 @@ table_id table_name 1 columns 8 counters +10 diskpagebuffer 6 logbuffers 5 logspaces 9 nodes @@ -119,6 +122,7 @@ 7 8 9 +10 TRUNCATE ndb$tables; ERROR HY000: Table 'ndb$tables' is read only === modified file 'scripts/mysql_system_tables.sql' --- a/scripts/mysql_system_tables.sql 2010-08-17 13:53:11 +0000 +++ b/scripts/mysql_system_tables.sql 2010-11-03 09:12:49 +0000 @@ -156,6 +156,16 @@ EXECUTE stmt; DROP PREPARE stmt; +SET @str=IF(@have_ndbinfo,'DROP VIEW IF EXISTS ndbinfo.diskpagebuffer','SET @dummy = 0'); +PREPARE stmt FROM @str; +EXECUTE stmt; +DROP PREPARE stmt; + +SET @str=IF(@have_ndbinfo,'DROP VIEW IF EXISTS ndbinfo.diskpagebufferusage','SET @dummy = 0'); +PREPARE stmt FROM @str; +EXECUTE stmt; +DROP PREPARE stmt; + # Drop any old lookup tables in ndbinfo SET @str=IF(@have_ndbinfo,'DROP TABLE IF EXISTS ndbinfo.blocks','SET @dummy = 0'); PREPARE stmt FROM @str; @@ -277,6 +287,17 @@ EXECUTE stmt; DROP PREPARE stmt; +# ndbinfo.ndb$diskpagebuffer +SET @str=IF(@have_ndbinfo,'DROP TABLE IF EXISTS `ndbinfo`.`ndb$diskpagebuffer`','SET @dummy = 0'); +PREPARE stmt FROM @str; +EXECUTE stmt; +DROP PREPARE stmt; + +SET @str=IF(@have_ndbinfo,'CREATE TABLE `ndbinfo`.`ndb$diskpagebuffer` (`node_id` INT UNSIGNED,`block_instance` INT UNSIGNED,`pages_written` BIGINT UNSIGNED COMMENT "Pages written to disk",`pages_written_lcp` BIGINT UNSIGNED COMMENT "Pages written by local checkpoint",`pages_read` BIGINT UNSIGNED COMMENT "Pages read from disk",`log_waits` BIGINT UNSIGNED COMMENT "Page writes waiting for log to be written to disk",`page_requests_direct_return` BIGINT UNSIGNED COMMENT "Page in buffer and no requests waiting for it",`page_requests_wait_queue` BIGINT UNSIGNED COMMENT "Page in buffer, but some requests are already waiting for it",`page_requests_wait_io` BIGINT UNSIGNED COMMENT "Page not in buffer, waiting to be read from disk") COMMENT="disk page buffer info" ENGINE=NDBINFO','SET @dummy = 0'); +PREPARE stmt FROM @str; +EXECUTE stmt; +DROP PREPARE stmt; + # ndbinfo.blocks SET @str=IF(@have_ndbinfo,'CREATE TABLE `ndbinfo`.`blocks` (block_number INT UNSIGNED PRIMARY KEY, block_name VARCHAR(512))','SET @dummy = 0'); PREPARE stmt FROM @str; @@ -341,3 +362,14 @@ EXECUTE stmt; DROP PREPARE stmt; +# ndbinfo.diskpagebuffer +SET @str=IF(@have_ndbinfo,'CREATE OR REPLACE SQL SECURITY INVOKER VIEW `ndbinfo`.`diskpagebuffer` AS SELECT node_id, block_instance, pages_written, pages_written_lcp, pages_read, log_waits, page_requests_direct_return, page_requests_wait_queue, page_requests_wait_io FROM ndbinfo.ndb$diskpagebuffer','SET @dummy = 0'); +PREPARE stmt FROM @str; +EXECUTE stmt; +DROP PREPARE stmt; + +# ndbinfo.diskpagebufferusage +SET @str=IF(@have_ndbinfo,'CREATE OR REPLACE SQL SECURITY INVOKER VIEW `ndbinfo`.`diskpagebufferusage` AS SELECT node_id, SUM(pages_written) AS pages_written, SUM(pages_written_lcp) AS pages_written_LCP, SUM(pages_read) AS pages_read, SUM(log_waits) AS log_waits, (SUM(page_requests_direct_return) + SUM(page_requests_wait_queue) + SUM(page_requests_wait_io) ) AS `page_requests_total =`, SUM(page_requests_direct_return) AS `(PR_direct_return`, SUM(page_requests_wait_queue) AS ` + PR_wait_queue`, SUM(page_requests_wait_io) AS ` + PR_wait_IO)` FROM ndbinfo.ndb$diskpagebuffer GROUP BY node_id','SET @dummy = 0'); +PREPARE stmt FROM @str; +EXECUTE stmt; +DROP PREPARE stmt; === modified file 'storage/ndb/src/kernel/blocks/dbinfo/Dbinfo.cpp' --- a/storage/ndb/src/kernel/blocks/dbinfo/Dbinfo.cpp 2010-08-17 11:47:55 +0000 +++ b/storage/ndb/src/kernel/blocks/dbinfo/Dbinfo.cpp 2010-11-03 09:12:49 +0000 @@ -29,7 +29,8 @@ #endif Uint32 dbinfo_blocks[] = { DBACC, DBTUP, BACKUP, DBTC, SUMA, DBUTIL, - TRIX, DBTUX, DBDICT, CMVMI, DBLQH, LGMAN, 0}; + TRIX, DBTUX, DBDICT, CMVMI, DBLQH, LGMAN, + PGMAN, 0}; Dbinfo::Dbinfo(Block_context& ctx) : SimulatedBlock(DBINFO, ctx) === modified file 'storage/ndb/src/kernel/blocks/pgman.cpp' --- a/storage/ndb/src/kernel/blocks/pgman.cpp 2010-08-20 11:10:25 +0000 +++ b/storage/ndb/src/kernel/blocks/pgman.cpp 2010-11-03 09:12:49 +0000 @@ -72,6 +72,7 @@ addRecSignal(GSN_DATA_FILE_ORD, &Pgman::execDATA_FILE_ORD); addRecSignal(GSN_RELEASE_PAGES_REQ, &Pgman::execRELEASE_PAGES_REQ); + addRecSignal(GSN_DBINFO_SCANREQ, &Pgman::execDBINFO_SCANREQ); // loop status m_stats_loop_on = false; @@ -187,15 +188,6 @@ { } -Pgman::Stats::Stats() : - m_num_pages(0), - m_num_hot_pages(0), - m_current_io_waits(0), - m_page_hits(0), - m_page_faults(0) -{ -} - void Pgman::execSTTOR(Signal* signal) { @@ -1448,6 +1440,7 @@ ndbrequire(m_stats.m_current_io_waits > 0); m_stats.m_current_io_waits--; + m_stats.m_pages_read++; ptr.p->m_last_lcp = m_last_lcp_complete; do_busy_loop(signal, true); @@ -1490,6 +1483,7 @@ else { ndbrequire(ret == 0); + m_stats.m_log_waits++; state |= Page_entry::LOGSYNC; } set_page_state(ptr, state); @@ -1542,6 +1536,7 @@ state &= ~ Page_entry::LCP; ndbrequire(m_lcp_outstanding); m_lcp_outstanding--; + m_stats.m_pages_written_lcp++; if (ptr.p->m_copy_page_i != RNIL) { jam(); @@ -1552,6 +1547,10 @@ return; } } + else + { + m_stats.m_pages_written++; + } set_page_state(ptr, state); do_busy_loop(signal, true); @@ -1715,6 +1714,7 @@ ! (req_flags & Page_request::UNLOCK_PAGE)) { ptr.p->m_state |= (req_flags & DIRTY_FLAGS ? Page_entry::DIRTY : 0); + m_stats.m_page_requests_direct_return++; if (ptr.p->m_copy_page_i != RNIL) { D("m_real_page_i != RNIL); + m_stats.m_page_requests_direct_return++; return ptr.p->m_real_page_i; } } @@ -1757,6 +1758,12 @@ } // queue the request + + if ((state & Page_entry::MAPPED) && ! (state & Page_entry::PAGEOUT)) + m_stats.m_page_requests_wait_q++; + else + m_stats.m_page_requests_wait_io++; + Ptr req_ptr; { Local_page_request_list req_list(m_page_request_pool, ptr.p->m_requests); @@ -2780,3 +2787,36 @@ SET_ERROR_INSERT_VALUE(11009); } } + +void +Pgman::execDBINFO_SCANREQ(Signal *signal) +{ + DbinfoScanReq req= *(DbinfoScanReq*)signal->theData; + const Ndbinfo::ScanCursor* cursor = + reinterpret_cast(DbinfoScan::getCursorPtr(&req)); + Ndbinfo::Ratelimit rl; + + jamEntry(); + switch(req.tableId) { + case Ndbinfo::DISKPAGEBUFFER_TABLEID: + { + jam(); + BlockNumber bn = blockToMain(number()); + Ndbinfo::Row row(signal, req); + row.write_uint32(getOwnNodeId()); + row.write_uint32(instance()); // block instance + row.write_uint64(m_stats.m_pages_written); + row.write_uint64(m_stats.m_pages_written_lcp); + row.write_uint64(m_stats.m_pages_read); + row.write_uint64(m_stats.m_log_waits); + row.write_uint64(m_stats.m_page_requests_direct_return); + row.write_uint64(m_stats.m_page_requests_wait_q); + row.write_uint64(m_stats.m_page_requests_wait_io); + + ndbinfo_send_row(signal, req, row, rl); + } + default: + break; + } + ndbinfo_send_scan_conf(signal, req, rl); +} === modified file 'storage/ndb/src/kernel/blocks/pgman.hpp' --- a/storage/ndb/src/kernel/blocks/pgman.hpp 2010-08-20 11:10:25 +0000 +++ b/storage/ndb/src/kernel/blocks/pgman.hpp 2010-11-03 09:12:49 +0000 @@ -424,12 +424,32 @@ // runtime sizes and statistics struct Stats { - Stats(); + Stats() : + m_num_pages(0), + m_num_hot_pages(0), + m_current_io_waits(0), + m_page_hits(0), + m_page_faults(0), + m_pages_written(0), + m_pages_written_lcp(0), + m_pages_read(0), + m_log_waits(0), + m_page_requests_direct_return(0), + m_page_requests_wait_q(0), + m_page_requests_wait_io(0) + {} Uint32 m_num_pages; // current number of cache pages Uint32 m_num_hot_pages; Uint32 m_current_io_waits; Uint64 m_page_hits; Uint64 m_page_faults; + Uint64 m_pages_written; + Uint64 m_pages_written_lcp; + Uint64 m_pages_read; + Uint64 m_log_waits; // wait for undo WAL to flush the log recs + Uint64 m_page_requests_direct_return; + Uint64 m_page_requests_wait_q; + Uint64 m_page_requests_wait_io; } m_stats; enum CallbackIndex { @@ -459,6 +479,8 @@ void execDATA_FILE_ORD(Signal*); + void execDBINFO_SCANREQ(Signal*); + private: static Uint32 get_sublist_no(Page_state state); void set_page_state(Ptr ptr, Page_state new_state); === modified file 'storage/ndb/src/kernel/vm/Ndbinfo.hpp' --- a/storage/ndb/src/kernel/vm/Ndbinfo.hpp 2010-03-10 10:13:54 +0000 +++ b/storage/ndb/src/kernel/vm/Ndbinfo.hpp 2010-11-03 09:12:49 +0000 @@ -46,7 +46,8 @@ LOGBUFFERS_TABLEID = 6, RESOURCES_TABLEID = 7, COUNTERS_TABLEID = 8, - NODES_TABLEID = 9 + NODES_TABLEID = 9, + DISKPAGEBUFFER_TABLEID = 10 }; struct Table { === modified file 'storage/ndb/src/kernel/vm/NdbinfoTables.cpp' --- a/storage/ndb/src/kernel/vm/NdbinfoTables.cpp 2010-01-20 08:09:28 +0000 +++ b/storage/ndb/src/kernel/vm/NdbinfoTables.cpp 2010-11-03 09:12:49 +0000 @@ -153,6 +153,22 @@ } }; +DECLARE_NDBINFO_TABLE(DISKPAGEBUFFER, 9) = +{ { "diskpagebuffer", 9, 0, "disk page buffer info" }, + { + {"node_id", Ndbinfo::Number, ""}, + {"block_instance", Ndbinfo::Number, ""}, + + {"pages_written", Ndbinfo::Number64, "Pages written to disk"}, + {"pages_written_lcp", Ndbinfo::Number64, "Pages written by local checkpoint"}, + {"pages_read", Ndbinfo::Number64, "Pages read from disk"}, + {"log_waits", Ndbinfo::Number64, "Page writes waiting for log to be written to disk"}, + {"page_requests_direct_return", Ndbinfo::Number64, "Page in buffer and no requests waiting for it"}, + {"page_requests_wait_queue", Ndbinfo::Number64, "Page in buffer, but some requests are already waiting for it"}, + {"page_requests_wait_io", Ndbinfo::Number64, "Page not in buffer, waiting to be read from disk"}, + } +}; + #define DBINFOTBL(x) { Ndbinfo::x##_TABLEID, (Ndbinfo::Table*)&ndbinfo_##x } static @@ -171,7 +187,8 @@ DBINFOTBL(LOGBUFFERS), DBINFOTBL(RESOURCES), DBINFOTBL(COUNTERS), - DBINFOTBL(NODES) + DBINFOTBL(NODES), + DBINFOTBL(DISKPAGEBUFFER) }; static int no_ndbinfo_tables = === modified file 'storage/ndb/tools/ndbinfo_sql.cpp' --- a/storage/ndb/tools/ndbinfo_sql.cpp 2010-08-17 13:53:11 +0000 +++ b/storage/ndb/tools/ndbinfo_sql.cpp 2010-11-03 09:12:49 +0000 @@ -154,6 +154,28 @@ "WHERE block_number IN (248, 254) AND " " (pool_name = \"Index memory\" OR pool_name = \"Data memory\") " "GROUP BY node_id, memory_type" + }, + { "diskpagebuffer", + "SELECT node_id, block_instance, " + "pages_written, pages_written_lcp, pages_read, log_waits, " + "page_requests_direct_return, page_requests_wait_queue, page_requests_wait_io " + "FROM .diskpagebuffer" + }, + { "diskpagebufferusage", + "SELECT node_id, " + " SUM(pages_written) AS pages_written, " + " SUM(pages_written_lcp) AS pages_written_LCP, " + " SUM(pages_read) AS pages_read, " + " SUM(log_waits) AS log_waits, " + " (SUM(page_requests_direct_return) + " + " SUM(page_requests_wait_queue) + " + " SUM(page_requests_wait_io) " + " ) AS `page_requests_total =`, " + " SUM(page_requests_direct_return) AS `(PR_direct_return`, " + " SUM(page_requests_wait_queue) AS ` + PR_wait_queue`, " + " SUM(page_requests_wait_io) AS ` + PR_wait_IO)` " + "FROM .diskpagebuffer " + "GROUP BY node_id" } }; No bundle (reason: useless for push emails).