ourdelta-developers team mailing list archive

[Arjen Lentz <arjen@xxxxxxxxxxxxxxxx>]

Hi Mark,

On 06/03/2009, at 1:15 AM, MARK CALLAGHAN wrote:
> Well, I fixed this and then I removed all code for SHOW  
> CLIENT_STATISTICS from the next patch. We were not using it enough  
> to justify the cost of having that code there. SHOW INDEX_STATISTICS  
> has been removed as well. SHOW TABLE_STATS and SHOW USER_STATS  
> remain with changes to fix the mutex contention that they used to  
> create. I don't know what Percona will do with that. They may be  
> bigger fans of SHOW INDEX_STATS than I was.


INDEX_STATISTICS allows us to find unused indexes, and that's very  
valuable.
Don't care for the SHOW commands as such, in INFORMATION_SCHEMA  
they're much more useful as they can be joined onto other tables to do  
the unused-index detection.

Don't think OurDelta will be removing that, it's specifically why we  
included it.


Cheers,
Arjen.
-- 
Arjen Lentz, Director @ Open Query (http://openquery.com)
Affordable Training and Pro-Active Support for MySQL & related  
technologies

My blog is at http://arjen-lentz.livejournal.com
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org

-- 
CLIENT_STATISTICS are broken if hostname is > 16 chars
https://bugs.launchpad.net/bugs/338012
You received this bug notification because you are a member of OurDelta-
developers, which is the registrant for OurDelta.

Status in OurDelta - Builds for MySQL: New

Bug description:

I noticed today that the CLIENT_STATISTICS tables were not working properly if the hostnames are longer
than 16 characters.  I am running these packages from the ourdelta repository (centos5.2):

MySQL-OurDelta-client.x86_64             5.0.67.d7-44.el5_2     installed
MySQL-OurDelta-server.x86_64             5.0.67.d7-44.el5_2     installed
MySQL-OurDelta-shared.x86_64             5.0.67.d7-44.el5_2     installed

mysql> select count(*) from client_statistics;
+----------+
| count(*) |
+----------+
|  1485697 |
+----------+
1 row in set (1.62 sec)

mysql> select client from client_statistics limit 20;
+------------------+
| client           |
+------------------+
| gonzo17i.bunchba |
|                  |
| gonzo23i.bunchba |
| gonzo20i.bunchba |
| gonzo20i.bunchba |
| gonzo23i.bunchba |
| gonzo20i.bunchba |
| gonzo20i.bunchba |
| gonzo17i.bunchba |
| gonzo17i.bunchba |
| gonzo20i.bunchba |
| gonzo23i.bunchba |
| gonzo20i.bunchba |
| gonzo17i.bunchba |
| gonzo23i.bunchba |
| gonzo20i.bunchba |
| gonzo17i.bunchba |
| gonzo20i.bunchba |
| gonzo15i.bunchba |
| gonzo17i.bunchba |
+------------------+
20 rows in set (1.36 sec)


As you can see, there are lots of duplicate rows.  The heart of the problem seems to be this
code from the google patch:

ourdelta-mybranch/mysql/5.0/google/userstatsv2/userstatsv2-main.patch.

Client name lengths are set to LIST_PROCESS_HOST_LEN (64), but the CLIENT column is set to 16 characters.
This means that there will be rows in client stats table that will be distinct, but indistinguishable in the show client_statistics output.  The CLIENT column should probably have the same length (its set in this line:

+  {"CLIENT", 16, MYSQL_TYPE_STRING, 0, 0, "Client"},

But the underlying problem seems to be in a different place.

There is a routine to initialize the statistics:

+void init_global_client_stats(void)
+{
+  if (hash_init(&global_client_stats, system_charset_info, max_connections,
+                0, 0, (hash_get_key)get_key_user_stats,
+                (hash_free_key)free_user_stats, 0)) {
+    sql_print_error("Initializing global_client_stats failed.");
+    exit(1);
+  }
+}

The get_key_user_stats is shared between the global_user_stats and global_client_stats hash tables.
Here is the definition of that function:

+byte *get_key_user_stats(USER_STATS *user_stats, uint *length,
+                         my_bool not_used __attribute__((unused)))
+{
+  *length = strlen(user_stats->user);
+  return (byte*)user_stats->user;
+}

The "user_name" field is being shared between user and client statistics.  This field is limited to 16 characters.  But when a hash match is being looked for, we are comparing against a 64 char host name, and we are always inserting a new row.