Saturday, 19 December, 2015

dim_STAT v.9.0-u15 CoreUpdate is ready !

The 2015 year is finishing, and I'm happy to present you the latest dim_STAT v.9.0 CoreUpdate-15 (just on time ;-))

So, what is new ?

First of all I'd like to tell you about the new experimental STAT-service v.5.0 :

  • the main difference in this version is coming with remastered code of the daemon
  • now you're no more attached to the fixed /etc/STATsrv directory on your server
  • you can install STAT-service anywhere you want ! ;-))
  • this is resolving any situations when you don't have write permissions in /etc
  • or simply need to monitor several database instances running in parallel on the same host
  • now you may just duplicate your STAT-service instances into different directories and start them on different IP ports
  • also, to simplify scripts management, STAT-service is publishing as environment variables its currently used home directory (as SRV_HOME) and IP port number (as SRV_PORT)
  • you can use them in your scripts or config files to make their maintenance much more simple
  • and yes, both variable are also considered within the access file as well
  • while the name of the access file is now also configurable, so you can use different files according needs, etc.
  • in parallel many bundled scripts are remastered and simplified
  • all the stats you need to collect via EasySTAT for ex. now are moved to .env-easystat file
  • for SysINFO - into .env-sysinfo
  • so, you don't need to edit a big script file to change the list of your options, but just a small one, etc.
  • also, to simplify configuration of connection setting for Oracle RDBMS, MySQL and PostgreSQL stats all related scripts are checking the corresponding .env-oracle, .env-mysql and .env-pgsql files -- so, just one file to edit to make all scripts working
  • whenever possible, the scripts were remastered to avoid zero sleep time and unlimited sleep time due OS clock time changes or heavy system load..
  • well, there was a lot of ile editing, that's why I'm calling this STAT-service version experimental, as it's still possible that I've missed something on one of the scripts ;-))
  • STAT-service v.5.0 is available for Linux, Solaris 10+ SPARC and x64, MacOSX :

  • NOTE: each tarball is also containing "src" directory with new STATsrv.c and STATcmd.c code -- just in case if you have any issues with new binaries on your systems, you may always recompile them yourself..

PerfSTAT

Analyzing performance problems on Linux I'm often needing a profiler, but rather a profiler with a low overhead ;-)) - the "perf" tool is pretty good for that, but I was missed from a long time live profiling collects saved directly into dim_STAT. Now it's fixed, and you can use this new PerfSTAT Add-On to see the top-N hot functions from various programs running on your system to understand what is really going odd.. -- in the following example I'm just analyzing from where the regressions are coming in different MySQL engines :

don't hesitate to try it yourself, you may discover very interesting things on your machines ;-))

Command Line Interface

I'm surprised how many people are using dim_STAT from the command line to generate various graphs for their reports or different workflows.. - so, I've extended then existing options/features yet more :
  • dim_STAT-CLI :
    • there is a new "-Times" option to allow you to select several time intervals on the same graph T1-T2[,T3-T4[,..]]) similar as you do via web interface
    • the "-RAW" option allows now to directly export raw stats data from your database
  • dim_STAT-Admin :
    • delete ALL is now involving TRUNCATE on tables which is giving a really huge speed-up ;-))
  • dimSTAT-Server :
    • there is now also a "start-force" option available to by-pass the situations when you're starting your dim_STAT instance for the first time and some database files are missed just because your database was not yet initialized..

Other changes & fixes

just to mention few more :
  • you can now Backup and Restore your Bookmark Presets ;-))
  • Bookmark IMPORT was improved to allow you better manage all bundled Add-Ons
  • in case you're needing to collect very big numbers in your Add-Ons, there are new data types were added: Big-Integer and Big-Float
  • any use of "/tmp" in the code for temporary files was removed, everywhere the TMP_DIR environment variable is used (it's configured when you're installing dim_STAT and saved within /opt/WebX/x.env file -- you may modify it at any time)
  • the Log messages are better checked now for odd characters on entry (did not expect such, and don't know if it'll be enough ;-))
  • and various other code remastering and minor fixes..

Hope you'll enjoy the new release! ;-))

The upgrade process is simple as usual :
  • 0.) no need to stop anything, update can be applied live on your running dim_STAT instance ;-))
  • 1.) download the latest tgz (WebX_apps-v90-u15.tgz) file from the CoreUpdates repository: http://dimitrik.free.fr/Core_Updates/
  • 2.) backup your current apps scripts:
    • $ cd /opt/WebX ; tar czf apps-bkp.tgz apps
  • 3.) deploy the CoreUpdate-15 scripts bundle:
    • $ cd /opt/WebX; tar xzf /path/to/WebX_apps-v90-u15.tgz
  • 4.) enjoy ;-)

As usually, any feedback is welcome!

Rgds,
-Dimitri
Posted by Dimitri at 4:42 - Comments...
Categories: dim_STAT

Friday, 11 December, 2015

MySQL Performance: Re:Visiting InnoDB vs MyISAM with MySQL 5.7

Next article from the MySQL 5.7 Performance stories (if missed, see 1.6M SQL Query/sec (QPS) with MySQL 5.7, 1M SQL Query/sec on mixed OLTP_RO , true Point-Selects performance and over 100K Connect/sec Rate -- all with MySQL 5.7)..

The today's article will be about re:visited MyISAM -vs- InnoDB performance comparison within MySQL 5.7 -- in fact the main and detailed article related to these engines comparison I've already published in 2012 but with MySQL 5.6 (just before MySQL 5.6 became GA) -- however, since then I'm constantly re:asked "And what about MySQL 5.7 ?" -- so, the following is the answer ;-))

First of all, let's summarize little bit what was already observed with MySQL 5.6 before :

  • so, on Full Text Search (FTS) InnoDB was already way better than MyISAM, no need to replay..
  • (on OLTP RW with its table-locking design MyISAM will always loose -vs- row-locking InnoDB, no need to waste a time either)
  • then, on mixed OLTP_RO workload : InnoDB was slightly, but better (215K QPS) -vs- MyISAM (200K QPS)
  • on Simple-Ranges queries workload : InnoDB was much better (170K QPS) -vs- MyISAM (95K QPS)
  • and on Point-Select queries workload : InnoDB was much worse (250K QPS) -vs- MyISQM (430K QPS)
    • NOTE: however, InnoDB was able to reach 450K QPS in experimental on that time "read_only mode", this showed the potential gain possible "in theory" to reach on InnoDB by by-passing the whole transactions layer, but not too much useful in real life (if writes are not allowed), so for MySQL 5.6 times it was more likely a hacking rather a real solution..
But since then, things are changed a lot with MySQL 5.7 :
  • the whole transactional layer was greatly improved in InnoDB
  • as well the whole MySQL Server code got a rid of all known scalability bottlenecks
  • so, we're having a much higher expectation to scale today with MySQL 5.7 than with 5.6 in the past ;-)

However, keeping this all in mind, it's not yet really clear if since then the gap between InnoDB and MyISAM was increased or decreased in MySQL 5.7 -- because general improvements in MySQL Server code made also MyISAM running faster.. -- and as usual, only a real test will give us a real answer ;-)

So far, this was the main reason to re:visit 3 years old obtained results, and my new results presented below are coming from the following :
  • HW config : 32cores-HT server (exactly the same I've used 3 years ago) and a newer 40cores-HT server (to observe a tendency)
  • OS : the same Oracle Linux 6.5
  • Test workloads :
    • load level : 8, 16, 32, .. 1024 concurrent user sessions
    • test cases: Sysbench OLTP_RO, RO Simple-Ranges, RO Distinct-Ranges, RO Point-Selects
    • datasets : single table with 10M rows, 8 tables with 1M rows each

And, finally, here are the results :

Mixed OLTP_RO workload



dataset 10M x 1-table @32cores-HT :

dataset 1M x 8-tables @32cores-HT :

dataset 10M x 1-table @40cores-HT :

dataset 1M x 8-tables @40cores-HT :


RO Simple-Ranges

dataset 10M x 1-table @32cores-HT :

dataset 1M x 8-tables @32cores-HT :

dataset 10M x 1-table @40cores-HT :

dataset 1M x 8-tables @40cores-HT :


RO Distinct-Ranges

dataset 10M x 1-table @32cores-HT :

dataset 1M x 8-tables @32cores-HT :

dataset 10M x 1-table @40cores-HT :

dataset 1M x 8-tables @40cores-HT :


RO Point-Selects

dataset 10M x 1-table @32cores-HT :

dataset 1M x 8-tables @32cores-HT :

dataset 10M x 1-table @40cores-HT :

dataset 1M x 8-tables @40cores-HT :


SUMMARY :
  • in MySQL 5.7 both InnoDB and MyISAM engines are giving better results than before on the same workloads and the same HW
  • all the results obtained on 40cores-HT server are better than on 32cored-HT
  • the most critical Point-Select workload is now leaving only a small gain to MyISAM -vs- InnoDB (and it's easy to understand - the overhead of transactions is still present in InnoDB, even if the code was greatly improved for scalability.. -- however, this is now nothing about hacking, but a true, production ready, re:designed code!)..
  • then, on all other test workloads - InnoDB is doing just better than MyISAM
  • while on all the tests using a single table - InnoDB is far way faster than MyISAM, even on Point-Selects


As usual, any comments are welcome! Thank you for using MySQL ! (and preparing your upgrade to MySQL 5.7 asap ;-))

MySQL 5.7 rocks! ;-)

Rgds,
-Dimitri
Posted by Dimitri at 19:25 - Comments...
Categories: MySQL

Friday, 27 November, 2015

MySQL Performance: Improved Connect/sec Rate in MySQL 5.7

This article is continuing the MySQL 5.7 Performance story, started from 1.6M QPS on MySQL 5.7 details post , then 1M QPS on mixed OLTP_RO with MySQL 5.7 article, and detailed story about why the Point-Selects performance is so critical (and why the 1M result published by MariaDB is not fair)..

The current story will be about Connect/sec (connect/disconnect) performance improvement in MySQL 5.7 - such kind of metric is very critical for any application which cannot use persistent connections all the time (and many web apps are in such a case). Well, I'd say MySQL from the beginning was extremely good for its lightweight connections, and made in the past the base of success for many web solutions.. However, time is going, and we're no more with 4cores as "commodity hardware" (this is rather a smart-watch today ;-)) - so, there was a need to speed-up this Connect rate to match higher workloads. This was already greatly done in MySQL 5.6, and finally yet more improved in MySQL 5.7 - you may read all details about directly from our developers - I'll just present here a short summary about where we're today..

So far, first of all, how to test the Connect/sec performance of your MySQL server instance? - the most simple way here is just to use a standard Sysbench kit, load 10M rows into sysbench database (1 table or several tables, no matter -- the main show-stopper here is the Connect code itself), and then run the following :

#!/bin/bash

# ----------------------------------------------------------------
# Connect/sec test
# ----------------------------------------------------------------

  for Users in 8 16 32 64 128 256 512 1024
  do
    LD_PRELOAD=/usr/lib64/libjemalloc.so.1  sysbench --num-threads=$Users \
        --test=oltp --oltp-table-size=10000000 \
        --db-ps-mode=disable --oltp-dist-type=uniform --oltp-table-name=sbtest_10M \
        --max-requests=0 --max-time=300 --mysql-socket=/tmp/mysql.sock \
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
        --mysql-table-engine=INNODB  --db-driver=mysql \
        --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \
        --oltp-read-only=on --oltp-reconnect-mode=query --oltp-connect-delay=0 run
    sleep 30
  done

# ----------------------------------------------------------------


means on every point-select query your client session will re-connect, and the final QPS result will give you the max Connect/sec rate your MySQL instance is able to reach for a given amount of concurrent users.

And here are the results obtained from older to newer generations Intel-based Linux servers :

12cores-HT @2.9Ghz :

32cores-HT @2.3Ghz :

40cores-HT @2.3Ghz :

72cores-HT @2.5Ghz :

Instead of SUMMARY :

  • Connect/sec performance is mainly depending on the MySQL "connect/disconnect" code itself + CPU chip speed
  • it's hard to speak about "scalability" here as the max possible Connect/sec rate limit is reached pretty quickly and depending on IP stack performance as well..
  • tuning "thread_cache_size" to something bigger than zero is helping here, but not too much..
  • MySQL 5.7 showing the best performance here regardless the HW platform, and reaching over 100K Connect/sec on the latest Intel CPU chip
  • there is a clear better-and-better tendency in MySQL 5.5 => 5.6 => 5.7 results
  • and we still can do yet more better with MySQL 5.8 ! (question of time and resources - so, please, send us your feedback/ votes/ wishes if you want to see it ;-))

MySQL 5.7 rocks! ;-))

Rgds,
-Dimitri

Posted by Dimitri at 18:15 - Comments...
Categories: MySQL

Wednesday, 11 November, 2015

MySQL Performance: What is odd with 1M QPS claimed by MariaDB 10.1 ?..

This article is continuing the MySQL 5.7 Performance story, started from 1.6M QPS details post, and followed by 1M QPS OLTP_RO article. However, the current story will not be mostly about MySQL 5.7, but also about announced on the same time MariaDB 10.1 GA ;-)

So far, MySQL Team was proud to show 1.6M QPS on Point-Select (SQL) queries, and MariaDB 10.1 GA announce was also claiming an ability to reach 1M QPS, also on Point-Selects, but on POWER8 HW. And I may be only happy for MariaDB team for their progress on POWER systems, except just one small detail related to how their 1M QPS result was obtained..

But first of all, what are these Point-Selects and what is so special with this workload ?..

  • point-select is representing a single SQL query reading a row by its primary key (PK)
  • a workload based on such queries is very similar to any generic key-value store solution, but via SQL
  • usually a point-select is extremely fast and doing a round-trip from end-to-end in database engine
  • so any internals overhead in engine code, any contentions, any scalability limits are seen very quickly..
  • historically such kind of workload did not scale very well on MySQL/InnoDB tandem..
  • on MySQL Server side there was a huge amount of internal locks around every SQL query execution
  • while on the InnoDB side there was a huge general overhead related to transactions management + related locks..
  • the first step ahead was made here in MySQL 5.6
  • but the real changes came only with MySQL 5.7 ;-)


So, our 1.6M QPS on Point-Selects with MySQL 5.7 we were able to reach under the most heavy conditions :
  • each Sysbench "client" thread is executing a single point-select query per iteration
  • no transactions clauses used, no grouping..
  • single query round-trip is driving the response time latency
  • a fair simulation of a key-value store by SQL access


and the Sysbench execution command line is looking then as the following:
LD_PRELOAD=/usr/lib64/libjemalloc.so /BMK/sysbench --num-threads=$1 \
        --test=oltp --oltp-table-size=1000000 \
        --oltp-dist-type=uniform --oltp-table-name=sbtest_1M_$n \
        --max-requests=0 --max-time=$2 --mysql-socket=/SSD_raid0/mysql.sock \
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
        --mysql-table-engine=INNODB  --db-driver=mysql \
        --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \
        --oltp-read-only=on run  > /tmp/test_$n.log &


Now, about 1M QPS result published on MariaDB 10.1 :
  • if you'll look in details published in their article, instead of --oltp-point-selects=1 the --oltp-point-selects=1000 was used
  • and these 1K point-selects then grouped and executed within a single transaction!.. (--oltp-skip-trx=off is used (default))


Adapting these test conditions to my Sysbench command line this will then give the following :
LD_PRELOAD=/usr/lib64/libjemalloc.so /BMK/sysbench --num-threads=$1 \
        --test=oltp --oltp-table-size=1000000 \
        --oltp-dist-type=uniform --oltp-table-name=sbtest_1M_$n \
        --max-requests=0 --max-time=$2 --mysql-socket=/SSD_raid0/mysql.sock \
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
        --mysql-table-engine=INNODB  --db-driver=mysql \
        --oltp-point-selects=1000 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=off \
        --oltp-read-only=on run  > /tmp/test_$n.log &


How does this change the initial workload test case ?
  • in one word : completely ;-)
  • by grouping 1000 queries within a single transaction we're lowering a lot the whole related transaction locks..
  • and as the result, an overall QPS numbers are growing! ;-)
  • but did you see many real cases when a single MySQL session is grouping 1000 SELECT queries within a single transaction ?..
  • I may still imagine a need to run several SELECTs within a single transaction to guarantee a read consistency...
  • but such kind of queries are usually not short ones, so not really good to show a high QPS ;-)
  • but well, even they are really short as point-selects.. -- what kind of application will run them by 1000 in a single shot ?..
  • such kind of a "workaround" we proposed yet to our users when started to ship MySQL 5.6, but always mentioned that this is as a workaround, not a final solution (as this could help when you're really have many fast SELECTs and you can really group then within a single transaction without breaking your apps logic).. -- but as soon as potential amount of SELECTs to group is small, the transaction overhead is quickly eating your gain..
  • so, the real solution here is - MySQL 5.7 ;-)


Let me show now all this by example, using the test scenario as proposed by MariaDB and just vary the amount of point-selects executed within a single transaction. Let's start with 50 point-selects (which is already too much as to me, but well) :

50 point-selects in transaction :

Observations :
  • MySQL 5.7 is still reaching its 1.6M QPS as well
  • MariaDB 10.1 is reaching over 1.4M QPS (so, not only on Power while running within a "workaround" test conditions)..


Then the following are the results with 25, 10, and 5 point-selects executed within the same transaction :

25 point-selects in transaction :

10 point-selects in transaction :

5 point-selects in transaction :

Observations :
  • as you can see, less queries executed within the same single transaction - lower QPS is reached..
  • so, MySQL 5.7 is going from 1.6M QPS to 1.2M QPS
  • while MariaDB 10.1 is going from 1.4M QPS to just 600K QPS..


On the last test, with 5 point-selects within a transaction the lower QPS is also impacted by round-trips of BEGIN and COMMIT statements around transaction.. What the result will be if I'll not use transactions?

5 point-selects without transaction :

Observations :
  • as you can see, MySQL 5.7 is going back to its 1.6M QPS
  • while MariaDB 10.1 is going yet more down to just slightly higher than 400K QPS only..


To get a better understanding of the transaction statements impact let's get a look on the following graph comparing MySQL 5.7 in the last test with 5 point-selects within transaction (left side) -vs- 5 point-selects without transaction (right side) :

Observations :
  • as you can see in overall amount of Queries/sec the left side is even higher! (BEGIN and COMMIT are also counted as queries by MySQL stats)
  • and as we're wasting CPU cycles to process BEGIN and COMMIT - we're doing less SELECTs as the result..
  • while on the right side (without transactions) the whole CPU time is spent for SELECTs, and we're reaching 1.6M QPS then ;-)


Now :
  • it's up to you to consider whenever MariaDB 10.1 is really reaching 1M QPS, or 400K QPS only ;-))
  • however, what is important for me here after all : you can really reach 1.6M QPS with MySQL 5.7 whatever the test conditions are used here ;-))


Then, to avoid any kind of speculations about new Intel chips as the reason of excellent MySQL 5.7 scalability results, let me just remind you about the results obtained on the old severs (32cores-HT and 40cores-HT), running the old Intel CPUs (similar 4CPU sockets each, but just 8cores-HT and 10cores-HT per CPU socket, both are running at 2300Mhz frequency). The following are the results obtained on the same Sysbench RO Point-Select workload :
  • single point-select per iteration, no transactions
  • 8 tables of 1M rows are used in the first test
  • 1 table of 10M rows is used in the second test


32cores-HT :


40cores-HT :


72cores-HT :


Observations :
  • as you can see there is a huge gap between MySQL 5.7 and any other engine on 32, 40 and 72cores-HT HW..
  • the gap on 72cores-HT HW is way bigger only because MySQL 5.7 is continuing to scale and reaching a yet more higher result, while all other engines are already reached their limits and cannot go anymore further..
  • NOTE : only MySQL 5.7 is showing near the same QPS results on both 8-tables and 1-table workloads


INSTEAD OF SUMMARY :
  • From where is coming such a great scalability gain in MySQL 5.7 ?
    • first of all it's a continuous improvement process started yet since MySQL 5.6 with kernel_mutex split + RO transactions on InnoDB side, and many various internal contentions improvements on the MySQL Server side
    • then, on MySQL 5.7 InnoDB side : a whole transactions management redesign, improved lock management, redesigned index locking, etc...
    • MySQL 5.7 Server side : resolved contentions around MDL, THR_lock, LOCK_grant, LOCK_plugin, and all other "next-level" locks fixes.. -- in fact for today there is no more any known/visible internal contentions in MySQL 5.7 except coming from InnoDB ;-)
    • so far, all the credit is to our great MySQL Engineering Team! (all the listed stuff above is related to fundamental changes invented and implemented by our Engineering, taking us months and years of heavy work)..
    • and, of course, huge thanks to MySQL Community for all feedback we have about ;-)

  • When MariaDB will be able to scale as far as MySQL 5.7 ?
    • when it'll move to InnoDB SE from MySQL 5.7
    • and when on its Server side the lock contention in the "lock free" table definition cache code will be fixed ;-)

  • When Percona Server will scale as far as MySQL 5.7 ?
    • since Percona Server will move to the MySQL 5.7 code base
    • and XtraDB moves to InnoDB code base from MySQL 5.7
    • not before ;-)

  • or both are re:implementing all these changes in their 5.6 / 10.1 code ;-)
  • And hope I don't say you here any news, because the same story was already with MySQL 5.6 too, but who cares? ;-))

Well, what to say.. -- #MySQL 5.7 rocks! ;-)

Rgds,
-Dimitri

Posted by Dimitri at 2:12 - Comments...
Categories: MySQL

Friday, 06 November, 2015

Slides from my talk about MySQL Performance @OpenWorld 2015

Slides from my talk during MySQL Central @OpenWorld 2015 are available from here now :
- http://dimitrik.free.fr/Presentations/MySQL_Perf-Tuning-OOW2015-dim.pdf 

they should be soon available from the OpenWorld site as well.

Rgds,
-Dimitri

Posted by Dimitri at 20:33 - Comments...
Categories: MySQL