« February 2016 | Main | November 2015 »
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
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..
- 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