Month: August 2015

Log Buffer #438: A Carnival of the Vanities for DBAs

This Log Buffer Edition covers Oracle, MySQL, and SQL Server blog posts from the last week.Oracle:Integrating Telstra Public SMS API into BluemixAdaptive Query Optimization in Oracle 12c : Ongoing UpdatesFirst flight into the Oracle Mobile Cloud ServiceOracle 12C Problem with datapatch. Part 2, the “fix”oracle applications r12 auto start on linuxSQL Server:Email Formatted HTML Table with T-SQL SQL Server 2016 – Introduction to Stretch DatabaseSoundex – Experiments with SQLCLR Part 3An Introduction to Real-Time Communication with SignalRStrange Filtered Index ProblemMySQL:Announcing Galera Cluster 5.5.42 and 5.6.25 with Galera 3.12doing nothing on modern CPUsSingle-threaded linkbench performance for MySQL 5.7, 5.6, WebScale and MyRocksIdentifying Insecure ConnectionsMyOraDump, Oracle dump utility, version 1.2 Learn more about Pythian’s expertise in Oracle , SQL Server & MySQL.

Log Buffer #437: A Carnival of the Vanities for DBAs

This Log Buffer Edition goes out deep into the vistas of database world and brings out few of the good ones published during the week from Oracle, SQL Server, and MySQL.Oracle:Overriding Default Context-Sensitive Action EnablementThis is an alternative to if… then… else… elsif… end if when you want to use conditional statements in PL/SQL.Achieving SAML interoperability with OAM OAuth ServerRelease of BP02 for Oracle Identity Manager Business Edge: Oracle Ties Mobile Security to Identity and Access ManagementSQL Server:How to render PDF documents using SQL CLR. Also a good introduction on creating SQL CLR functions.What is DNX?SQL Server Performance dashboard reportsUsing Microsoft DiskSpd to Test Your Storage SubsystemConnect to Salesforce Data as a Linked ServerMySQL:Optimizing PXC Xtrabackup State Snapshot TransferAdding your own collation to MySQLMonitoring your Amazon Aurora Databases using MONyogHow much could you benefit from MySQL 5.6 parallel replication?MySQL checksum Learn more about Pythian’s expertise in Oracle , SQL Server & MySQL.

Percona Toolkit 2.2.15 is now available

Percona is pleased to announce the availability of Percona Toolkit 2.2.15.  Released August 28, 2015. Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.This release is the current GA (Generally Available) stable release in the 2.2 series. It includes multiple bug fixes as well as continued preparation for MySQL 5.7 compatibility. Full details are below. Downloads are available here and from the Percona Software Repositories.New Features:Added –max-flow-ctl option to pt-online-schema-change and pt-archiver with a value set in percent. When a Percona XtraDB Cluster node is very loaded, it sends flow control signals to the other nodes to stop sending transactions in order to catch up. When the average value of time spent in this state (in percent) exceeds the maximum provided in the option, the tool pauses until it falls below again.Default is no flow control checking.Added the –sleep option for pt-online-schema-change to avoid performance problems. The option accepts float values in seconds.Implemented ability to specify –check-slave-lag multiple times for pt-archiver. The following example enables lag checks for two slaves:pt-archiver –no-delete –where ‘1=1’ –source h=oltp_server,D=test,t=tbl –dest h=olap_server –check-slave-lag h=slave1 –check-slave-lag h=slave2 –limit 1000 –commit-eachAdded the –rds option to pt-kill, which makes the tool use Amazon RDS procedure calls instead of the standard MySQL kill command.Bugs Fixed:Fixed bug 1042727: pt-table-checksum doesn’t reconnect the slave $dbh Before, the tool would die if any slave connection was lost. Now the tool waits forever for slaves.Fixed bug 1056507: pt-archiver –check-slave-lag agressiveness The tool now checks replication lag every 100 rows instead of every row, which significantly improves efficiency.Fixed bug 1215587: Adding underscores to constraints when using pt-online-schema-change can create issues with constraint name length Before, multiple schema changes lead to underscores stacking up on the name of the constraint until it reached the 64 character limit. Now there is a limit of two underscores in the prefix, then the tool alternately removes or adds one underscore, attempting to make the name unique.Fixed bug 1277049: pt-online-schema-change can’t connect with comma in password For all tools, documented that commas in passwords provided on the command line must be escaped.Fixed bug 1441928: Unlimited chunk size when using pt-online-schema-change with –chunk-size-limit=0 inhibits checksumming of single-nibble tables When comparing table size with the slave table, the tool now ignores –chunk-size-limit if it is set to zero to avoid multiplying by zero.Fixed bug 1443763: Update documentation and/or implentation of pt-archiver –check-interval Fixed the documentation for –check-interval to reflect its correct behavior.Fixed bug 1449226: pt-archiver dies with “MySQL server has gone away” when –innodb_kill_idle_transaction is set to a low value and –check-slave-lag is enabled The tool now sends a dummy SQL query to avoid timing out.Fixed bug 1446928: pt-online-schema-change not reporting meaningful errors The tool now produces meaningful errors based on text from MySQL errors.Fixed bug 1450499: ReadKeyMini causes pt-online-schema-change session to lock under some circumstances Removed ReadKeyMini, because it is no longer necessary.Fixed bug 1452914: –purge and –no-delete are mutually exclusive, but still allowed to be specified together by pt-archiver The tool now issues an error when –purge and –no-delete are specified together.Fixed bug 1455486: pt-mysql-summary is missing the –ask-pass option Added the –ask-pass option to the tool.Fixed bug 1457573: pt-mysql-summary fails to download pt-diskstats pt-pmp pt-mext pt-align Added the -L option to curl and changed download address to use HTTPS.Fixed bug 1462904: pt-duplicate-key-checker doesn’t support triple quote in column name Updated TableParser module to handle literal backticks.Fixed bug 1488600: pt-stalk doesn’t check TokuDB status Implemented status collection similar to how it is performed for InnoDB.Fixed bug 1488611: various testing bugs related to newer Perl versionsDetails of the release can be found in the release notes and the 2.2.15 milestone on Launchpad. Bugs can be reported on the Percona Toolkit launchpad bug tracker.The post Percona Toolkit 2.2.15 is now available appeared first on Percona Data Performance Blog.

“Attempt to read or write protected memory” error from SSMS for System.Data

I just spent a couple of hours fruitlessly trying to solve a problem and thought I’d blog the solution in case anyone else hits the issue. A few months back I got a new laptop and installed SQL Server 2014 on it and everything worked fine. A few weeks ago I installed SQL Server 2008 […]

The post “Attempt to read or write protected memory” error from SSMS for System.Data appeared first on Paul S. Randal.

Announcing Galera Cluster 5.5.42 and 5.6.25 with Galera 3.12

Codership is pleased to announce the release of Galera Cluster 5.5.42 and 5.6.25 with Galera Replication library 3.12, implementing wsrep API version 25.
Galera Cluster is now available as targeted packages and package repositories for a number of Linux distributions, including Ubuntu, Debian, Fedora, CentOS, OpenSUSE and SLES. Obtaining packages using a package repository removes the need to download individual files and facilitates the deployment and upgrade of Galera nodes.
This and future releases will be available from, while previous releases remain available on LaunchPad. The source repositories and bug tracking are now on
New features and notable changes in Galera Cluster and the Galera library:

Various forms of FLUSH that are replicated in traditional MySQL async replication are now also replicated in Galera under TOI (codership/mysql-wsrep#67)
The applier thread will now honor FLUSH TABLES WITH READ LOCK, FLUSH FOR EXPORT and will block until the lock is released (codership/mysql-wsrep#113)
Support for Debian Jessie (galera/mysql-wsrep#127,codership/galera#264)
The SST password is no longer passed via the command line or visble in the error log or ‘ps’ output (codership/mysql-wsrep#141)
The xtrabackup SST script has been updated from the upstream source (codership/mysql-wsrep#143)
Galera will abort gracefully if there is no disk space to write the required gcache files (codership/galera#324)
Gcache files are removed faster than before in order to reduce Galera disk usage (codership/galera#317)
Better error logging in case of SSL errors or misconfiguration (codership/galera#290)
The configuration in /etc/sysconfig/garb is now properly honored by the garbd systemd service (codership/galera#267)
Arbitrator service no longer starts automatically on package installation, giving the user the opportunity to configure it first (codership/galera#266)
Miscellaneous fixes in the garb startup script (codership/galera#186)

doing nothing on modern CPUs

Sometimes you don’t want to do anything. This is understandably human, and probably a sign you should either relax or get up and do something.
For processors, you sometimes do actually want to do absolutely nothing. Often this will be while waiting for a lock. You want to do nothing until the lock is free, but you want to be quick about it, you want to start work once that lock is free as soon as possible.
On CPU cores with more than one thread (e.g. hyperthreading on Intel, SMT on POWER) you likely want to let the other threads have all of the resources of the core if you’re sitting there waiting for something.
So, what do you do? On x86 there’s been the PAUSE instruction for a while and on POWER there’s been the SMT priority instructions.
The x86 PAUSE instruction delays execution of the next instruction for some amount of time while on POWER each executing thread in a core has a priority and this is how chip resources are handed out (you can set different priorities using special no-op instructions as well as setting the Relative Priority Register to map how these coarse grained priorities are interpreted by the chip).
So, when you’re writing spinlock code (or similar, such as the implementation of mutexes in InnoDB) you want to check if the lock is free, and if not, spin for a bit, but at a lower priority than the code running in the other thread that’s doing actual work. The idea being that when you do finally acquire the lock, you bump your priority back up and go do actual work.
Usually, you don’t continually check the lock, you do a bit of nothing in between checking. This is so that when the lock is contended, you don’t just jam every thread in the system up with trying to read a single bit of memory.
So you need a trick to do nothing that the complier isn’t going to optimize away.
Current (well, MySQL 5.7.5, but it’s current in MariaDB 10.0.17+ too, and other MySQL versions) code in InnoDB to “do nothing” looks something like this:
ulint ut_delay(ulint delay)
ulint i, j;
j = 0;
for (i = 0; i < delay * 50; i++) {
j += i;
if (ut_always_false) {
ut_always_false = (ibool) j;
On x86, UT_RELAX_CPU() ends up being the PAUSE instruction.
On POWER, the UT_LOW_PRIORITY_CPU() and UT_RESUME_PRIORITY_CPU() tunes the SMT thread priority (and on x86 they’re defined as nothing).
If you want an idea of when this was all written, this comment may be a hint:
/*!< in: delay in microseconds on 100 MHz Pentium */
But, if you’re not on x86 you don’t have the PAUSE instruction, instead, you end up getting this code:
# elif defined(HAVE_ATOMIC_BUILTINS)
# define UT_RELAX_CPU() do { \
volatile lint volatile_var; \
os_compare_and_swap_lint(&volatile_var, 0, 1); \
} while (0)
Which you may think “yep, that does nothing and is not optimized away by the compiler”. Except you’d be wrong! What it actually does is generates a lot of memory traffic. You’re now sitting in a tight loop doing atomic operations, which have to be synchronized between cores (and sockets) since there’s no real way that the hardware is going to be able to work out that this is only a local variable that is never accessed from anywhere.
Additionally, the ut_always_false and j variable there is also attempts to trick the complier into not optimizing the loop away, and since ut_always_false is a global, you’re generating traffic to a single global variable too.
Instead, what’s needed is a compiler barrier. This simple bit of nothing tells the compiler “pretend memory has changed, so you can’t optimize around this point”.
__asm__ __volatile__ (“”:::”memory”)
So we can eliminate all sorts of useless non-work and instead do what we want: do nothing (a for loop for X iterations that isn’t optimized away by the compiler) and don’t have side effects.
In MySQL bug 74832 I detailed this with the appropriately produced POWER assembler. Unfortunately, this patch (submitted under the OCA) has sat since November 2014 (so, over 9 months) with no action. I’m a bit disappointed by that to be honest.
Anyway, the real moral of this story is: don’t implement your own locking primitives. You’re either going to get it wrong or you’ll be wrong in a few years when everything changes under you.
See also:
SMT Priorities in the book “Performance Optimization and Tuning Techniques for IBM Processors including POWER8”

Single-threaded linkbench performance for MySQL 5.7, 5.6, WebScale and MyRocks

The good news is that features in the pending 5.7 release look great. More good news is that InnoDB transparent page compression might be faster than the original compression feature assuming your storage system supports it. The bad news is that there are significant performance regressions for low-concurrency workloads. I previously reported this for 5.6 and 5.7.5 and have yet to see progress. While the focus on high-concurrency workloads has been important, we can’t lose this much performance at low-concurrency.I used linkbench with one client thread to load and then query a small & cached database. This was repeated for many configurations to determine the impact from compression and partitioning. The binlog was enabled but it and the InnoDB redo log were not synced on commit. The performance summary is:InnoDB transparent compression is faster for loads than non-transparent compressionInsert rates for 5.7.8 are much worse than for 5.6. The insert rate for 5.6 are between 1.07X and 1.77X faster than 5.7.The Facebook 5.6 build is always faster than 5.6.26 and 5.7.8, except when transparent compression is used.MyRocks (MySQL+RocksDB) has the fastest load rateDefault configuration for the performance schema reduces the insert rate by 5%Transparent compressionWhile transparent compression might be a lot faster than non-transparent compression I share the same doubts as Domas about whether we can use this feature in production. The feature requires either a filesystem that is happy when doing a holepunch on every write or a filesystem that is really a key-value store where the key is pageID, the value is a page and the page will be compressed on write with minimal roundup to meet alignment requirements. By roundup I mean that when a 16kb page is compressed to 11kb, it is written as 11kb when rounded up to the next 512b sector or 12kb when rounded up to the next 4kb sector. With 2X compression, that page would be split and the ~5.5kb compressed output for each post-split page would be rounded up to 8kb.AFAIK there isn’t a GA solution for the filesystem that doesn’t require holepunch on write. There is a proprietary solution (filesystem & storage device) that is not yet GA from one vendor and the open-source solution might be bcachefs assuming it gets sufficient support to reach GA.InnoDB page compression adds latency to query processing. By non-transparent compression I mean the original page compression for InnoDB where a compression factor (2X, 4X, 8X) is declared per table. Both transparent and non-transparent page compression add the latency of decompression after page reads. There is other latency that only occurs for non-transparent page compression. By latency I mean work done by threads handling SQL statements and these are things that I see when looking at PMP stack traces.Page images are optionally written to the redo log to avoid the risk of changing the zlib library during crash recovery. I always disable page image logging.When the per-page compression log is full all changes must be applied to the page and then the page is compressed.When a page doesn’t compress enough (see #2) then a page split is done and the split pages must then be compressed. When a page split is done the per-index mutex is held so other threads can’t access that index until the operation is done. I frequently use partitioning to reduce the impact from contention on the per-index mutex.There is a bit more work to manage both the uncompressed and compressed LRUs.Performance regressionsUsing partitioning has improved InnoDB performance for IO-bound linkbench tests in the past as it reduces contention for the per-index mutex. This test wasn’t IO-bound as the database was always cached by InnoDB, but I still wanted to see the impact from the different configurations. Transparent compression in this case is done via holepunch-on-write. I ignore the compression rates for now and will revisit that in a future test. I tested these configurations:p0.c0 – no partitioning, no compressionp0.c1 – no partitioning, compression (transparent & non-transparent)p1.c0 – partitioning, no compressionp1.c1 – partitioning, compression (transparent & non-transparent)The linkbench test was run with maxid=2000001, loaders=1 & requesters=1. Otherwise the default settings were used. The InnoDB buffer pool was large enough to cache the database, the binlog was enabled but the binlog and InnoDB redo log were not synced on commit. I set many other my.cnf values to match what we use in production but don’t list them here. The p0.c0 test was repeated for upstream 5.6.26 and 5.7.8 with and without the PS enabled (performance_schema=0, 1 in my.cnf). I didn’t set any other options for the PS. The load rate is reduced by about 5% when the PS is enabled with default options.I tested several binaries:myrocks – the Facebook patch for MySQL, 5.6.X and the RocksDB storage enginefb56 – the Facebook patch for MySQL, 5.6.X and InnoDBorig56.nops, – upstream 5.6.26 without and with PS enabled. The my.cnf file I used was based on what we use in production and is here.orig57.nops, – upstream 5.7.8 with non-transparent compression, without and with PS enabled. The my.cnf file I used was based on what we use in production and is – upstream 5.7.8 with transparent compression and PS disabledThe test was done in two parts. First I measure the rate at which one thread can load the database. Then I measure the average QPS sustained for one hour. The data below is the insert rate from the load (load ips), the size after load (load gb), the QPS rate during the 1-hour run (1h qps) and the size after the 1 hour run (1h gb).The relative ips column is the insert rate for that configuration divided by the rate for upstream 5.7.8. These rates are large and we have lost a lot performance at low-concurrency in 5.7.8. Most of the loss is not from the performance schema.p0.c0load    load    1h      1h     relativeips     gb      qps     gb     ips       config 53577  X       3244    X      1.57      myrocks 47165  2.3     4016    3.0    1.38      fb56 46785  2.6     3698    3.4    1.37      orig56.nops 48857  2.6     3735    3.4    1.43 34233  2.6     3604    3.4    1.0       orig57.nops 36077  2.6     3693    3.4    1.05      orig57.psp0.c1load    load    1h      1h     relativeips     gb      qps     gb     ips       config 17688  1.5     3874    1.9    1.33      fb56 14164  1.7     3481    2.1    1.07      orig56.nops 13252  1.7     3372    2.1    1.0       orig57.nops 34770  2.1     3510    2.3    2.62      orig57.tcp1.c0load    load    1h      1h     relativeips     gb      qps     gb     ips       config 45980  3.2     3921    3.8    1.77      fb56 34660  3.5     3560    4.1    1.34      orig56.nops 25908  3.5     3391    4.1    1.0       orig57.nopsp1.c1load    load    1h      1h     relativeips     gb      qps     gb     ips       config 16152  2.1     3707    2.4    1.50      fb56 12345  2.3     3296    2.7    1.15      orig56 10779  2.3     3214    2.7    1.0       orig57.nops 23148  3.0     3361    3.0    2.15      orig57.tcGraphsA graph for the p0.c0 test (no partitioning, no compression) showing relative insert rates.

TEL/電話+86 13764045638
QQ 47079569