February 2011 - developers - lists.mariadb.org

[Maria-developers] Benchmarking mrr_sort_keys=on|off.
by Sergey Petrunya 02 Feb '11

02 Feb '11

Hi! Following the discussion of benchmarks of mrr_sort_keys=on|off functionality, I've done a re-run. Please find the results below. ## Dataset: DBT-3, scale=10, xtradb, ## innodb_file_per_table=1 ## innodb_file_format=barracuda ## innodb_log_file_size=256M ## innodb_buffer_pool_size=4000M ## ## Platform: work.askmonty.org host ## Cold-cache is achieved by: ## - stopping the server ## - echo 1 > /proc/sys/vm/drop_caches ## - starting the server ## - running EXPLAIN {query} ## ## MariaDB: 5.3-current, release build (compile-pentium-max) ## Result summary: * mrr_sort_keys=on provides speedup (2.25 min vs 13.5 min) on cold cache. * on hot cache, there is no big difference (~1.3 sec in both cases). * I was able to repeat Igor's August results with the current 5.3. It is not clear why Igor can't repeat them anymore. (An evasive bug? or just mistake in experiments?) set join_buffer_size=1024*1024*32; set join_buffer_space_limit=1024*1024*32; set join_cache_level=6; ## ## Cold cache, mrr_sort_keys=on: ## [22:12] root@dbt3sf10>explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31'; +----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+------------------------------------+ | 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 142680 | Using where; Using index | | 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 | dbt3sf10.orders.o_orderkey | 1 | Using join buffer (flat, BKA join) | +----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+------------------------------------+ 2 rows in set (3.04 sec) [22:06] root@dbt3sf10>select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31'; +----------------------+ | avg(l_extendedprice) | +----------------------+ | 38229.0683265894 | +----------------------+ 1 row in set (2 min 13.40 sec) [22:09] root@dbt3sf10> show status like "%handler%"; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 2 | | Handler_read_key | 193396 | | Handler_read_next | 966535 | +----------------------------+--------+ 15 rows in set (0.29 sec) # # mrr_sort_keys=off, cold cache: # [22:20] root@dbt3sf10>explain select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31'; +----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+--------------------------+ | 1 | SIMPLE | orders | range | PRIMARY,i_o_orderdate | i_o_orderdate | 4 | NULL | 142680 | Using where; Using index | | 1 | SIMPLE | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | PRIMARY | 4 | dbt3sf10.orders.o_orderkey | 2 | | +----+-------------+----------+-------+--------------------------------------------+---------------+---------+----------------------------+--------+--------------------------+ [22:20] root@dbt3sf10>select avg(l_extendedprice) from orders, lineitem where l_orderkey=o_orderkey and o_orderdate between date '1992-07-01' and date '1992-07-31'; +----------------------+ | avg(l_extendedprice) | +----------------------+ | 38229.0683265898 | +----------------------+ 1 row in set (13 min 23.68 sec) [22:09] root@dbt3sf10> show status like "%handler%"; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 2 | | Handler_read_key | 193396 | | Handler_read_next | 966535 | +----------------------------+--------+ ## ## Hot caches (re-run the query until time stabilizes): ## mrr_sort_keys=off: 1.34 sec mrr_sort_keys=on: 1.38 sec BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] Windows installer MWL#55 finished.
by Vladislav Vaintroub 02 Feb '11

02 Feb '11

From: Peter Laursen [mailto:peter_laursen@webyog.com] Sent: Mittwoch, 2. Februar 2011 17:14 To: Vladislav Vaintroub Cc: maria-developers(a)lists.launchpad.net Subject: Re: [Maria-developers] Windows installer MWL#55 finished. Hi Peter, >Your default install path is \program files [(x86)]. You copy the /datadir as a subfolder of /basedirr. >This will cause problems on Vista+ with UAC enabled if the server is ever started from commandline as a non-service (also if >the user doing so is an administrator user). For the user who installed MySQL, it won't cause problems. After you stop the service you can start mysqld --defaults-file=path\to\datadir\my.ini --console from the non-elevated command line, with/without UAC. No file system virtualization will happen. That bug http://bugs.mysql.com/bug.php?id=38373 (user can not from mysqld from command line after installation) was filed by myself 2.5 years ago , I could not leave that unfixed now. I know, writing to ProgramFiles might not be kosher, but it works if ACLs on our tree are set. (And Microsoft should not tell people that writing to program files is bad, they are doing it with SQLServer, and also document it http://msdn.microsoft.com/en-us/library/ms143547.aspx , so we are again as good/bad as SQLServer here) >2) There is no my.ini generated with a fresh install as far as I can see - so the server probably runs (it does!) with very low default buffer settings etc. Also latin1 charset and MyISAM will be default. I think a my.ini with settings fit for a 1GB-2GB RAM machine should be generated - and with a few configuration options. And a my.ini will be required to solve 1) anyway. There is a my.ini, it is in database directory. It is spartanic, it has port and datadir, nothing else. You can edit it using non-elevated notepad (with or without UAC) and there is a shortcut for it created in start menu, MariaDB 5.2 folder (rsp MariaDB 5.2 (x64) if for 64 bit package). There are zero performance related options, which means in fact it won't run on full speed. I have no strong opinion on it, nor I feel qualified for configuring "optimal performance out of the box" . If there is a strong demand on specific parameters, they should probably go there. I would like to hear more opinions on this, one thing that will definitely not be in the first incarnation of this installer is fully-featured ConfigWizard like stuff. Where I see differences between this my.ini and MySQL installer's default is * sql_mode (MySQL's default is "strict transactional tables") * default storage engine (MySQL default is innodb) * innodb buffer pool size (IIRC, MySQL's default is something like 12% of RAM) Those are trivial to change modifying my.ini Stuff like innodb log file size (calculated from innodb buffer pool size) not trivial to change with simple my.ini modification, there must be a clean shutdown and exiting files need to be removed. > But still it is a good installer (and there is a 64 bit one as well), so this is an important step in the right direction. So thanks for your effort! Thanks! -- Peter On Wed, Feb 2, 2011 at 14:37, Peter Laursen <peter_laursen(a)webyog.com> wrote: I am a little bit busy now, but will confirm tonight. -- Peter On Wed, Feb 2, 2011 at 14:12, Vladislav Vaintroub <wlad(a)montyprogram.com> wrote: From: Peter Laursen [mailto:peter_laursen@webyog.com] Sent: Dienstag, 1. Februar 2011 20:31 To: Vladislav Vaintroub Cc: maria-developers(a)lists.launchpad.net Subject: Re: [Maria-developers] Windows instller MWL#55 finished. >It is a Danish Windows (Home Premium 64 bit). Hi Peter, As it turns out, installer had the same "localization" problem registering service on German Windows. Fixed now, I uploaded the msis to the same place http://cid-ff0c950417b4f8a4.office.live.com/browse.aspx/share Thanks, Wlad

2 1

[Maria-developers] Windows installer MWL#55 finished.
by Vladislav Vaintroub 02 Feb '11

02 Feb '11

Hi all, I updated the MWL#55 (Windows installer) with the upgrade information. Now, people will be able to upgrade between prior MariaDB (or prior MySQL) versions using upgrade wizard, which is by default launched at the end of installation, if upgradable instances are found . Users will also be able to use Upgrade Wizard after the installation, at any the time convenient for them. Upgrade wizard will also be included into ZIP distribution. Besides GUI Wizard, there is a command line utility mysql_upgrade_service.exe to perform all operations necessary to upgrade an existing database instance registered as service (in fact GUI wizard uses this mysql_upgrade_service.exe as "backend"). Wizard is an MFC application. I'm sorry for that, but my Windows C/C++ GUI programming experience is not only rusty (prior to this WL last time I used it was more than a decade ago), it is also restricted to MFC only. Being MFC application, it will require at least VS Professional to build. There is no MFC in free Visual Studio Express edition, nor in free Windows SDK. Build and packaging process will handle missing MFC gracefully (build won't try to compile upgrade wizard, package won't include it , and installer won't start it at the end of installation). The repository that includes the final windows installer is lp:~maria-captains/maria/5.2-windows-packaging-upgrade Building installer is described in http://askmonty.org/worklog/Server-Sprint/?tid=55 (Section "Building installer", at the very start of the WL). Monty or Sergei (or volunteers if any): Can you please have a look the above repo and do a review . I tried to split the big patch into parts, but I did not find the result to be too satisfactory Philip: would you be able to test the upgrade wizard? You need a service running under some older mysqld.exe binary (e.g. install MySQL 5.1), you'll should get upgrade wizard launched at the end of install. You should also get Wizard at the end of installation any time you have an older version of Maria running as service. Thanks and best regards, Wlad

6 27

[Maria-developers] IMPORTANT: LP bug #611648 does not allow running benchmarks for DS-MRR normally
by Igor Babaev 02 Feb '11

02 Feb '11

Hi, I've just reported bug #611648 that probably explains the strange results I had when trying to run my benchmark queries for DS-MRR about which I talked at today's optimizer meeting. Philip, please assign this bug to SergeyP. Regards, Igor.

2 1