Very High Activity
I Use This!

News

Analyzed 8 days ago. based on code collected 8 days ago.
Posted about 22 hours ago by MySQL Performance Blog
This blog post lists the Percona Live Europe Amsterdam 2016 tutorial schedule. We are excited to announce that the tutorial schedule for the Percona Live Europe Amsterdam Open Source Database Conference 2016 is up! The Percona Live Europe Amsterdam ... [More] Open Source Database Conference is the premier event for the diverse and active open source community, as well as businesses that develop and use open source software. The conferences have a technical focus with an emphasis on the core topics of MySQL, MongoDB, and other open source databases. Tackling subjects such as analytics, architecture and design, security, operations, scalability and performance, Percona Live provides in-depth discussions for your high-availability, IoT, cloud, big data and other changing business needs. Tackling subjects such as analytics, architecture and design, security, operations, scalability and performance, Percona Live Europe provides in-depth discussions for your high-availability, IoT, cloud, big data and other changing business needs. This conference is an opportunity to network with peers and technology professionals by bringing together accomplished DBA’s, system architects and developers from around the world to share their knowledge and experience – all to help you learn how to tackle your open source database challenges in a whole new way. These tutorials are a must for any data performance professional! The Percona Live Europe Open Source Database Conference is October 3-5 at the Mövenpick Hotel Amsterdam City Centre. Click through to the tutorial link right now, look them over, and pick which sessions you want to attend. Discounted passes available below! Tutorial List: InnoDB Architecture and Performance Optimization Peter Zaitsev (Percona) Best Practices for MySQL High Availability Colin Charles (MariaDB) ProxySQL Tutorial René Cannaò (Self Employed),  Derek Downey (Pythian),  David Turner(Dropbox) MySQL Operations in Docker Giuseppe Maxia (VMware) MySQL GTID Implementation, Maintenance and Best Practices Mark Filipi (SurveyMonkey),  Gillian Gunson (GitHub),  Brian Cain (Dropbox) Become a MySQL DBA (Part 1 and 2) Krzysztof Książek (Severalnines AB) MongoDB 101 (Part 1 and 2) David Murphy (Percona) NoSQL Data Stores in Research and Practice Felix Gessert MySQL Performance Schema in Action Alexander Rubin (Percona),  Sveta Smirnova (Percona) MySQL Group Replication in a Nutshell: Hands-on Tutorial Frédéric Descamps (Oracle) MyRocks Deep Dive: Flash Optimized LSM Database for MySQL, and Its Use Case at Facebook Yoshinori Matsunobu (Facebook) MySQL Schema Design in Practice Jaime Crespo (Wikimedia Foundation) MySQL High Availability with Percona XtraDB 5.7 TBD The Complete MariaDB Tutorial Colin Charles (MariaDB) Early Bird Discounts Just a reminder to everyone out there: our Early Bird discount rate for the Percona Live Europe Amsterdam Open Source Database Conference is only available ‘til August 8, 2016, 11:30 pm PST! This rate gets you all the excellent and amazing opportunities that Percona Live offers, at a very reasonable price! Sponsor Percona Live Become a conference sponsor! We have sponsorship opportunities available for this annual MySQL, MongoDB and open source database event. Sponsors become a part of a dynamic and growing ecosystem and interact with hundreds of DBAs, sysadmins, developers, CTOs, CEOs, business managers, technology evangelists, solutions vendors, and entrepreneurs who attend the event. [Less]
Posted 1 day ago by MySQL Performance Blog
In this blog, we’ll discuss monitoring MongoDB with Nagios. There is a significant amount of talk around graphing MongoDB metrics using things like Prometheus, Data Dog, New Relic, and Ops Manager from MongoDB Inc. However, I haven’t noticed a lot of ... [More] talk around “What MongoDB alerts should I be setting up?” While building out Percona’s remote DBA service for MongoDB, I looked at Prometheus’s AlertManager. After reviewing it, I’m not sure it’s quite ready to be used exclusively. We needed to decide quickly if there are better Nagios checks on the market, or did I need to write my own? In the end, we settled on a hybrid approach. There are some good frameworks, but we need to create or tweak some of the things needed for an “SEV 1-” or “SEV 2-” type issue (which are most important to me). One of the most common problems for operations, Ops, DevOps, DBA teams and most engineering is alert spam. As such I wanted to be very careful to only alert on the things pointing to immediate dangers or current outages. As a result, we have now added pmp-check-mongo.py to the GitHub for Percona Monitoring Plugins. Since we use Grafana and Prometheus for metrics and graphing, there are no accompanying Catci information templates. In the future, we’ll need to decide how this will change PMP overtime. In the meantime, we wanted to make the tool available now and worry about some of the issues later on. As part of this push, I want to give you some real world examples of how you might use this tool. There are many options available to you, and Nagios is still a bit green in regards to making those options as user-friendly as our tools are.Usage: pmp-check-mongo.py [options] Options: -h, --help show this help message and exit -H HOST, --host=HOST The hostname you want to connect to -P PORT, --port=PORT The port mongodb is running on -u USER, --user=USER The username you want to login as -p PASSWD, --password=PASSWD The password you want to use for that user -W WARNING, --warning=WARNING The warning threshold you want to set -C CRITICAL, --critical=CRITICAL The critical threshold you want to set -A ACTION, --action=ACTION The action you want to take. Valid choices are (check_connections, check_election, check_lock_pct, check_repl_lag, check_flushing, check_total_indexes, check_balance, check_queues, check_cannary_test, check_have_primary, check_oplog, check_index_ratio, check_connect) Default: check_connect -s SSL, --ssl=SSL Connect using SSL -r REPLICASET, --replicaset=REPLICASET Connect to replicaset -c COLLECTION, --collection=COLLECTION Specify the collection in check_cannary_test -d DATABASE, --database=DATABASE Specify the database in check_cannary_test -q QUERY, --query=QUERY Specify the query, only used in check_cannary_test --statusfile=STATUS_FILENAME File to current store state data in for delta checks --backup-statusfile=STATUS_FILENAME_BACKUP File to previous store state data in for delta checks --max-stale=MAX_STALE Age of status file to make new checks (seconds)There seems to be a huge amount going on here, but let’s break it down into a few categories: Connection options Actions Action options Status options Hopefully, this takes some of the scariness out of the script above. Connection options Host / Port Number Pretty simple, this is just the host you want to connect to and what TCP port it is listening on. Username and Password Like with Host/Port, this is some of your normal and typical Mongo connection field options. If you do not set both the username and password, the system will assume auth was disabled. SSL This is mostly around the old SSL support in Mongo clients (which was a boolean). This tool needs updating to support the more modern SSL connection options. Use this as a “deprecated” feature that might not work on newer versions. ReplicaSet Very particular option that is only used for a few checks and verifies that the connection uses a replicaset connection. Using this option lets the tool automatically find a primary node for you, and is helpful to some checks specifically around replication and high availability (HA): check_election check_repl_lag check_cannary_test chech_have_primary check_oplog Actions and what they mean check_connections This parameter refers to memory usage, but beyond that you need to know if your typical connections suddenly double. This indicates something unexpected happened in the application or database and caused everything to reconnect. It often takes up to 10 minutes for those old connections to go away. check_election This uses the status file options we will cover in a minute, but it checks to see if the primary from the last check differs from the current found primary. If so, it alerts. This check should only have a threshold of one before it alarms (as an alert means an HA event occurred). check_lock_pct MMAP only, this engine has a write lock on the whole collection/database depending on the version. This is a crucial metric to determine if MMAP writes are blocking reads, meaning you need to scale the DB layer in some way. check_repl_lag Checks the replication stream to understand how lagged a given node is the primary. To accomplish this, it uses a fake record in the test DB to cause a write. Without this, a read-only system would look lagged artificially as no new oplog entries get created. check_flushing A common issue with MongoDB is very long flush times, causing a system halt. This is a caused by your disk subsystem not keeping up, and then the DB having to wait on flushing to make sure writes get correctly journaled. check_total_indexes The more indexes you have, the more the planner has to work to determine which index is a good fit. This increases the risk that the recovery of a failure will take a long time. This is due to the way a restore builds indexes and how MongoDB can only make one index at a time. check_balance While MongoDB should keep things in balance across a cluster, many things can happen: jumbo chunks, a disabled balancer being, constantly attempting to move the same chunk but failing, and even adding/removing sharding. This alert is for these cases, as an imbalance means some records might get served faster than others. It is purely based on the chunk count that the MongoDB balancer is also based on, which is not necessarily the same as disk usage. check_queues No matter what engine you have selected, a backlog of sustained reads or writes indicates your DB layer is unable to keep up with demand. It is important in these cases to send an alert if the rate is maintained. You might notice this is also in our Prometheus exporter for graphics as both trending and alerting are necessary to watch in a MongoDB system. check_cannary_test This is a typical query for the database and then used to set critical/warning levels based on the latency of the returned query. While not as accurate as full synthetic transactions, queries through the application are good to measure response time expectations and SLAs. check_have_primary If we had an HA event but failed to get back up quickly, it’s important to know if a new primary is causing writes to error on the system. This check simply determines if the replica set has a primary, which means it can handle reads and writes. check_oplog This check is all about how much oplog history you have. This is much like measuring how much history you have in MySQL blogs. The reason this is important is when recovering from a backup and performing a point in time recovery, you can use the current oplog if the oldest timestamp in the oplog is newer than the backup timestamp. As a result, this is normal three times the backup interval you use to guarantee that you have plenty of time to find the newest recovery and then do the recovery. check_index_ratio This is an older metric that modern MongoDB versions will not find useful, but in the past, it was a good way to understand the percentage of queries not handled by an index. check_connect A very basic check to ensure it can connect (and optionally login) to MongoDB and verify the server is working. Status File options These options rarely need to be changed but are present in case you want to store the status on an SHM mount point to avoid actual disk writes. statusfile This is where a copy of the current rs.status, serverStatus and other command data is stored backup-statusfile Like status_file, but status_file is moved here when a new check is done. These two objects can then be compared to find the delta between two checkpoints. max-stale This is the amount of age for which an old file is still valid. Deltas older then this aren’t allowed and exist to protect the system from will assumption when a statusfile is hours or days old. If you have any questions on how to use these parameters, feel free to let us know. In the code, there is also a defaults dictionary for most of these options so that in many cases setting warning and critical level are not needed. [Less]
Posted 1 day ago by Severalnines
Following our popular webinar on MySQL database performance tuning, we’re excited to introduce a new webinar trilogy dedicated to MySQL query tuning. This is an in-depth look into the ins and outs of optimising MySQL queries conducted by Krzysztof ... [More] Książek, Senior Support Engineer at Severalnines. When done right, tuning MySQL queries and indexes can significantly increase the performance of your application as well as decrease response times. This is why we’ll be covering this complex topic over the course of three webinars of 60 minutes each. Dates Part 1: Query tuning process and tools Tuesday, August 30thRegister Part 2: Indexing and EXPLAIN - deep dive Tuesday, September 27thRegister Part 3: Working with the optimizer and SQL tuning Tuesday, October 25thRegister Agenda Part 1: Query tuning process and tools Query tuning process Build Collect Analyze Tune Test Tools tcpdump pt-query-digest Part 2: Indexing and EXPLAIN - deep dive How B-Tree indexes are built? Indexes - MyISAM vs. InnoDB Different index types B-Tree Fulltext Hash Indexing gotchas EXPLAIN walkthrough - query execution plan Part 3: Working with optimizer and SQL tuning Optimizer How execution plans are calculated InnoDB statistics Hinting the optimizer Index hints JOIN order modifications Tweakable optimizations Optimizing SQL Speaker Krzysztof Książek, Senior Support Engineer at Severalnines, is a MySQL DBA with experience in managing complex database environments for companies like Zendesk, Chegg, Pinterest and Flipboard. He’s the main author of the Severalnines blog and webinar series: Become a MySQL DBA. Tags: MySQLquery tuninginnodbmyisam [Less]
Posted 2 days ago by MySQL Performance Blog
This blog post will detail the results of Samsung storage in tpcc-mysql benchmark using Percona Server. I had an opportunity to test different Samsung storage devices under tpcc-mysql benchmark powered by Percona Server 5.7. You can find a summary ... [More] with details here https://github.com/Percona-Lab-results/201607-tpcc-samsung-storage/blob/master/summary-tpcc-samsung.md I have in my possession: Samsung 850 Pro, 2TB: This is a SATA device and is positioned as consumer-oriented, something that you would use in a high-end user desktop. As of this post, I estimate the price of this device as around $430/TB. Samsung SM863, 1.92TB: this device is also a SATA, and positioned for a server usage. The current price is about $600/TB.  Samsung PM1725, 800GB: This is an NVMe device, in a 2.5″ form factor, but it requires a connection to a PCIe slot, which I had to allocate in my server. The device is high-end, oriented for server-side and demanding workloads. The current price is about $1300/TB. I am going to use 1000 warehouses in the tpcc-mysql benchmarks, which corresponds roughly to a data size of 100GB. This benchmark varies the innodb_buffer_pool_size from 5GB to 115GB. With 5GB buffer pool size only a very small portion of data fits into memory, which results in intensive foreground IO reads and intensive background IO writes. With 115GB almost all data fits into memory, which results in very small (or almost zero) IO reads and moderate background IO writes. All buffer pool sizes in the middle of the interval correspond to resulting IO reads and writes. For example, we can see the read to write ratio on the chart below (received for the PM1725 device) with different buffer pool sizes: We can see that for the 5GB buffer pool size we have 56000 read IOPs operations and 32000 write IOPs. For 115GB, the reads are minimal at about 300 IOPS and the background writes are at the 20000 IOPs level. Reads gradually decline with the increasing buffer pool size. The charts are generated with the Percona Monitoring and Management tools. Results Let’s review the results. The first chart shows measurements taken every one second, allowing us to see the trends and stalls. If we take averages, the results are: In table form (the results are in new order transactions per minute (NOTPM)): bp, GB pm1725 sam850 sam863 pm1725 / sam863 pm1725 / sam850 5 42427.57 1931.54 14709.69 2.88 21.97 15 78991.67 2750.85 31655.18 2.50 28.72 25 108077.56 5156.72 56777.82 1.90 20.96 35 122582.17 8986.15 93828.48 1.31 13.64 45 127828.82 12136.51 123979.99 1.03 10.53 55 130724.59 19547.81 127971.30 1.02 6.69 65 131901.38 27653.94 131020.07 1.01 4.77 75 133184.70 38210.94 131410.40 1.01 3.49 85 133058.50 39669.90 131657.16 1.01 3.35 95 133553.49 39519.18 132882.29 1.01 3.38 105 134021.26 39631.03 132126.29 1.01 3.38 115 134037.09 39469.34 132683.55 1.01 3.40 Conclusion The Samsung 850 obviously can’t keep with the more advanced SM863 and PM1725. The PM1725 shows a greater benefit with smaller buffer pool sizes. In cases using large amounts of memory, there is practically no difference with SM863. The reason is that with big buffer pool sizes, MySQL does not push IO subsystem much to use all the PM1725 performance. For the reference, my.cnf file is[mysqld] datadir=/var/lib/mysql socket=/tmp/mysql.sock ssl=0 symbolic-links=0 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # general thread_cache_size=2000 table_open_cache = 200000 table_open_cache_instances=64 back_log=1500 query_cache_type=0 max_connections=4000 # files innodb_file_per_table innodb_log_file_size=15G innodb_log_files_in_group=2 innodb_open_files=4000 innodb_io_capacity=10000 loose-innodb_io_capacity_max=12000 innodb_lru_scan_depth=1024 innodb_page_cleaners=32 # buffers innodb_buffer_pool_size= 200G innodb_buffer_pool_instances=8 innodb_log_buffer_size=64M # tune innodb_doublewrite= 1 innodb_support_xa=0 innodb_thread_concurrency=0 innodb_flush_log_at_trx_commit= 1 innodb_flush_method=O_DIRECT_NO_FSYNC innodb_max_dirty_pages_pct=90 join_buffer_size=32K sort_buffer_size=32K innodb_use_native_aio=0 innodb_stats_persistent = 1 # perf special innodb_adaptive_flushing = 1 innodb_flush_neighbors = 0 innodb_read_io_threads = 16 innodb_write_io_threads = 8 innodb_purge_threads=4 innodb_adaptive_hash_index=0 innodb_change_buffering=none loose-innodb-log_checksum-algorithm=crc32 loose-innodb-checksum-algorithm=strict_crc32 loose-innodb_sched_priority_cleaner=39 loose-metadata_locks_hash_instances=256 [Less]
Posted 2 days ago by Karen Langford
Dear MySQL users, MySQL Enterprise Backup v4.0.2, a new version of the online MySQL backup tool, is now available for download from the My Oracle Support (MOS) website as our latest GA release. This release will be available on eDelivery (OSDC) ... [More] after the next upload cycle. MySQL Enterprise Backup is a commercial extension to the MySQL family of products. MySQL Enterprise Backup v4.0.2 supports only the MySQL Server 5.7.9 and above. For any earlier versions of the MySQL server, please use MySQL Enterprise Backup 3.12 instead. A brief summary of the changes in MySQL Enterprise Backup (MEB) version 4.0.2 is given below. Changes in MySQL Enterprise Backup 4.0.2 (2016-07-26) Functionality Added or Changed * When there were no tables matching the regular expression specified with the --include-table option during a backup operation, mysqlbackup still created a backup, which contained an empty folder for each database on the server. mysqlbackup now throws an error when --include-tables selects no tables to be backed up. (Bug #18114353) * MySQL Enterprise Backup can now backup and restore encrypted InnoDB tables. See Working with Encrypted InnoDB Tables and Options for Working with Encrypted InnoDB Tablespaces for details. Bugs Fixed * When trying to restore a compressed image backup of a server that had separate undo tablespaces residing in the data directory with the copy-back-and-apply-log command, the operation failed at the apply-log phase, as mysqlbackup could not load the undo tablepaces. (Bug #23583961) * Attempts to restore an image backup from the cloud using the --skip-binlog option failed with a "global tail magic mismatch" error. This was because mysqlbackup failed to perform a non-sequential read from the cloud with gaps caused by the skipping of the binary logs. This fix makes sure mysqlbackup can perform such reads. (Bug #23534700) * When a compressed backup was being restored, if the undo logs had been put into separate tablespaces outside of the data directory on the backed up server, they got restored twice, once mistakenly as general tablespaces with the .ibd extension, and once as undo tablespaces without a file extension. This fix makes sure they are restored normally as undo tablespaces only. (Bug #23179194) * An extract operation for an image backup failed with a checksum mismatch error in cases when, during the backup, an InnoDB tablespace file kept growing in size, and mysqlbackup failed to put the correct file size in its file header. (Bug #22905984) References: This issue is a regression of: Bug #22613568. * During a mysqlbackup operation on a compressed backup (that is, the --uncompress option was used), mysqlbackup, in some situations, wrote to the log file multiple instances of the message "ERROR: InnoDB: file write at offset > 4 GB," even though the operation was actually successful. (Bug #22733760) * Occasionally, some files were missing from an image backup created by the --backup-to-image command. It was due to an internal race condition, which this fix eliminates. (Bug #19600687) The complete manual for MEB 4.0.2 is at http://dev.mysql.com/doc/mysql-enterprise-backup/4.0/en/index.html The tool is available for download from Oracle Software Delivery Cloud (http://edelivery.oracle.com/). You can also download the binaries from MOS, https://support.oracle.com Choose the "Patches & Updates" tab, and then use the "Product or Family (Advanced Search)" feature. If you haven't looked at MEB recently, please do so now and let us know how MEB works for you. Your feedback is greatly appreciated! Please report any problems you have at https://bug.oraclecorp.com/ for the product "MySQL Enterprise Backup" Thanks, On behalf MySQL RE team at Oracle Sreedhar S [Less]
Posted 2 days ago by Uber Engineering
Uber Engineering explains the technical reasoning behind its switch in database technologies, from Postgres to MySQL. The post Why Uber Engineering Switched from Postgres to MySQL appeared first on Uber Engineering Blog.
Posted 3 days ago by Justin Swanhart
[this is a repost of my http://shardquery.com blog post, because it did not syndicate to planet.mysql.com]As work on WarpSQL (Shard-Query 3) progresses, it has outgrown MySQL proxy.  MySQL proxy is a very useful tool, but it requires LUA scripting ... [More] , and it is an external daemon that needs to be maintained.  The MySQL proxy module for Shard-Query works well, but to make WarpSQL into a real distributed transaction coordinator, moving the proxy logic inside of the server makes more sense.The main benefit of MySQL proxy is that it allows a script to "inject" queries between the client and server, intercepting the results and possibly sending back new results to the client.  I would like similar functionality, but inside of the server.For example, I would like to implement new SHOW commands, and these commands do not need to be implemented as actual MySQL SHOW commands under the covers.For example, for this blog post I made a new example command called "SHOW PASSWORD"Example "injection" which adds SHOW PASSWORD functionality to the server mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) -- THIS COMMAND DOES NOT EXIST mysql> show password; +-------------------------------------------+ | password_hash | +-------------------------------------------+ | *00A51F3F48415C7D4E8908980D443C29C69B60C9 | +-------------------------------------------+ 1 row in set (0.00 sec) Important - This isn't a MySQL proxy plugin.  There is C++ code in the SERVER to answer that query, but it isn't the normal SHOW command code.  This "plugin" (I put it in quotes because my plan is for a pluggable interface but it isn't added to the server yet) doesn't access the mysql.user table using normal internal access methods. It runs actual SQL inside of the server, on the same THD as the client connection, in the same transaction as the client connection, to get the answer!Problem #1 - Running SQL in the serverThe MySQL C client API doesn't have any methods for connecting to the server from inside of the server, except to connect to the normally available socket interfaces, authenticate, and then issue queries like a normal client.  While it is perfectly possible to connect to the server as a client in this manner, it is sub-optimal for a number of reasons.  First, it requires a second connection to the server, second, it requires that you authenticate again (which requires you have the user's password), and lastly, any work done in the second connection is not party to transactional changes in the first, and vice-versa.The problem is communication between the client and server, which uses a mechanism called VIO.  There was work done a long time ago for external stored procedures, which never made it into the main server that would have alleviated this problem by implementing a in-server VIO layer, and making the parser re-entrant.  That work was done on MySQL 5.1 though.It is possible to run queries without using VIO though.  You simply can't get results back, except to know if the query succeeded or not.  This means it is perfectly acceptable for any command that doesn't need a resultset, basically anything other than SELECT.  There is a loophole however, in that any changes made to the THD stay made to that THD.  Thus, if the SQL executed sets any user variables, then those variables are of course visible after query execution.Solution  - encapsulate arbitrary SQL resultsets through a user variableSince user variables are visible after query execution, the goal is to get the complete results of a query into a user variable, so that the resultset can be accessed from the server.  To accomplish this, first a method to get the results into the variable must be established, and then some data format for communication that is amenable to that method has to be decided upon so that the resultset can be accessed conveniently..With a little elbow grease MySQL can convert any SELECT statement into CSV resultset.  To do so, the following are used:SELECT ... INTO @user_variableA subquery in the FROM clause (for the original query)CONCAT, REPLACE, IFNULL, GROUP_CONCAT (to encode the resultset data)Here is the SQL that the SHOW PASSWORD command uses to get the correct password: select authentication_string as pw, user from mysql.user where concat(user,'@',host) = USER() or user = USER() LIMIT 1Here is the "injected" SQL that the database generates to encapsulate the SQL resultset as CSV: select group_concat( concat('"', IFNULL(REPLACE(REPLACE(`pw`,'"','\\"'),"\n","\\n"),"\N"), '"|"', IFNULL(REPLACE(REPLACE(`user`,'"','\\"'),"\n","\\n"),"\N"), '"' ) separator "\n" ) from ( select authentication_string as pw, user from mysql.user where concat(user,'@',host) = USER() OR user = USER() LIMIT 1 ) the_query into @sql_resultset ; Query OK, 1 row affected (0.00 sec)Here is the actual encapsulated resultset.  If there were more than one row, they would be newline separated. mysql> select @sql_resultset; +----------------+ | @sql_resultset | +----------------+ | ""|"root" | +----------------+ 1 row in set (0.00 sec)Injecting SQL in the serverWith the ability to encapsulate resultsets into CSV in user variables, it is possible to create a cursor over the resultset data and access it in the server.  The MySQL 5.7 pre-parse rewrite plugins, however,  still run inside the parser.  The THD is not "clean" with respect to being able to run a second query.  The parser is not re-entrant.  Because I desire to run (perhaps many) queries between the time a user enters a query and the server actually answers the query (perhaps with a different query than the user entered!) the MySQL 5.7 pre-parse rewrite plugin infrastructure doesn't work for me.I modified the server, instead, so that there is a hook in do_command() for query injections.  I called it conveniently query_injection_point() and the goal is to make it a new plugin type, but I haven't written that code yet.  Here is the current signature for query_injection_point(): bool query_injection_point( THD* thd, COM_DATA *com_data, enum enum_server_command command, COM_DATA* new_com_data, enum enum_server_command* new_command );It has essentially the same signature as dispatch_command(), but it provides the ability to replace the command, or keep it as is.  It returns true when the command has been replaced.Because it is not yet pluggable, here is the code that I placed in the injection point: /* TODO: make this pluggable */ bool query_injection_point(THD* thd, COM_DATA *com_data, enum enum_server_command command, COM_DATA* new_com_data, enum enum_server_command* new_command) { /* example rewrite rule for SHOW PASSWORD*/ if(command != COM_QUERY) { return false; } /* convert query to upper case */ std::locale loc; std::string old_query(com_data->com_query.query,com_data->com_query.length); for(unsigned int i=0;icom_query.length;++i) { old_query[i] = std::toupper(old_query[i], loc); } if(old_query == "SHOW PASSWORD") { std::string new_query; SQLClient conn(thd); SQLCursor* stmt; SQLRow* row; if(conn.query("pw,user", "select authentication_string as pw,user from mysql.user " \ "where concat(user,'@',host) = USER() or user = USER() LIMIT 1", &stmt)) { if(stmt != NULL) { if((row = stmt->next())) { new_query = "SELECT '" + row->at(0) + "' as password_hash"; } } else { return false; } } else { return false; } /* replace the command sent to the server */ if(new_query != "") { Protocol_classic *protocol= thd->get_protocol_classic(); protocol->create_command( new_com_data, COM_QUERY, (uchar *) strdup(new_query.c_str()), new_query.length() ); *new_command = COM_QUERY; } else { if(stmt) delete stmt; return false; } if(stmt) delete stmt; return true; } } /* don't replace command */ return false; }SQLClientYou will notice that the code access the mysql.user table using SQL, using the SQLClient, SQLCursor, and SQLRow objects.  These are the objects that wrap around encapsulating the SQL into a CSV resultset, and actually accessing the result set.  The interface is very simple, as you can see from the example.  You create a SQLClient for a THD (one that is NOT running a query already!) and then you simply run queries and access the results.The SQLClient uses a stored procedure to methodically encapsulate the SQL into CSV and then provides objects to access and iterate over the data that is buffered in the user variable.  Because MySQL 5.7 comes with the sys schema, I placed the stored procedure into it, as there is no other available default database that allows the creation of stored procedures.  I called it sys.sql_client().Because the resultset is stored as text data, the SQLRow object returns all column values as std::string.What's next?I need to add a proper plugin type for "SQL injection plugins".  Then I need to work on a plugin for parallel queries.  Most of the work for that is already done, actually, at least to get it into an alpha quality state.  There is still quite a bit of work to be done though.You can find the code in the internal_client branch of my fork of MySQL 5.7:http://github.com/greenlion/warpsql-server [Less]
Posted 3 days ago by MySQL Performance Blog
Percona announces the GA release of Percona XtraBackup 2.4.4 on July 25th, 2016. You can download it from our download site and from apt and yum repositories. Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for ... [More] companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups. New Features: Percona XtraBackup has been rebased on MySQL 5.7.13. Bugs Fixed: Percona XtraBackup reported the difference in the actual size of the system tablespace and the size which was stored in the tablespace header. This check is now skipped for tablespaces with autoextend support. Bug fixed #1550322. Because Percona Server 5.5 and MySQL 5.6 store the LSN offset for large log files at different places inside the redo log header, Percona XtraBackup was trying to guess which offset is better to use by trying to read from each one and compare the log block numbers and assert lsn_chosen == 1 when both LSNs looked correct, but they were different. Fixed by improving the server detection. Bug fixed #1568009. Percona XtraBackup didn’t correctly detect when tables were both compressed and encrypted. Bug fixed #1582130. Percona XtraBackup would crash if the keyring file was empty. Bug fixed #1590351. Backup couldn’t be prepared when the size in cache didn’t match the physical size. Bug fixed #1604299. Free Software Foundation address in copyright notices was outdated. Bug fixed #1222777. Backup process would fail if the datadir specified on the command-line was not the same as one that is reported by the server. Percona XtraBackup now allows the datadir from my.cnf override the one from SHOW VARIABLES. xtrabackup prints a warning that they don’t match, but continues. Bug fixed #1526467. With upstream change of maximum page size from 16K to 64K, the size of incremental buffer became 1G. Which increased the requirement to 1G of RAM in order to prepare the backup. While in fact there is no need to allocate such a large buffer for smaller pages. Bug fixed #1582456. Backup process would fail on MariaDB Galera cluster operating in GTID mode if binary logs were in non-standard directory. Bug fixed #1517629. Other bugs fixed: #1583717, #1583954, and #1599397. Release notes with all the bugfixes for Percona XtraBackup 2.4.4 are available in our online documentation. Please report any bugs to the launchpad bug tracker. [Less]
Posted 3 days ago by Severalnines
Networking is critical in MySQL, it is a fundamental resource to manage access to the server from client applications and other replication peers. The behaviour of a containerized MySQL service is determined by how the MySQL image is spawned with ... [More] “docker run” command. With Docker single-host networking, a MySQL container can be run in an isolated environment (only reachable by containers in the same network), or an open environment (where the MySQL service is totally exposed to the outside world) or the instance simply runs with no network at all. In the previous two blog posts, we covered the basics of running MySQL in a container and how to build a custom MySQL image. In today’s post, we are going to cover the basics of how Docker handles single-host networking and how MySQL containers can leverage that. 3 Types of Networks By default, Docker creates 3 networks on the machine host upon installation: $ docker network ls NETWORK ID NAME DRIVER 1a54de857c50 host host 1421a175401a bridge bridge 62bf0f8a1267 none nullEach network driver has its own characteristic, explained in the next sections. Host Network The host network adds a container on the machine host’s network stack. You may imagine containers running in this network are connecting to the same network interface as the machine host. It has the following characteristics: Container’s network interfaces will be identical with the machine host. Only one host network per machine host. You can’t create more. You have to explicitly specify “--net=host” in the “docker run” command line to assign a container to this network. Container linking, “--link mysql-container:mysql” is not supported. Port mapping, “-p 3307:3306” is not supported. Let’s create a container on the host network with “--net=host”: $ docker run \ --name=mysql-host \ --net=host \ -e MYSQL_ROOT_PASSWORD=mypassword \ -v /storage/mysql-host/datadir:/var/lib/mysql \ -d mysqlWhen we look into the container’s network interface, the network configuration inside the container is identical to the machine host: [machine-host]$ docker exec -it mysql-host /bin/bash [container-host]$ ip a 1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth0: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 00:0c:29:fa:f6:30 brd ff:ff:ff:ff:ff:ff inet 192.168.55.166/24 brd 192.168.55.255 scope global eth0 valid_lft forever preferred_lft forever inet6 fe80::20c:29ff:fefa:f630/64 scope link valid_lft forever preferred_lft forever 3: docker0: mtu 1500 qdisc noqueue state DOWN group default link/ether 02:42:93:50:ee:c8 brd ff:ff:ff:ff:ff:ff inet 172.17.0.1/16 scope global docker0 valid_lft forever preferred_lft forever inet6 fe80::42:93ff:fe50:eec8/64 scope linkIn this setup, the container does not need any forwarding rules in iptables since it’s already attached to the same network as the host. Hence, port mapping using option “-p” is not supported and Docker will not manage the firewall rules of containers that run in this type of network. If you look at the listening ports on the host machine, port 3306 is listening as it should: [machine-host]$ netstat -tulpn | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 25336/mysqldHaving a MySQL container running on the Docker host network is similar to having a standard MySQL server installed on the host machine. This is only helpful if you want to dedicate the host machine as a MySQL server, however managed by Docker instead. Now, our container architecture can be illustrated like this: Containers created on host network are reachable by containers created inside the default docker0 and user-defined bridge. Bridge network Bridging allows multiple networks to communicate independently while keep separated on the same physical host. You may imagine this is similar to another internal network inside the host machine. Only containers in the same network can reach each other including the host machine. If the host machine can reach the outside world, so can the containers. There are two types of bridge networks: Default bridge (docker0) User-defined bridge Default bridge (docker0) The default bridge network, docker0 will be automatically created by Docker upon installation. You can verify this by using the “ifconfig” or “ip a” command. The default IP range is 172.17.0.1/16 and you can change this inside /etc/default/docker (Debian) or /etc/sysconfig/docker (RedHat). Refer to Docker documentation if you would like to change this. Let’s jump into an example. Basically, if you don’t explicitly specify “--net” parameter in the “docker run” command, Docker will create the container under the default docker0 network: $ docker run \ --name=mysql-bridge \ -p 3307:3306 \ -e MYSQL_ROOT_PASSWORD=mypassword \ -v /storage/mysql-bridge/datadir:/var/lib/mysql \ -d mysqlAnd when we look at the container’s network interface, Docker creates one network interface, eth0 (excluding localhost): [machine-host]$ docker exec -it mysql-container-bridge /bin/bash [container-host]$ ip a 1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 4: eth0: mtu 1500 qdisc noqueue state UP group default link/ether 02:42:ac:11:00:02 brd ff:ff:ff:ff:ff:ff inet 172.17.0.2/16 scope global eth0 valid_lft forever preferred_lft forever inet6 fe80::42:acff:fe11:2/64 scope link valid_lft forever preferred_lft foreverBy default, Docker utilises iptables to manage packet forwarding to the bridge network. Each outgoing connection will appear to originate from one of the host machines’s own IP addresses. The following is the machine’s NAT chains after the above container was started: [machine-host]$ iptables -L -n -t nat Chain POSTROUTING (policy ACCEPT) target prot opt source destination MASQUERADE all -- 172.17.0.0/16 0.0.0.0/0 MASQUERADE tcp -- 172.17.0.2 172.17.0.2 tcp dpt:3306 Chain DOCKER (2 references) target prot opt source destination DNAT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3307 to:172.17.0.2:3306The above rules allows port 3307 to be exposed on the machine host based on the port mapping option “-p 3307:3306” in the “docker run” command line. If we look at the netstat output on the host, we can see MySQL is listening on port 3307, owned by docker-proxy process: [machine-host]$ netstat -tulpn | grep 3307 tcp6 0 0 :::3307 :::* LISTEN 4150/docker-proxyAt this point, our container setup can be illustrated below: The default bridge network supports the use of port mapping and container linking to allow communication between containers in the docker0 network. If you would like to link another container, you can use the “--link” option in the “docker run” command line. Docker documentation provides extensive details on how the container linking works by exposing environment variables and auto-configured host mapping through /etc/hosts file. User-defined bridge Docker allows us to create custom bridge network, a.k.a user-defined bridge network (you can also create user-defined overlay network, but we are going to cover that in the next blog post). It behaves exactly like the docker0 network, where each container in the network can immediately communicate with other containers in the network. Though, the network itself isolates the containers from external networks. The big advantage of having this network is that all containers have the ability to resolve the container’s name. Consider the following network: [machine-host]$ docker network create mysql-networkThen, create 5 mysql containers under the user-defined network: [machine-host]$ for i in {1..5}; do docker run --name=mysql$i --net=mysql-network -e MYSQL_ROOT_PASSWORD=mypassword -d mysql; doneNow, login into one of the containers (mysql3): [machine-host]$ docker exec -it mysql3 /bin/bashWe can then ping all containers in the network without ever linking them: [mysql3-container]$ for i in {1..5}; do ping -c 1 mysql$i ; done PING mysql1 (172.18.0.2): 56 data bytes 64 bytes from 172.18.0.2: icmp_seq=0 ttl=64 time=0.151 ms --- mysql1 ping statistics --- 1 packets transmitted, 1 packets received, 0% packet loss round-trip min/avg/max/stddev = 0.151/0.151/0.151/0.000 ms PING mysql2 (172.18.0.3): 56 data bytes 64 bytes from 172.18.0.3: icmp_seq=0 ttl=64 time=0.138 ms --- mysql2 ping statistics --- 1 packets transmitted, 1 packets received, 0% packet loss round-trip min/avg/max/stddev = 0.138/0.138/0.138/0.000 ms PING mysql3 (172.18.0.4): 56 data bytes 64 bytes from 172.18.0.4: icmp_seq=0 ttl=64 time=0.087 ms --- mysql3 ping statistics --- 1 packets transmitted, 1 packets received, 0% packet loss round-trip min/avg/max/stddev = 0.087/0.087/0.087/0.000 ms PING mysql4 (172.18.0.5): 56 data bytes 64 bytes from 172.18.0.5: icmp_seq=0 ttl=64 time=0.353 ms --- mysql4 ping statistics --- 1 packets transmitted, 1 packets received, 0% packet loss round-trip min/avg/max/stddev = 0.353/0.353/0.353/0.000 ms PING mysql5 (172.18.0.6): 56 data bytes 64 bytes from 172.18.0.6: icmp_seq=0 ttl=64 time=0.135 ms --- mysql5 ping statistics --- 1 packets transmitted, 1 packets received, 0% packet loss round-trip min/avg/max/stddev = 0.135/0.135/0.135/0.000 msIf we look into the resolver setting, we can see Docker configures an embedded DNS server: [mysql3-container]$ cat /etc/resolv.conf search localdomain nameserver 127.0.0.11 options ndots:0The embedded DNS server maintains the mapping between the container name and its IP address, on the network the container is connected to, as in this case it is mysql-network. This feature facilitates node discovery in the network and is extremely useful in building a cluster of MySQL containers using MySQL clustering technology like MySQL replication, Galera Cluster or MySQL Cluster. At this point, our container setup can be illustrated as the following: Default vs User-defined Bridge The following table simplifies the major differences between these two networks: Area Default bridge (docker0) User-defined bridge Network deployment Docker creates upon installation Created by user Container deployment Default to this network Explicitly specify “--net=[network-name]” in the “docker run” command Container linking Allows you to link multiple containers together and send connection information from one to another by using “--link [container-name]:[service-name]”. When containers are linked, information about a source container can be sent to a recipient container. Not supported Port mapping Supported e.g, by using “-p 3307:3306” Supported e.g, by using “-p 3307:3306” Name resolver Not supported (unless you link them) All containers in this network are able to resolve each other’s container name to IP address. Version <1.10 use /etc/hosts, >=1.10 use embedded DNS server. Packet forwarding Yes, via iptables Yes, via iptables Example usage for MySQL MySQL standalone MySQL replication, Galera Cluster, MySQL Cluster (involving more than one MySQL container setup) No network We can also create a container without any network attached to it by specifying “--net=none” in the “docker run” command. The container is only accessible through interactive shell. No additional network interface will be configured on the node. Consider the following: [machine-host]$ docker run --name=mysql0 --net=none -e MYSQL_ROOT_PASSWORD=mypassword -d mysqlBy looking at the container’s network interface, only localhost interface is available: [machine-host]$ docker exec -it mysql0 /bin/bash [mysql0-container]$ ip a 1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft foreverContainer in network none indicates it can’t join any network. Nevertheless, the MySQL container is still running and you can access it directly from the shell using mysql client command line through localhost or socket: [mysql0-container]$ mysql -uroot -pmypassword -h127.0.0.1 -P3306 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.13 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>Example use cases to run MySQL container in this network are MySQL backup verification by testing the restoration process, preparing the backup created using, e.g., Percona Xtrabackup or testing queries on different version of MySQL servers. At this point, our containers setup can be illustrated as the following: This concludes today’s blog. In the next blog post, we are going to look into multiple host networking (using overlay networks) together with Docker Swarm, an orchestration tool to manage containers on multiple machine hosts. Tags: dockerMySQLnetworkingcontainersDatabase [Less]
Posted 3 days ago by Jan Kneschke
Decoding binary protocols in python Decoding binary protocols like the MySQL Client/Server Protocol or MySQL's new X Protocol involves taking a sequence of bytes and turning them into integers. In python the usually workhorse for this task is ... [More] struct.unpack() It takes a sequence of bytes and a format-string and returns a tuple of decoded values. In the case of the MySQL Client/Server protocol the integers are (mostly) little-endian, unsigned and we can use: format description len(payload): raise IndexError() if isinstance(payload, (bytes, bytearray)): charp = payload return _unpack_int_le(charp, l) else: raise TypeError(type(payload)) See also: http://docs.cython.org/en/latest/src/tutorial/pure.html With these hints given the _unpack_int_le() from above gets translated to: __pyx_v_v = 0; __pyx_v_sh = 0; __pyx_t_1 = __pyx_v_l; for (__pyx_t_2 = 0; __pyx_t_2 < __pyx_t_1; __pyx_t_2+=1) { __pyx_v_n = __pyx_t_2; __pyx_v_v = (__pyx_v_v | ((__pyx_v_payload[__pyx_v_n]) << __pyx_v_sh)); __pyx_v_sh = (__pyx_v_sh + 8); } return PyInt_from_unsigned_long(__pyx_v_v) This gains another 50%: byte length time (s) 1 0.224607 2 0.157248 3 0.158877 4 0.163004 8 0.165769 While the code is pure python with some annotations it sadly comes with some overhead in the generated C-code. Static typed cython cython has two more ways of specifying the static types: move the static types into a .pxd file write a .pyx file directly In our case the result would be the same which is why I go with the first approach: # unpack_int_le_5.pxd import cython @cython.locals(v=cython.ulong, sh=cython.ulong, n=cython.ulong) cdef unsigned long _unpack_int_le(unsigned char *payload, unsigned long l) @cython.locals(l=cython.ulong, charp=cython.p_uchar) cpdef unpack_int_le(payload, unsigned long l) # unpack_int_le_5.py def _unpack_int_le(payload, l): v = sh = n = 0 for n in range(l): v |= payload[n] << sh sh += 8 return v def unpack_int_le(payload, l): if l > len(payload): raise IndexError() if isinstance(payload, (bytes, bytearray)): charp = payload return _unpack_int_le(charp, l) else: raise TypeError(type(payload)) $ cythonize ./unpack_int_le_5.py && \ gcc -shared -pthread -fPIC -fwrapv \ -O3 -Wall -fno-strict-aliasing \ -I/usr/include/python2.7 \ -o unpack_int_le_5.so unpack_int_le_5.c byte length time (s) 1 0.084414 2 0.0818369 3 0.083369 4 0.084229 8 0.0980709 One can see that the longer byte-length leads to slightly higher runtime. Just cast it One last step and we are at the final form of the optimizations which mirror what one would have written if one would have written the code in C directly. If we are running on a little endian platform, we can save shift-or-loop and just cast the byte-sequence into the right integer directly. Note to show how the code looks in a pyx file, the previous py and pxd files have been merged into a pyx. # unpack_int_le_6.pyx import sys cdef unsigned int is_le is_le = sys.byteorder == "little" cdef unsigned long _unpack_int_le(const unsigned char *payload, unsigned long l): cdef unsigned long *u32p cdef unsigned short *u16p cdef unsigned long long *u64p cdef unsigned long v = 0 cdef unsigned long sh = 0 cdef unsigned long n = 0 if is_le: if l == 1: return payload[0] elif l == 2: u16p = payload return u16p[0] elif l == 4: u32p = payload return u32p[0] elif l == 8: u64p = payload return u64p[0] v = sh = n = 0 for n in range(l): v |= payload[n] << sh sh += 8 return v cpdef unsigned long unpack_int_le(payload, unsigned long l): cdef unsigned char *charp if l > len(payload): raise IndexError() if isinstance(payload, (bytes, bytearray)): charp = payload return _unpack_int_le(charp, l) else: raise TypeError(type(payload)) $ cythonize ./unpack_int_le_6.pyx && \ gcc -shared -pthread -fPIC -fwrapv \ -O3 -Wall -fno-strict-aliasing \ -I/usr/include/python2.7 \ -o unpack_int_le_6.so unpack_int_le_6.c byte length time (s) 1 0.0812111 2 0.0792191 3 0.082288 4 0.079355 8 0.0791218 Note the 3-byte case hits the loop again and is slightly slower. Conclusion Between the first attempt with struct.unpack() and our last with cython we have quite a speed difference: byte length time (s) time (s) speedup 1 0.466556 0.0812111 5.75x 2 0.422064 0.0792191 5.34x 3 1.17804 0.082288 14.36x 4 0.439113 0.079355 5.56x 8 0.448069 0.0791218 5.67x We got a 5x speed up and didn't had to write a single line of python C-API code. [Less]