<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="performance-schema-connection-tables">
      </a>
      29.12.8 Performance Schema Connection Tables
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="performance-schema-accounts-table.html">
       29.12.8.1 The accounts Table
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="performance-schema-hosts-table.html">
       29.12.8.2 The hosts Table
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="performance-schema-users-table.html">
       29.12.8.3 The users Table
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <p>
   When a client connects to the MySQL server, it does so under a
        particular user name and from a particular host. The Performance
        Schema provides statistics about these connections, tracking
        them per account (user and host combination) as well as
        separately per user name and host name, using these tables:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="performance-schema-accounts-table.html" title="29.12.8.1 The accounts Table">
       <code class="literal">
        accounts
       </code>
      </a>
      : Connection statistics
            per client account
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="performance-schema-hosts-table.html" title="29.12.8.2 The hosts Table">
       <code class="literal">
        hosts
       </code>
      </a>
      : Connection statistics
            per client host name
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="performance-schema-users-table.html" title="29.12.8.3 The users Table">
       <code class="literal">
        users
       </code>
      </a>
      : Connection statistics
            per client user name
     </p>
    </li>
   </ul>
  </div>
  <p>
   The meaning of
   <span class="quote">
    “
    <span class="quote">
     account
    </span>
    ”
   </span>
   in the connection tables
        is similar to its meaning in the MySQL grant tables in the
   <code class="literal">
    mysql
   </code>
   system database, in the sense that the
        term refers to a combination of user and host values. They
        differ in that, for grant tables, the host part of an account
        can be a pattern, whereas for Performance Schema tables, the
        host value is always a specific nonpattern host name.
  </p>
  <p>
   Each connection table has
   <code class="literal">
    CURRENT_CONNECTIONS
   </code>
   and
   <code class="literal">
    TOTAL_CONNECTIONS
   </code>
   columns to track the
        current and total number of connections per
   <span class="quote">
    “
    <span class="quote">
     tracking
        value
    </span>
    ”
   </span>
   on which its statistics are based. The tables
        differ in what they use for the tracking value. The
   <a class="link" href="performance-schema-accounts-table.html" title="29.12.8.1 The accounts Table">
    <code class="literal">
     accounts
    </code>
   </a>
   table has
   <code class="literal">
    USER
   </code>
   and
   <code class="literal">
    HOST
   </code>
   columns to
        track connections per user and host combination. The
   <a class="link" href="performance-schema-users-table.html" title="29.12.8.3 The users Table">
    <code class="literal">
     users
    </code>
   </a>
   and
   <a class="link" href="performance-schema-hosts-table.html" title="29.12.8.2 The hosts Table">
    <code class="literal">
     hosts
    </code>
   </a>
   tables have a
   <code class="literal">
    USER
   </code>
   and
   <code class="literal">
    HOST
   </code>
   column,
        respectively, to track connections per user name and host name.
  </p>
  <p>
   The Performance Schema also counts internal threads and threads
        for user sessions that failed to authenticate, using rows with
   <code class="literal">
    USER
   </code>
   and
   <code class="literal">
    HOST
   </code>
   column
        values of
   <code class="literal">
    NULL
   </code>
   .
  </p>
  <p>
   Suppose that clients named
   <code class="literal">
    user1
   </code>
   and
   <code class="literal">
    user2
   </code>
   each connect one time from
   <code class="literal">
    hosta
   </code>
   and
   <code class="literal">
    hostb
   </code>
   . The
        Performance Schema tracks the connections as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <a class="link" href="performance-schema-accounts-table.html" title="29.12.8.1 The accounts Table">
       <code class="literal">
        accounts
       </code>
      </a>
      table has four
            rows, for the
      <code class="literal">
       user1
      </code>
      /
      <code class="literal">
       hosta
      </code>
      ,
      <code class="literal">
       user1
      </code>
      /
      <code class="literal">
       hostb
      </code>
      ,
      <code class="literal">
       user2
      </code>
      /
      <code class="literal">
       hosta
      </code>
      , and
      <code class="literal">
       user2
      </code>
      /
      <code class="literal">
       hostb
      </code>
      account
            values, each row counting one connection per account.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="performance-schema-hosts-table.html" title="29.12.8.2 The hosts Table">
       <code class="literal">
        hosts
       </code>
      </a>
      table has two rows,
            for
      <code class="literal">
       hosta
      </code>
      and
      <code class="literal">
       hostb
      </code>
      ,
            each row counting two connections per host name.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="performance-schema-users-table.html" title="29.12.8.3 The users Table">
       <code class="literal">
        users
       </code>
      </a>
      table has two rows,
            for
      <code class="literal">
       user1
      </code>
      and
      <code class="literal">
       user2
      </code>
      ,
            each row counting two connections per user name.
     </p>
    </li>
   </ul>
  </div>
  <p>
   When a client connects, the Performance Schema determines which
        row in each connection table applies, using the tracking value
        appropriate to each table. If there is no such row, one is
        added. Then the Performance Schema increments by one the
   <code class="literal">
    CURRENT_CONNECTIONS
   </code>
   and
   <code class="literal">
    TOTAL_CONNECTIONS
   </code>
   columns in that row.
  </p>
  <p>
   When a client disconnects, the Performance Schema decrements by
        one the
   <code class="literal">
    CURRENT_CONNECTIONS
   </code>
   column in the row
        and leaves the
   <code class="literal">
    TOTAL_CONNECTIONS
   </code>
   column
        unchanged.
  </p>
  <p>
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   is permitted for
        connection tables. It has these effects:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Rows are removed for accounts, hosts, or users that have no
            current connections (rows with
      <code class="literal">
       CURRENT_CONNECTIONS
            = 0
      </code>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      Nonremoved rows are reset to count only current connections:
            For rows with
      <code class="literal">
       CURRENT_CONNECTIONS &gt; 0
      </code>
      ,
      <code class="literal">
       TOTAL_CONNECTIONS
      </code>
      is reset to
      <code class="literal">
       CURRENT_CONNECTIONS
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Summary tables that depend on the connection table are
            implicitly truncated, as described later in this section.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The Performance Schema maintains summary tables that aggregate
        connection statistics for various event types by account, host,
        or user. These tables have
   <code class="literal">
    _summary_by_account
   </code>
   ,
   <code class="literal">
    _summary_by_host
   </code>
   , or
   <code class="literal">
    _summary_by_user
   </code>
   in the name. To identify
        them, use this query:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa21263555"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token keyword">TABLE_NAME</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">TABLES</span>
       <span class="token keyword">WHERE</span> TABLE_SCHEMA <span class="token operator">=</span> <span class="token string">'performance_schema'</span>
       <span class="token operator">AND</span> <span class="token keyword">TABLE_NAME</span> <span class="token operator">REGEXP</span> <span class="token string">'_summary_by_(account|host|user)'</span>
       <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token keyword">TABLE_NAME</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> TABLE_NAME                                           <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> events_errors_summary_by_account_by_error            <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_errors_summary_by_host_by_error               <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_errors_summary_by_user_by_error               <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_stages_summary_by_account_by_event_name       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_stages_summary_by_host_by_event_name          <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_stages_summary_by_user_by_event_name          <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_statements_summary_by_account_by_event_name   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_statements_summary_by_host_by_event_name      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_statements_summary_by_user_by_event_name      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_transactions_summary_by_account_by_event_name <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_transactions_summary_by_host_by_event_name    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_transactions_summary_by_user_by_event_name    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_waits_summary_by_account_by_event_name        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_waits_summary_by_host_by_event_name           <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> events_waits_summary_by_user_by_event_name           <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> memory_summary_by_account_by_event_name              <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> memory_summary_by_host_by_event_name                 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> memory_summary_by_user_by_event_name                 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   For details about individual connection summary tables, consult
        the section that describes tables for the summarized event type:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Wait event summaries:
      <a class="xref" href="performance-schema-wait-summary-tables.html" title="29.12.20.1 Wait Event Summary Tables">
       Section 29.12.20.1, “Wait Event Summary Tables”
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      Stage event summaries:
      <a class="xref" href="performance-schema-stage-summary-tables.html" title="29.12.20.2 Stage Summary Tables">
       Section 29.12.20.2, “Stage Summary Tables”
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      Statement event summaries:
      <a class="xref" href="performance-schema-statement-summary-tables.html" title="29.12.20.3 Statement Summary Tables">
       Section 29.12.20.3, “Statement Summary Tables”
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      Transaction event summaries:
      <a class="xref" href="performance-schema-transaction-summary-tables.html" title="29.12.20.5 Transaction Summary Tables">
       Section 29.12.20.5, “Transaction Summary Tables”
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      Memory event summaries:
      <a class="xref" href="performance-schema-memory-summary-tables.html" title="29.12.20.10 Memory Summary Tables">
       Section 29.12.20.10, “Memory Summary Tables”
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      Error event summaries:
      <a class="xref" href="performance-schema-error-summary-tables.html" title="29.12.20.11 Error Summary Tables">
       Section 29.12.20.11, “Error Summary Tables”
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   is permitted for
        connection summary tables. It removes rows for accounts, hosts,
        or users with no connections, and resets the summary columns to
        zero for the remaining rows. In addition, each summary table
        that is aggregated by account, host, user, or thread is
        implicitly truncated by truncation of the connection table on
        which it depends. The following table describes the relationship
        between connection table truncation and implicitly truncated
        tables.
  </p>
  <div class="table">
   <a name="idm46045072120800">
   </a>
   <p class="title">
    <b>
     Table 29.2 Implicit Effects of Connection Table Truncation
    </b>
   </p>
   <div class="table-contents">
    <table summary="Which Performance Schema summary tables are implicity truncated by connection table truncation.">
     <colgroup>
      <col style="width: 40%"/>
      <col style="width: 60%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Truncated Connection Table
       </th>
       <th>
        Implicitly Truncated Summary Tables
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        <code class="literal">
         accounts
        </code>
       </td>
       <td>
        Tables with names containing
        <code class="literal">
         _summary_by_account
        </code>
        ,
        <code class="literal">
         _summary_by_thread
        </code>
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         hosts
        </code>
       </td>
       <td>
        Tables with names containing
        <code class="literal">
         _summary_by_account
        </code>
        ,
        <code class="literal">
         _summary_by_host
        </code>
        ,
        <code class="literal">
         _summary_by_thread
        </code>
       </td>
      </tr>
      <tr>
       <td>
        <code class="literal">
         users
        </code>
       </td>
       <td>
        Tables with names containing
        <code class="literal">
         _summary_by_account
        </code>
        ,
        <code class="literal">
         _summary_by_user
        </code>
        ,
        <code class="literal">
         _summary_by_thread
        </code>
       </td>
      </tr>
     </tbody>
    </table>
   </div>
  </div>
  <br class="table-break"/>
  <p>
   Truncating a
   <code class="literal">
    _summary_global
   </code>
   summary table
        also implicitly truncates its corresponding connection and
        thread summary tables. For example, truncating
   <a class="link" href="performance-schema-wait-summary-tables.html" title="29.12.20.1 Wait Event Summary Tables">
    <code class="literal">
     events_waits_summary_global_by_event_name
    </code>
   </a>
   implicitly truncates the wait event summary tables that are
        aggregated by account, host, user, or thread.
  </p>
 </div>
 <br/>
</div>
