maria-developers team mailing list archive

[Michael Widenius <monty@xxxxxxxxxxxx>]

Hi!

>>>>> "MARK" == MARK CALLAGHAN <mdcallag@xxxxxxxxx> writes:

<cut>

MARK> I am willing to bet that Monty's patch makes sysbench readonly much
MARK> slower with high concurrency on an 8-core server. But those problems
MARK> have been fixed in recent versions of the patch.
>> 
>> One should note that I changed the patch so that there is minimal
>> overhead (in the order of 4 if) if the userstat is not enabled (ie,
>> the userstat variable is not set)

MARK> I should have run tests first. Fortunately I didn't bet a lot of
MARK> money. I will do that now (test v4 google patch, maria 5.2, 5.0.37
MARK> unmodified, 5.1.38 unmodified with 1.0.4 plugin) and confirm or deny
MARK> my claim.

I am sure that you are right that my version is slower when it's
enabled  (didn't know of your new version of the patch).

MARK> I measured approximately zero overhead from the SHOW TABLE_STATS
MARK> feature in the Facebook patch listed below using sysbench readonly on
MARK> an 8-core server.

Do you mean when the statistics is enabled or not ?

>> Where can I find that newer version of the patch or can you describe
>> what changes needs to be done ?

MARK> For SHOW TABLE_STATS, look at
MARK> http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/trunk/revision/53,
MARK> and also revisions 55 and 56

MARK> For SHOW USER_STATS see the big v4 google patch:
MARK> http://google-mysql-tools.googlecode.com/svn/trunk/mysql-patches/all.v4-mysql-5.0.37.patch.gz

Thanks. I will look at this during the next few days!

MARK> The key to making SHOW TABLE_STATS faster is:
MARK> * cache pointers into global_table_stats using handler::version_table_stats
MARK> * updating stats prior to getting LOCK_open when possible (see bzr
MARK> revision 53 above)
MARK> * remove code for SHOW INDEX_STATS, doing that efficiently requires
MARK> more changes than I am willing to maintain -- someone should fund that
MARK> work

The counting part is already solved in my patch.
(In other words, no need to do any changes in storage engines code)

MARK> The key to making SHOW USER_STATS faster is:
MARK> * cache pointers into global_user_stats using THD::thd_user_stats_version
MARK> * get rid of the code that computes concurrent connections per account

MARK> I want to review your changes to see whether I can reuse your
MARK> improvements for my code.

Feel free to do that! Other options:

a) Wait until I have looked at your changes and added them to MariaDB
b) Help with getting the above work done
c) After the aboce, consider to start using MariaDB to not have to
  maintain the patches anymore ;)

MARK> Both table and user stats would be cleaner were the stats stored in an
MARK> existing global user or table object. I think such an object exists
MARK> for user but not for table (the 'shared' table object isn't shared
MARK> yet).

For user it exists, which I use in my patch.

The shared table object is in 5.1 unique for all instances of an table
that is in the open table cache.  This could be used to store a
pointer to the table object (and not have to do hash lookups at all).

This would however not help to store information about tables that are
not anymore open.

One could probably fix this by having most things counters directly in
the share and have a hash for all tables that are not open.

This would make table statistics almost instant, with only a small
overhead when opening/closing a table.

Regards,
Monty