On Thu, Mar 5, 2009 at 1:06 PM, Arjen Lentz <[email protected]> wrote: > 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. >
Then you have inherited a mutex contention performance bug. I fixed it by removing the feature. There are other ways to fix it that require more code changes. But it needs to be fixed. > > 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. > > _______________________________________________ > Mailing list: https://launchpad.net/~ourdelta-developers > Post to : [email protected] > Unsubscribe : https://launchpad.net/~ourdelta-developers > More help : https://help.launchpad.net/ListHelp > -- Mark Callaghan [email protected] _______________________________________________ Mailing list: https://launchpad.net/~ourdelta-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~ourdelta-developers More help : https://help.launchpad.net/ListHelp
