| PostgreSQL 7.4.1 ʸ½ñ | ||||
|---|---|---|---|---|
| Á°¤Î¥Ú¡¼¥¸ | ´¬Ìᤷ | Âè 23¾Ï¥Ç¡¼¥¿¥Ù¡¼¥¹³èÆ°¾õ¶·¤Î´Æ»ë | ÁáÁ÷¤ê | ¼¡¤Î¥Ú¡¼¥¸ |
PostgreSQL ¤Î Åý·×¾ðÊó¼ý½¸´ï ¤Ï¥µ¡¼¥Ð¤Î³èÆ°¾õ¶·¤Ë´Ø¤¹¤ë¾ðÊó¤ò¼ý½¸¤·¡¢Êó¹ð¤¹¤ë¥µ¥Ö¥·¥¹¥Æ¥à¤Ç¤¹¡£ ¸½ºß¡¢¼ý½¸´ï¤Ï¥Æ¡¼¥Ö¥ë¤È¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ø¤Î¥¢¥¯¥»¥¹¤ò¥Ç¥£¥¹¥¯¥Ö¥í¥Ã¥¯¤ª¤è¤Ó¸Ä¡¹¤Î¹Ôñ°Ì¤Ç¿ô¤¨¤ë¤³¤È¤¬¤Ç¤¤Þ¤¹¡£¤Þ¤¿¡¢Â¾¤Î¥µ¡¼¥Ð¥×¥í¥»¥¹¤Ë¤è¤Ã¤Æ¸½ºß¼Â¹Ô¤µ¤ì¤Æ¤¤¤ëÌ䤤¹ç¤ï¤»¤òÀµ³Î¤Ë·èÄꤹ¤ëµ¡Ç½¤ò»ý¤Á¤Þ¤¹¡£
Åý·×¾ðÊó¤Î¼ý½¸¤Ë¤è¤Ã¤ÆÌ䤤¹ç¤ï¤»¤Î¼Â¹Ô¤Ë¤¹¤³¤·¥ª¡¼¥Ð¥Ø¥Ã¥É¤¬²Ã¤ï¤ê¤Þ¤¹¤Î¤Ç¡¢¥·¥¹¥Æ¥à¤Ï¾ðÊó¤ò¼ý½¸¤¹¤ë¤è¤¦¤Ë¤â¤·¤Ê¤¤¤è¤¦¤Ë¤âÀßÄꤹ¤ë¤³¤È¤¬¤Ç¤¤Þ¤¹¡£ ¤³¤ì¤ÏÄ̾ï¤Ï postgresql.conf Æâ¤ÇÀßÄꤵ¤ì¤ë¡¢ÀßÄê¥Ñ¥é¥á¡¼¥¿¤Ë¤è¤Ã¤ÆÀ©¸æ¤µ¤ì¤Þ¤¹ (ÀßÄê¥Ñ¥é¥á¡¼¥¿¤ÎÀßÄê¤Ë¤Ä¤¤¤Æ¤Î¾ÜºÙ¤Ï ¹à16.4 ¤ò»²¾È¤·¤Æ²¼¤µ¤¤)¡£
Åý·×¾ðÊó¼ý½¸´ï¤òÁ´¤Æµ¯Æ°¤¹¤ë¤Ë¤Ï¡¢stats_start_collector ¥Ñ¥é¥á¡¼¥¿¤ò true ¤ËÀßÄꤹ¤ëɬÍפ¬¤¢¤ê¤Þ¤¹¡£ ¤³¤ì¤Ï¥Ç¥Õ¥©¥ë¥È¤Ç¤¢¤ê¡¢¿ä¾©¤¹¤ëÀßÄê¤Ç¤¹¤¬¡¢Åý·×¾ðÊó¤Ë¶½Ì£¤¬¤Ê¤¯¡¢Á´¤Æ¤Î»Ä¸¤¹¤ë¥ª¡¼¥Ð¥Ø¥Ã¥É¤òÄù¤á½Ð¤·¤¿¤¤¤Î¤Ç¤¢¤ì¤Ð̵¸ú¤Ë¤¹¤ë¤³¤È¤â¤Ç¤¤Þ¤¹¡£ (¤·¤«¤·¤³¤ì¤Ë¤è¤ëÀ®²Ì¤Ï¤ï¤º¤«¤Ê¤â¤Î¤Ç¤¹¡£) ¥µ¡¼¥Ð¼Â¹ÔÃæ¤Ë¤³¤Î¥ª¥×¥·¥ç¥ó¤òÊѹ¹¤¹¤ë¤³¤È¤¬¤Ç¤¤Ê¤¤¤³¤È¤ËÃí°Õ¤·¤Æ²¼¤µ¤¤¡£
stats_command_string¡¢stats_block_level¡¢stats_row_level ¥Ñ¥é¥á¡¼¥¿¤Ï¡¢¼ý½¸´ï¤Ë¼ÂºÝ¤ËÁ÷¿®¤µ¤ì¤ë¾ðÊóÎ̤òÀ©¸æ¤·¡¢¤Ä¤Þ¤ê¡¢¼Â¹Ô»þ¤ËȯÀ¸¤¹¤ë¥ª¡¼¥Ð¥Ø¥Ã¥É¤ÎÎ̤ò·èÄꤷ¤Þ¤¹¡£ ¤³¤ì¤é¤Ï¤½¤ì¤¾¤ì¡¢¥µ¡¼¥Ð¥×¥í¥»¥¹¤¬¸½ºß¤Î¥³¥Þ¥ó¥Éʸ»úÎ󡢥ǥ£¥¹¥¯¥Ö¥í¥Ã¥¯¥ì¥Ù¥ë¤Î¥¢¥¯¥»¥¹Åý·×¡¢¹Ô¥ì¥Ù¥ë¤Î¥¢¥¯¥»¥¹Åý·×¤ò¼ý½¸´ï¤ËÁ÷¿®¤¹¤ë¤«¤É¤¦¤«¤ò·èÄꤷ¤Þ¤¹¡£ Ä̾¤³¤ì¤é¤ÎÊÑ¿ô¤ÏÁ´¤Æ¤Î¥µ¡¼¥Ð¥×¥í¥»¥¹¤ËŬÍѤǤ¤ë¤è¤¦¤Ë postgresql.conf Æâ¤ÇÀßÄꤵ¤ì¤Þ¤¹¡£ ¤·¤«¤·¡¢SET ¥³¥Þ¥ó¥É¤ò»ÈÍѤ·¤Æ¡¢¸ÄÊ̤Υµ¡¼¥Ð¥×¥í¥»¥¹¤Ç͸ú¤Þ¤¿¤Ï̵¸ú¤Ë¤¹¤ë¤³¤È¤¬¤Ç¤¤Þ¤¹¡£ (°ìÈ̥桼¥¶¤¬¤½¤Î³èÆ°¤ò´ÉÍý¼Ô¤Ë±£¤¹¤³¤È¤òËɻߤ¹¤ë¤¿¤á¤Ë¡¢¥¹¡¼¥Ñ¡¼¥æ¡¼¥¶¤Î¤ß¤¬ SET ¤ò»ÈÍѤ·¤Æ¤³¤ì¤é¤ÎÊÑ¿ô¤òÊѹ¹¤¹¤ë¤³¤È¤¬¤Ç¤¤Þ¤¹¡£)
Ãí°Õ: stats_command_string¡¢stats_block_level¡¢stats_row_level¥Ñ¥é¥á¡¼¥¿¤Ï¥Ç¥Õ¥©¥ë¥È¤Ç false ¤Ç¤¹¤Î¤Ç¡¢¥Ç¥Õ¥©¥ë¥È¤ÎÀßÄê¤Ç¤Ï¼ÂºÝ¤Ë¤ÏÅý·×¾ðÊó¤Ï²¿¤â¼ý½¸¤µ¤ì¤Þ¤»¤ó¡£ Åý·×¾ðÊó¼ý½¸´ï¤ò»ÈÍѤ·¤ÆÍÍѤʷë²Ì¤òÆÀ¤ëÁ°¤Ë¡¢¤³¤ì¤é¤ò 1 ¤Ä¤Þ¤¿¤ÏÊ£¿ô¸ÄÀßÄꤷ¤Ê¤±¤ì¤Ð¤Ê¤ê¤Þ¤»¤ó¡£
Åý·×¾ðÊó¤Î¼ý½¸·ë²Ì¤òɽ¼¨¤¹¤ë¤¿¤á¤Î¡¢Â¿¤¯¤ÎÄêµÁºÑ¤ß¤Î¥Ó¥å¡¼¤¬¤¢¤ê¡¢É½23-1 ¤Ë¥ê¥¹¥È¤µ¤ì¤Æ¤¤¤Þ¤¹¡£ ¾¤Ë¤â¡¢´ðÁÃŪ¤ÊÅý·×¾ðÊó´Ø¿ô¤ò»ÈÍѤ·¤¿¥«¥¹¥¿¥à¥Ó¥å¡¼¤ò¹½ÃÛ¤¹¤ë¤³¤È¤â¤Ç¤¤Þ¤¹¡£
¤³¤ÎÅý·×¾ðÊó¤ò»ÈÍѤ·¤Æ¡¢¸½ºß¤Î³èÆ°¾õ¶·¤ò´Æ»ë¤¹¤ë¾ì¹ç¡¢¤³¤Î¾ðÊó¤Ï¨ºÂ¤Ë¹¹¿·¤µ¤ì¤Ê¤¤¤³¤È¤òǧ¼±¤¹¤ë¤³¤È¤¬½ÅÍפǤ¹¡£ ¸ÄÊ̤Υµ¡¼¥Ð¥×¥í¥»¥¹¤Ï¡¢Â¾¤Î¥¯¥é¥¤¥¢¥ó¥È¤«¤é¤Î¥³¥Þ¥ó¥É¤òÂԤľÁ°¤Ë¡¢¿·¤·¤¤¥¢¥¯¥»¥¹¿ô¤ò¼ý½¸´ï¤ËÁ÷¿®¤·¤Þ¤¹¡£ ¤Ç¤¹¤Î¤Ç¡¢¼Â¹ÔÃæ¤ÎÌ䤤¹ç¤ï¤»¤Ïɽ¼¨¾å¤ÎÁíϤˤϱƶÁ¤òÍ¿¤¨¤Þ¤»¤ó¡£ ¤Þ¤¿¡¢¼ý½¸´ï¼«ÂΤ⤪¤è¤½ pgstat_stat_interval (¥Ç¥Õ¥©¥ë¥È¤Ç¤Ï 500) ¥ß¥êÉä˰ìÅÙ¿·¤·¤¤ÁíϤò½ÐÎϤ·¤Þ¤¹¡£ ¤Ç¤¹¤Î¤Ç¡¢É½¼¨¾å¤ÎÁíϤϼºݤγèÆ°¤«¤éÃÙ¤ì¤Æɽ¼¨¤µ¤ì¤Þ¤¹¡£
¤³¤Î¾¤Î½ÅÍפʥݥ¤¥ó¥È¤Ï¡¢¤¤¤Ä¥µ¡¼¥Ð¥×¥í¥»¥¹¤¬Åý·×¾ðÊó¤òɽ¼¨¤¹¤ë¤è¤¦¤Ë¿Ò¤Í¤é¤ì¤ë¤«¤Ç¤¹¡£ ¥µ¡¼¥Ð¥×¥í¥»¥¹¤Ï¡¢¤Þ¤º¼ý½¸´ï¤Ë¤è¤Ã¤Æȯ¹Ô¤µ¤ì¤¿ºÇ¤âºÇ¶á¤ÎÁíϤò¼è¤ê½Ð¤·¤Þ¤¹¡£¤½¤·¤Æ¡¢¸½ºß¤Î¥È¥é¥ó¥¶¥¯¥·¥ç¥ó¤¬½ª¤ë¤Þ¤Ç¡¢Á´¤Æ¤ÎÅý·×¾ðÊó¥Ó¥å¡¼¤È´Ø¿ô¤Ë¤ª¤¤¤Æ¤³¤Î¥¹¥Ê¥Ã¥×¥·¥ç¥Ã¥È¤ò»ÈÍѤ·Â³¤±¤Þ¤¹¡£ ¤Ç¤¹¤«¤é¡¢¸½ºß¤Î¥È¥é¥ó¥¶¥¯¥·¥ç¥ó¤ò³¤±¤Æ¤¤¤ë´Ö¡¢Åý·×¾ðÊó¤ÏÊѹ¹¤µ¤ì¤Þ¤»¤ó¡£ ¤³¤ì¤Ï¥Ð¥°¤Ç¤Ï¤Ê¤¯¡¢ÆÃħ¤Ç¤¹¡£ ¤Ê¤¼¤Ê¤é¡¢¤³¤ì¤Ë¤è¤ê¡¢ÃΤé¤Ê¤¤´Ö¤ËÃͤ¬Êѹ¹¤¹¤ë¤³¤È¤ò¹Íθ¤¹¤ë¤³¤È¤Ê¤¯¡¢Åý·×¾ðÊó¤ËÂФ·¤ÆÊ£¿ô¤ÎÌ䤤¹ç¤ï¤»¤ò¼Â¹Ô¤·¡¢¤½¤Î·ë²Ì¤òÁê´Ø¤¹¤ë¤³¤È¤¬¤Ç¤¤ë¤«¤é¤Ç¤¹¡£ ¤·¤«¤·¡¢³ÆÌ䤤¹ç¤ï¤»¤Ç¿·¤·¤¤·ë²Ì¤ò¼è¤ê½Ð¤·¤¿¤¤¾ì¹ç¤Ï¡¢³Î¼Â¤Ë¥È¥é¥ó¥¶¥¯¥·¥ç¥ó¥Ö¥í¥Ã¥¯¤Î³°Â¦¤Ç¤½¤ÎÌ䤤¹ç¤ï¤»¤ò¹Ô¤Ê¤Ã¤Æ²¼¤µ¤¤¡£
ɽ 23-1. ɸ½àÅý·×¾ðÊó¥Ó¥å¡¼
| ¥Ó¥å¡¼Ì¾ | ÀâÌÀ |
|---|---|
| pg_stat_activity | ¥µ¡¼¥Ð¥×¥í¥»¥¹Åö¤¿¤ê 1 ¹Ô¤Î·Á¤Ç¡¢¥×¥í¥»¥¹¤Î ID¡¢¥Ç¡¼¥¿¥Ù¡¼¥¹¡¢¥æ¡¼¥¶¡¢¸½ºß¤ÎÌ䤤¹ç¤ï¤»¡¢¸½ºß¤ÎÌ䤤¹ç¤ï¤»¤Î¼Â¹Ô³«»Ï»þ¹ï¤òɽ¼¨¤·¤Þ¤¹¡£ ¸½ºß¤ÎÌ䤤¹ç¤ï¤»¤Ë¤Ä¤¤¤Æ¤Î¥Ç¡¼¥¿¤Ïstats_command_string¥Ñ¥é¥á¡¼¥¿¤¬Í¸ú¤Ê¾ì¹ç¤Ë¤Î¤ßɽ¼¨¤µ¤ì¤Þ¤¹¡£ ¹¹¤Ë¡¢¤½¤ÎÎó¤Ï¡¢¥Ó¥å¡¼¤ò³Îǧ¤¹¤ë¥æ¡¼¥¶¤¬¥¹¡¼¥Ñ¡¼¥æ¡¼¥¶¡¢¤¢¤ë¤¤¤Ï¡¢Êó¹ðÂÐ¾Ý¥×¥í¥»¥¹¤ò½êͤ¹¤ë¥æ¡¼¥¶¤ÈƱ¤¸¤Ç¤Ê¤±¤ì¤Ð NULL ¤È¤·¤ÆÆɤ߽Фµ¤ì¤Þ¤¹¡£ (¼ý½¸´ï¤Ë¤è¤ëÊó¹ð¤ÎÃÙ¤ì¤Î¤¿¤á¡¢¸½ºß¤ÎÌ䤤¹ç¤ï¤»¤ÏĹ»þ´Ö¼Â¹ÔÃæ¤ÎÌ䤤¹ç¤ï¤»¤Ë¤ª¤¤¤Æ¤Î¤ß¸½ºß¤Î¤â¤Î¤òɽ¤·¤Þ¤¹¡£) |
| pg_stat_database | ¥Ç¡¼¥¿¥Ù¡¼¥¹Åö¤¿¤ê 1 ¹Ô¤Î·Á¤Ç¡¢¤½¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹¤ËÂФ·¤Æ¡¢³èÆ°Ãæ¤Î¥Ð¥Ã¥¯¥¨¥ó¥É¥µ¡¼¥Ð¿ô¡¢¥³¥ß¥Ã¥È¤µ¤ì¤¿¥È¥é¥ó¥¶¥¯¥·¥ç¥ó¤ÎÁí¿ô¡¢¥í¡¼¥ë¥Ð¥Ã¥¯¤µ¤ì¤¿¥È¥é¥ó¥¶¥¯¥·¥ç¥ó¤ÎÁí¿ô¡¢ÆɤߤȤé¤ì¤¿¥Ç¥£¥¹¥¯¥Ö¥í¥Ã¥¯¤ÎÁí¿ô¡¢¥Ð¥Ã¥Õ¥¡¥Ò¥Ã¥È (¤Ä¤Þ¤ê¡¢¥Ð¥Ã¥Õ¥¡¥¥ã¥Ã¥·¥å¤ËÂоݤȤ¹¤ë¥Ö¥í¥Ã¥¯¤¬Â¸ºß¤¹¤ë¤¿¤á¤ËËɻߤµ¤ì¤¿¥Ö¥í¥Ã¥¯ÆɤߤȤêÍ×µá) ¤ÎÁí¿ô¤òɽ¼¨¤·¤Þ¤¹¡£ |
| pg_stat_all_tables | ¸½ºß¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Î³Æ¥Æ¡¼¥Ö¥ë¤Ë´Ø¤¹¤ë¡¢¥·¡¼¥±¥ó¥·¥ã¥ë¥¹¥¥ã¥ó¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¥¹¥¥ã¥ó¤ÎÁí¿ô¡¢³Æ¼ï¥¹¥¥ã¥ó¤Ë¤è¤Ã¤ÆÊÖ¤µ¤ì¤¿¹Ô¤ÎÁí¿ô¡¢ÁÞÆþ¡¢¹¹¿·¡¢ºï½ü¤µ¤ì¤¿¹Ô¤ÎÁí¿ô¡£ |
| pg_stat_sys_tables | ¥·¥¹¥Æ¥à¥Æ¡¼¥Ö¥ë¤Î¤ß¤¬É½¼¨¤µ¤ì¤ëÅÀ¤ò½ü¤¡¢pg_stat_all_tables ¤ÈƱ¤¸¤Ç¤¹¡£ |
| pg_stat_user_tables | ¥æ¡¼¥¶¥Æ¡¼¥Ö¥ë¤Î¤ß¤¬É½¼¨¤µ¤ì¤ëÅÀ¤ò½ü¤¡¢pg_stat_all_tables ¤ÈƱ¤¸¤Ç¤¹¡£ |
| pg_stat_all_indexes | ¸½ºß¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Î³Æ¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ë´Ø¤¹¤ë¡¢¤½¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤ò»ÈÍѤ·¤¿¥¤¥ó¥Ç¥Ã¥¯¥¹¥¹¥¥ã¥ó¿ô¡¢ÆɤߤȤé¤ì¤¿¥¤¥ó¥Ç¥Ã¥¯¥¹¹Ô¿ô¡¢Àµ¾ï¤ËÃê½Ð¤µ¤ì¤¿¥Ò¡¼¥×¹Ô¿ô (¤³¤ÎÃͤϡ¢Í¸ú´ü¸ÂÀÚ¤ì¤È¤Ê¤Ã¤¿¥Ò¡¼¥×¹Ô¤ò¼¨¤¹¥¤¥ó¥Ç¥Ã¥¯¥¹¹àÌܤ¬¤¢¤ë»þ¤Ë¾®¤µ¤¯¤Ê¤ê¤Þ¤¹)¡£ |
| pg_stat_sys_indexes | ¥·¥¹¥Æ¥à¥Æ¡¼¥Ö¥ë¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤Î¤ß¤¬É½¼¨¤µ¤ì¤ëÅÀ¤ò½ü¤¡¢pg_stat_all_indexes ¤ÈƱ¤¸¤Ç¤¹¡£ |
| pg_stat_user_indexes | ¥æ¡¼¥¶¥Æ¡¼¥Ö¥ë¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤Î¤ß¤¬É½¼¨¤µ¤ì¤ëÅÀ¤ò½ü¤¡¢pg_stat_all_indexes ¤ÈƱ¤¸¤Ç¤¹¡£ |
| pg_statio_all_tables | ¸½ºß¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Î³Æ¥Æ¡¼¥Ö¥ë¤Ë´Ø¤¹¤ë¡¢¤½¤Î¥Æ¡¼¥Ö¥ë¤«¤éÆɤߤȤé¤ì¤¿¥Ç¥£¥¹¥¯¥Ö¥í¥Ã¥¯¤ÎÁí¿ô¡¢¥Ð¥Ã¥Õ¥¡¥Ò¥Ã¥È¿ô¡¢¤½¤Î¥Æ¡¼¥Ö¥ë¤Ë´Ø¤¹¤ëÁ´¤Æ¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ë´Ø¤¹¤ëÆɤߤȤé¤ì¤¿¥Ç¥£¥¹¥¯¥Ö¥í¥Ã¥¯¿ô¤È¥Ð¥Ã¥Õ¥¡¥Ò¥Ã¥È¿ô¡¢(¸ºß¤¹¤ë¾ì¹ç)¤½¤Î¥Æ¡¼¥Ö¥ë¤ÎÊä½õŪ¤Ê TOAST ¥Æ¡¼¥Ö¥ë¤«¤éÆɤߤȤé¤ì¤¿¥Ç¥£¥¹¥¯¥Ö¥í¥Ã¥¯¿ô¤È¥Ð¥Ã¥Õ¥¡¥Ò¥Ã¥È¿ô¡¢TOAST ¥Æ¡¼¥Ö¥ë¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ë´Ø¤¹¤ëÆɤߤȤé¤ì¤¿¥Ç¥£¥¹¥¯¥Ö¥í¥Ã¥¯¿ô¤È¥Ð¥Ã¥Õ¥¡¥Ò¥Ã¥È¿ô¡£ |
| pg_statio_sys_tables | ¥·¥¹¥Æ¥à¥Æ¡¼¥Ö¥ë¤Î¤ß¤¬É½¼¨¤µ¤ì¤ëÅÀ¤ò½ü¤¡¢pg_statio_all_tables ¤ÈƱ¤¸¤Ç¤¹¡£ |
| pg_statio_user_tables | ¥æ¡¼¥¶¥Æ¡¼¥Ö¥ë¤Î¤ß¤¬É½¼¨¤µ¤ì¤ëÅÀ¤ò½ü¤¡¢pg_statio_all_tables ¤ÈƱ¤¸¤Ç¤¹¡£ |
| pg_statio_all_indexes | ¸½ºß¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Î³Æ¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ë´Ø¤¹¤ë¡¢¤½¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤ÎÆɤߤȤé¤ì¤¿¥Ç¥£¥¹¥¯¥Ö¥í¥Ã¥¯¿ô¤È¥Ð¥Ã¥Õ¥¡¥Ò¥Ã¥È¿ô¡£ |
| pg_statio_sys_indexes | ¥·¥¹¥Æ¥à¥Æ¡¼¥Ö¥ë¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤Î¤ß¤¬É½¼¨¤µ¤ì¤ëÅÀ¤ò½ü¤¡¢pg_statio_all_indexes ¤ÈƱ¤¸¤Ç¤¹¡£ |
| pg_statio_user_indexes | ¥æ¡¼¥¶¥Æ¡¼¥Ö¥ë¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤Î¤ß¤¬É½¼¨¤µ¤ì¤ëÅÀ¤ò½ü¤¡¢pg_statio_all_indexes ¤ÈƱ¤¸¤Ç¤¹¡£ |
| pg_statio_all_sequences | ¸½ºß¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Î³Æ¥·¡¼¥±¥ó¥¹¥ª¥Ö¥¸¥§¥¯¥È¤Ë´Ø¤¹¤ë¡¢¤½¤Î¥·¡¼¥±¥ó¥¹¤ÎÆɤߤȤé¤ì¤¿¥Ç¥£¥¹¥¯¥Ö¥í¥Ã¥¯¿ô¤È¥Ð¥Ã¥Õ¥¡¥Ò¥Ã¥È¿ô¡£ |
| pg_statio_sys_sequences | ¥·¥¹¥Æ¥à¥·¡¼¥±¥ó¥¹¤Î¤ß¤¬É½¼¨¤µ¤ì¤ëÅÀ¤ò½ü¤¡¢pg_statio_all_sequences ¤ÈƱ¤¸¤Ç¤¹¡£ (¸½»þÅÀ¤Ç¤Ï¡¢¥·¥¹¥Æ¥à¥·¡¼¥±¥ó¥¹¤ÏÄêµÁ¤µ¤ì¤Æ¤¤¤Þ¤»¤ó¤Î¤Ç¡¢¤³¤Î¥Ó¥å¡¼¤Ï¾ï¤Ë¶õ¤Ç¤¹¡£) |
| pg_statio_user_sequences | ¥æ¡¼¥¶¥·¡¼¥±¥ó¥¹¤Î¤ß¤¬É½¼¨¤µ¤ì¤ëÅÀ¤ò½ü¤¡¢pg_statio_all_sequences ¤ÈƱ¤¸¤Ç¤¹¡£ |
¥¤¥ó¥Ç¥Ã¥¯¥¹Ã±°Ì¤ÎÅý·×¾ðÊó¤Ï¡¢¤É¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤¬»ÈÍѤµ¤ì¡¢¤É¤ÎÄøÅÙ¸ú²Ì¤¬¤¢¤ë¤Î¤«¤òɾ²Á¤¹¤ëºÝ¤Ë¡¢ÆäËÍÍѤǤ¹¡£
pg_statio_ ¥Ó¥å¡¼¤Ï¼ç¤Ë¡¢¥Ð¥Ã¥Õ¥¡¥¥ã¥Ã¥·¥å¤Î¸úΨ¤òɾ²Á¤¹¤ëºÝ¤ËÍÍѤǤ¹¡£ ¼Â¥Ç¥£¥¹¥¯ÆɤߤȤê¤Î¿ô¤¬¥Ð¥Ã¥Õ¥¡¥Ò¥Ã¥È¤Î¿ô¤è¤ê¤â¤«¤Ê€¾¯¤Ê¤¤¤Î¤Ç¤¢¤ì¤Ð¡¢¤½¤Î¥¥ã¥Ã¥·¥å¤Ï¥«¡¼¥Í¥ë¸Æ¤Ó½Ð¤·¤ò¹Ô¤Ê¤¦¤³¤È¤Ê¤¯¡¢¤Û¤È¤ó¤É¤ÎÆɤߤȤêÍ×µá¤òËþ¤µ¤»¤Æ¤¤¤Þ¤¹¡£ ¤·¤«¤·¡¢PostgreSQL ¥Ð¥Ã¥Õ¥¡¥¥ã¥Ã¥·¥å¤Ë¸ºß¤·¤Ê¤¤¥Ç¡¼¥¿¤Ï¥«¡¼¥Í¥ë¤ÎI/O¥¥ã¥Ã¥·¥å¤Ë¤¢¤ë²ÄǽÀ¤¬¤¢¤ê¡¢¤½¤Î¤¿¤á¡¢ÊªÍýŪ¤ÊÆɤߤȤê¤ò¹Ô¤Ê¤¦¤³¤È¤Ê¤¯¼è¤ê½Ð¤µ¤ì¤ë²ÄǽÀ¤¬¤¢¤ë¤È¤¤¤¦PostgreSQL¤Î¥Ç¥£¥¹¥¯I/O¤Î¼è°·¤¤¤Î¤¿¤á¡¢¤³¤ì¤é¤ÎÅý·×¾ðÊó¤Ï¡¢´°Á´¤ÊÏÀµò¤òÄ󶡤·¤Þ¤»¤ó¡£ PostgreSQL I/OÆ°ºî¤Ë´Ø¤¹¤ë¤è¤ê¾ÜºÙ¤Ê¾ðÊó¤òÆþ¼ê¤·¤¿¤¤¤Î¤Ç¤¢¤ì¤Ð¡¢ PostgreSQLÅý·×¾ðÊó¼ý½¸´ï¤È¥«¡¼¥Í¥ë¤ÎI/O¤Î¼è°·¤¤¤Î´Æ»ë¤ò¹Ô¤Ê¤¦¥ª¥Ú¥ì¡¼¥Æ¥£¥ó¥°¥·¥¹¥Æ¥à¥æ¡¼¥Æ¥£¥ê¥Æ¥£¤òÁȤ߹ç¤ï¤»¤ë¤³¤È¤ò´«¤á¤Þ¤¹¡£
Åý·×¾ðÊó¤ò»²¾È¤¹¤ë¾¤ÎÊýË¡¤Ï¡¢¾å½Ò¤Îɸ½à¥Ó¥å¡¼Æ±Íͤˡ¢´ðÁÃŪ¤ÊÅý·×¾ðÊó¥¢¥¯¥»¥¹´Ø¿ô¤ò»ÈÍѤ·¤¿Ì䤤¹ç¤ï¤»¤òºîÀ®¤¹¤ë¤³¤È¤ÇÀßÄꤹ¤ë¤³¤È¤¬¤Ç¤¤Þ¤¹¡£ ¤³¤ì¤é¤Î´Ø¿ô¤Ï ɽ23-2 ¤Ë¥ê¥¹¥È¤µ¤ì¤Æ¤¤¤Þ¤¹¡£ ¥Ç¡¼¥¿¥Ù¡¼¥¹Ëè¤Î¥¢¥¯¥»¥¹´Ø¿ô¤Ï¡¢¤É¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹¤ËÂФ·¤ÆÊó¹ð¤¹¤ë¤Î¤«¤ò¼±Ê̤¹¤ë¤¿¤á¤Ë¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î OID ¤ò¼õ¤±ÉÕ¤±¤Þ¤¹¡£ ¥Æ¡¼¥Ö¥ëËè¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹Ëè¤Î´Ø¿ô¤Ï¥Æ¡¼¥Ö¥ë¤Î¡¢¤â¤·¤¯¤Ï¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¤Î OID ¤ò¼õ¤±ÉÕ¤±¤Þ¤¹¡£ (¤³¤Î´Ø¿ô¤ò»ÈÍѤ·¤Æ»²¾È¤Ç¤¤ë¥Æ¡¼¥Ö¥ë¤È¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ï¸½ºß¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Î¤â¤Î¤À¤±¤Ç¤¢¤ë¤³¤È¤ËÃí°Õ¤·¤Æ²¼¤µ¤¤¡£) ¥Ð¥Ã¥¯¥¨¥ó¥É¥×¥í¥»¥¹Ëè¤Î¥¢¥¯¥»¥¹´Ø¿ô¤Ï¥Ð¥Ã¥¯¥¨¥ó¥É¥×¥í¥»¥¹ ID ÈÖ¹æ¤ò¼õ¤±ÉÕ¤±¤Þ¤¹¡£ ¤È¤êÆÀ¤ëÈÏ°Ï¤Ï 1 ¤«¤é¸½»þÅÀ¤Ç³èÆ°Ãæ¤Î¥Ð¥Ã¥¯¥¨¥ó¥É¥×¥í¥»¥¹¤Î¿ô¤Þ¤Ç¤Ç¤¹¡£
ɽ 23-2. Åý·×¾ðÊó¥¢¥¯¥»¥¹´Ø¿ô
| ´Ø¿ô | Ìá¤êÃͤη¿ | ÀâÌÀ |
|---|---|---|
| pg_stat_get_db_numbackends(oid) | integer | ¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Ç³èÆ°Ãæ¤Î¥Ð¥Ã¥¯¥¨¥ó¥É¿ô¡£ |
| pg_stat_get_db_xact_commit(oid) | bigint | ¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Ç¥³¥ß¥Ã¥È¤µ¤ì¤¿¥È¥é¥ó¥¶¥¯¥·¥ç¥ó¡£ |
| pg_stat_get_db_xact_rollback(oid) | bigint | ¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Ç¥í¡¼¥ë¥Ð¥Ã¥¯¤µ¤ì¤¿¥È¥é¥ó¥¶¥¯¥·¥ç¥ó¡£ |
| pg_stat_get_db_blocks_fetched(oid) | bigint | ¥Ç¡¼¥¿¥Ù¡¼¥¹¤Ë´Ø¤¹¤ë¡¢¥Ç¥£¥¹¥¯¥Ö¥í¥Ã¥¯Ãê½ÐÍ×µá¿ô¡£ |
| pg_stat_get_db_blocks_hit(oid) | bigint | ¥Ç¡¼¥¿¥Ù¡¼¥¹¤Ë´Ø¤¹¤ë¡¢¥Ç¥£¥¹¥¯¥Ö¥í¥Ã¥¯Í×µá¤ÎÆ⥥ã¥Ã¥·¥å¤Ë¸ºß¤·¤¿¿ô¡£ |
| pg_stat_get_numscans(oid) | bigint | °ú¿ô¤¬¥Æ¡¼¥Ö¥ë¤Î¾ì¹ç¡¢¥·¡¼¥±¥ó¥·¥ã¥ë¥¹¥¥ã¥ó¤Î¼Â¹Ô²ó¿ô¡£ °ú¿ô¤¬¥¤¥ó¥Ç¥Ã¥¯¥¹¤Î¾ì¹ç¥¤¥ó¥Ç¥Ã¥¯¥¹¥¹¥¥ã¥ó¤Î¼Â¹Ô²ó¿ô¡£ |
| pg_stat_get_tuples_returned(oid) | bigint | °ú¿ô¤¬¥Æ¡¼¥Ö¥ë¤Î¾ì¹ç¡¢¥·¡¼¥±¥ó¥·¥ã¥ë¥¹¥¥ã¥ó¤Ë¤è¤Ã¤ÆÆɤߤȤé¤ì¤¿¹Ô¿ô¡£ °ú¿ô¤¬¥¤¥ó¥Ç¥Ã¥¯¥¹¤Î¾ì¹ç¡¢ÆɤߤȤé¤ì¤¿¥¤¥ó¥Ç¥Ã¥¯¥¹¹Ô¿ô¡£ |
| pg_stat_get_tuples_fetched(oid) | bigint | °ú¿ô¤¬¥Æ¡¼¥Ö¥ë¤Î¾ì¹ç¡¢¥·¡¼¥±¥ó¥·¥ã¥ë¥¹¥¥ã¥ó¤ÇÃê½Ð¤µ¤ì¤¿¡¢Í¸ú¤Ê(ÇË´þ¤µ¤ì¤Æ¤¤¤Ê¤¤)¥Æ¡¼¥Ö¥ë¹Ô¿ô¡£ °ú¿ô¤¬¥¤¥ó¥Ç¥Ã¥¯¥¹¤Î¾ì¹ç¡¢¤³¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤ò»ÈÍѤ·¤¿¥¤¥ó¥Ç¥Ã¥¯¥¹¥¹¥¥ã¥ó¤ÇÃê½Ð¤µ¤ì¤¿¡¢Í¸ú¤Ê(ÇË´þ¤µ¤ì¤Æ¤¤¤Ê¤¤)¥Æ¡¼¥Ö¥ë¹Ô¿ô¡£ |
| pg_stat_get_tuples_inserted(oid) | bigint | ¥Æ¡¼¥Ö¥ë¤ËÁÞÆþ¤µ¤ì¤¿¹Ô¿ô¡£ |
| pg_stat_get_tuples_updated(oid) | bigint | ¥Æ¡¼¥Ö¥ë¤Ç¹¹¿·¤µ¤ì¤¿¹Ô¿ô¡£ |
| pg_stat_get_tuples_deleted(oid) | bigint | ¥Æ¡¼¥Ö¥ë¤Çºï½ü¤µ¤ì¤¿¹Ô¿ô¡£ |
| pg_stat_get_blocks_fetched(oid) | bigint | ¥Æ¡¼¥Ö¥ë¡¢¤Þ¤¿¤Ï¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ë´Ø¤¹¤ë¡¢¥Ç¥£¥¹¥¯¥Ö¥í¥Ã¥¯Ãê½ÐÍ×µá¿ô¡£ |
| pg_stat_get_blocks_hit(oid) | bigint | ¥Æ¡¼¥Ö¥ë¡¢¤Þ¤¿¤Ï¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ë´Ø¤¹¤ë¡¢¥Ç¥£¥¹¥¯¥Ö¥í¥Ã¥¯Ãê½ÐÍ×µá¤ÎÆ⥥ã¥Ã¥·¥åÆâ¤Ë¸ºß¤·¤¿¿ô¡£ |
| pg_stat_get_backend_idset() | set of integer | ¸½ºß³èÆ°Ãæ¤Î¥Ð¥Ã¥¯¥¨¥ó¥É ID ¤ò(1¤«¤é³èÆ°Ãæ¥Ð¥Ã¥¯¥¨¥ó¥É¥×¥í¥»¥¹¿ô¤Þ¤Ç¤Î´Ö¤Ç)ÀßÄꤷ¤Þ¤¹¡£ °Ê²¼¤Î»ÈÍÑÎã¤ò»²¾È¤·¤Æ²¼¤µ¤¤¡£ |
| pg_backend_pid() | integer | ¸½ºß¤Î¥»¥Ã¥·¥ç¥ó¤ËÀܳ¤¹¤ë¥Ð¥Ã¥¯¥¨¥ó¥É¥×¥í¥»¥¹¤Î¥×¥í¥»¥¹ ID¡£ |
| pg_stat_get_backend_pid(integer) | integer | »ØÄꤵ¤ì¤¿¥Ð¥Ã¥¯¥¨¥ó¥É¥×¥í¥»¥¹¤Î¥×¥í¥»¥¹ ID¡£ |
| pg_stat_get_backend_dbid(integer) | oid | »ØÄꤵ¤ì¤¿¥Ð¥Ã¥¯¥¨¥ó¥É¥×¥í¥»¥¹¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹ ID¡£ |
| pg_stat_get_backend_userid(integer) | oid | »ØÄꤵ¤ì¤¿¥Ð¥Ã¥¯¥¨¥ó¥É¥×¥í¥»¥¹¤Î¥æ¡¼¥¶ ID¡£ |
| pg_stat_get_backend_activity(integer) | text | ¥Ð¥Ã¥¯¥¨¥ó¥É¥×¥í¥»¥¹¤Î¸½ºß¤ÎÌ䤤¹ç¤ï¤»¡£ (¸½ºß¤Î¥æ¡¼¥¶¤¬¥¹¡¼¥Ñ¥æ¡¼¥¶¤Ç¤Ï¤Ê¤¤¾ì¹ç¤äÌ䤤¹ç¤ï¤»À襻¥Ã¥·¥ç¥ó¤Î¥æ¡¼¥¶¤È°Û¤Ê¤ë¾ì¹ç¡¢stats_command_string¤¬Ìµ¸ú¤Ê¾ì¹ç¤Ï NULL¡£) |
| pg_stat_get_backend_activity_start(integer) | timestamp with time zone | »ØÄꤵ¤ì¤¿¥Ð¥Ã¥¯¥¨¥ó¥É¥×¥í¥»¥¹¤¬¼Â¹ÔÃæ¤ÎÌ䤤¹ç¤ï¤»¤¬³«»Ï¤·¤¿»þ¹ï¡£ (¸½ºß¤Î¥æ¡¼¥¶¤¬¥¹¡¼¥Ñ¥æ¡¼¥¶¤Ç¤Ï¤Ê¤¤¾ì¹ç¤äÌ䤤¹ç¤ï¤»À襻¥Ã¥·¥ç¥ó¤Î¥æ¡¼¥¶¤È°Û¤Ê¤ë¾ì¹ç¡¢stats_command_string¤¬Ìµ¸ú¤Ê¾ì¹ç¤Ï NULL¡£) |
| pg_stat_reset() | boolean | ¸½ºß¤Þ¤Ç¤Ë¼ý½¸¤µ¤ì¤¿Åý·×¾ðÊó¤ò¤¹¤Ù¤Æ¥ê¥»¥Ã¥È¡£ |
Ãí°Õ: pg_stat_get_db_blocks_fetched¤«¤épg_stat_get_db_blocks_hit¤ò°ú¤¯¤È¡¢¤½¤Î¥Æ¡¼¥Ö¥ë¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¡¢¥Ç¡¼¥¿¥Ù¡¼¥¹¤ËÂФ·¤Æȯ¹Ô¤µ¤ì¤¿¥«¡¼¥Í¥ë¤Îread()¥³¡¼¥ë¿ô¤¬¤ï¤«¤ê¤Þ¤¹¡£ ¤·¤«¤·¡¢¼ÂºÝ¤ÎʪÍýŪ¤ÊÆɤߤȤê¿ô¤Ï¡¢¥«¡¼¥Í¥ë¥ì¥Ù¥ë¤Î¥Ð¥Ã¥Õ¥¡½èÍý¤Î¤¿¤á¤ËÄ̾盧¤ì¤è¤ê¾®¤µ¤¯¤Ê¤ê¤Þ¤¹¡£
pg_stat_get_backend_idset ´Ø¿ô¤Ï¡¢³èÆ°Ãæ¥Ð¥Ã¥¯¥¨¥ó¥É¥×¥í¥»¥¹¤½¤ì¤¾¤ì¤Ë¤Ä¤¤¤Æ1¹Ô¤òºîÀ®¤¹¤ë´ÊÊؤÊÊýË¡¤òÄ󶡤·¤Þ¤¹¡£ Î㤨¤Ð¡¢Á´¤Æ¤Î¥Ð¥Ã¥¯¥¨¥ó¥É¥×¥í¥»¥¹¤Î PID ¤È¸½ºß¤ÎÌ䤤¹ç¤ï¤»¤òɽ¼¨¤¹¤ë¤Ë¤Ï¡¢°Ê²¼¤ò¹Ô¤¤¤Þ¤¹¡£
SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
| Á°¤Î¥Ú¡¼¥¸ | ¥Û¡¼¥à | ¼¡¤Î¥Ú¡¼¥¸ |
| ¥Ç¡¼¥¿¥Ù¡¼¥¹³èÆ°¾õ¶·¤Î´Æ»ë | ¾å¤ËÌá¤ë | ¥í¥Ã¥¯¤Îɽ¼¨ |