Provide per-thread memory usage statistics

[Laurynas Biveinis]

Original request and patch by Lixun Peng:

Hi Guys,

I always found my MySQL use many memory, even though Buffer Pool is small. So I think Server used many memory.
I developed a demo patch (see picture), it can display how many memory used of threads.
Expect this, I think how many sort_buffer/join_buffer/net_buffer memory used of threads are also need display, it will let DBAs know the memory cost detail.

Do you think this function is very useful?

[Raghavendra D Prabhu (raghavendra-prabhu)]

After thread based profiling has been introduced in Percona Server 5.5 (http://www.percona.com/doc/percona-server/5.5/diagnostics/thread_based_profiling.html), it already collects the following statistics per thread:

long ru_maxrss; /* maximum resident set size */
long ru_ixrss; /* integral shared memory size */
long ru_idrss; /* integral unshared data size */
long ru_isrss; /* integral unshared stack size */

but they are not added to the I_S.profiling table, so they are not available.

However, per-thread page faults (major/minor), swap statistics are still available.

I guess rest can be added to the table as well.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Seems only ru_maxrss makes any sense on Linux (BSD has other fields as well), so there isn't much except max RSS watermark.

>>bzr diff ./Percona-Server/sql/sql_profile.cc
=== modified file 'Percona-Server/sql/sql_profile.cc'

--- Percona-Server/sql/sql_profile.cc 2012-08-07 06:10:00 +0000
+++ Percona-Server/sql/sql_profile.cc 2012-08-30 15:29:31 +0000
@@ -77,6 +77,7 @@
   {"SOURCE_FUNCTION", 30, MYSQL_TYPE_STRING, 0, true, "Source_function", SKIP_OPEN_TABLE},
   {"SOURCE_FILE", 20, MYSQL_TYPE_STRING, 0, true, "Source_file", SKIP_OPEN_TABLE},
   {"SOURCE_LINE", 20, MYSQL_TYPE_LONG, 0, true, "Source_line", SKIP_OPEN_TABLE},
+ {"MAXIMUM RESIDENT SET SIZE", 20, MYSQL_TYPE_LONG, 0, true, "Maximum_Resident_Set_Size", SKIP_OPEN_TABLE},
   {NULL, 0, MYSQL_TYPE_STRING, 0, true, NULL, 0}
 };

@@ -103,6 +104,7 @@
     profile_options & PROFILE_SOURCE, /* Source_function */
     profile_options & PROFILE_SOURCE, /* Source_file */
     profile_options & PROFILE_SOURCE, /* Source_line */
+ profile_options & PROFILE_MEMORY, /* MEMORY_system */
   };

   ST_FIELD_INFO *field_info;
@@ -839,6 +841,13 @@
         table->field[17]->store(previous->line, true);
         table->field[17]->set_notnull();
       }
+#ifdef HAVE_GETRUSAGE
+ table->field[18]->store((uint32)(entry->rusage.ru_maxrss -
+ previous->rusage.ru_maxrss));
+ table->field[18]->set_notnull();
+#else
+ /* TODO: Add RSS info for non-BSD systems */
+#endif

       if (schema_table_store_record(thd_arg, table))
         DBUG_RETURN(1);

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2371