In this webinar, Baron Schwartz introduces the integration of Go and MySQL. He discusses idiomatic database/sql code, available drivers for MySQL and time saving tips and tricks.
If you did not have a chance to join the webinar, the slide deck is embedded below. You can also register for a recording here.
Using MySQL with Go - Golang's Database/SQL Driver from VividCortex
The MySQL/MariaDB optimiser likes to know things like the cardinality of an index – that is, the number of distinct values the index holds. For a PRIMARY KEY, which only has unique values, the number is the same as the number of rows. For an indexed column that is boolean (such as yes/no) the cardinality would be 2.
There’s more to it than that, but the point is that the optimiser needs some statistics from indexes in order to try and make somewhat sane decisions about which index to use for a particular query. The statistics also need to be updated when a significant number of rows have been added, deleted, or modified.
In MyISAM, ANALYZE TABLE does a tablescan where everything is tallied, and the index stats are updated. InnoDB, on the other hand, has always done “index dives”, looking at a small sample and deriving from that. That can be ok as a methodology, but unfortunately the history is awkward. The …
[Read more]Jeremy Cole has an excellent article on why a mysqld instance will swap despite proper memory settings on modern hardware here. A quick hand-wave of a summary goes: in modern architecture, every process is, by default, limited to a 1/Nth of the available memory on a machine, where N is the number of cores, and will start to swap on disk when all the available memory in that 1/Nth is consumed.
The eventual solution for a large, single-instance mysqld-running machine is to enable numa_interleave in the mysqld_safe section of the config:
Implicitly, when that flag is set, mysqld is being started by mysqld_safe with a numactl flag that will spread the memory …
[Read more]You might have encountered situations where you had to kill some specific select queries that were running for long periods and choking the database. This post will go into more detail with an example of report query offloading.
Report query (select) offloading to a slave server is a common practice to reduce the workload of the master DB server. The long running selects will be executed in the slave for report generation. I have observed in many cases where the slave used to get delayed or the slave DB encounters a slowdown due to some heavy long-running orphaned selects from some wrong reports.
There are two main ways to kill queries in MySQL: 1. use custom scripts that match on a regular expression, or 2. use a tool written and supported by Percona that is designed to kill queries based on matching conditions. Below is one script that will help you to kill those queries. The script will take the process list from MySQL and …
[Read more]November 19, 2014 By Severalnines
Following the release of ClusterControl 1.2.8 with a range of cool new features, we have now also published a new ClusterControl User Guide that provides all the steps, tips & tricks to follow in order to successfully deploy, monitor, manage and scale database clusters.
This user guide predominantly covers ClusterControl with MySQL-based clusters, namely:
New features in ClusterControl 1.2.8 include:
Optimizing MySQL performance requires the ability to inspect production query traffic. If you’re not seeing your application’s production workload, you’re missing a vital part of the picture. In particular, there are lots of performance optimizations your systems might be doing that you’re not aware of. One of these is using prepared statements for queries.
What Are Prepared Statements?
A prepared statement is a SQL statement with parameter placeholders which is sent to the database server and prepared for repeated execution. It’s a performance optimization as well as a security measure; it protects against attacks such as SQL injection, where an attacker hijacks unguarded string concatenation to produce malicious queries.
In MySQL, as well as in most databases, you first send the SQL to the server and ask for it to be prepared with placeholders for bind parameters. The server responds with a statement ID. You …
[Read more]Hi Apache Friends!
We just released new versions of XAMPP for all platforms with latest PHP versions: 5.5.19 and 5.6.3. We simplified the versioning for XAMPP releases and we now base the version number on the bundled PHP version, so you can more easily tell what is in the stack.
In addition to the component upgrades below, we also included a new beta Welcome page, which includes new tutorials. We will continue to add more and would love to get your feedback!
The following guides are available for Windows and Linux in the current versions:
MySQL table alterations can interrupt production traffic causing bad customer experience or in worst cases, loss of revenue. Not all DBAs, developers, syadmins know MySQL well enough to avoid this pitfall. DBAs usually encounter these kinds of production interruptions when working with upgrade scripts that touch both application and database or if an inexperienced admin/dev engineer perform the schema change without knowing how MySQL operates internally.
Truths
* Direct MySQL ALTER table locks for duration of change
(pre-5.6)
* Online DDL in MySQL 5.6 is not always online and may incurr
locks
* Even with Percona Toolkit‘s pt-online-schema-change there
are several workloads that can experience blocking
Here on the Percona MySQL …
[Read more]Starting with MySQL 5.6 there is an INNODB_METRICS table available in INFORMATION_SCHEMA which contains some additional information than provided in the SHOW GLOBAL STATUS output – yet might be more lightweight than PERFORMANCE_SCHEMA.
Too bad INNODB_METRICS was designed during the Oracle-Sun split under MySQL leadership and so it covers only InnoDB counters. I think this would be a great replacement to all counters that are currently provided though SHOW STATUS – it captures more information such as providing MIN/MAX counts for variables as well as providing the type of the counter (whenever it is current or commutative) as well as human readable comment – describing what such counter means.
The examples of data you can get only from the INNODB_METRICS table includes information about InnoDB Page Splits and merging (which can …
[Read more]Recently I had a need to determine the session value of a MySQL plugin variable in a core file. Here I use the word plugin variable to refer to MySQL system variables provided by plugins. While this is not a very difficult task, it is not as straight forward as printing a global variable. It took some time to figure out the layout of the plugin variables and get the needed information. This short article is to share this gdb tip with other interested MySQL developers and support engineers.
In this article, let us inspect the session value of the plugin variable innodb_strict_mode, which is of type boolean. Quite obviously, this plugin variable is provided by InnoDB storage engine.
The Quick Answer
…
[Read more]