Planet MySQL

‘Woz on your mind?’ Share your questions for Steve Wozniak during his Percona Live keynote!

Here’s your chance to get on stage with Woz! Sort of. Apple co-founder and Silicon Valley icon and philanthropist Steve Wozniak will participate in a moderated Q&A on creativity and innovation April 14 during the Percona Live MySQL Conference and Expo in Santa Clara, California.

Woz once said that he never intended to change the world. That was the other Steve, Steve Jobs.

“I didn’t want to start this company,” Woz told the Seattle Times of Apple’s beginnings in a 2006 interview. “My goal wasn’t to make a ton of money. It was to build good computers. I only started the company when I realized I could be an engineer forever.”

What would you ask Woz if given the opportunity?

“Woz, what first sparked your interest in engineering?”
“Hey Woz, how did you come up with the design for the first Apple?”
“Woz, what do you see as the next big thing in personal computers?”
“Hi Woz, what’s the deal with your giant vacuum tube watch?”

Now it’s your turn! Ask a question in the comments below and be sure to include your Twitter handle – or your Facebook page or LinkedIn profile. If we use your question, then your profile and question will be displayed on the giant screen behind Woz on stage as it’s being asked during his big keynote! How cool is that?

Want to be there in person? See Woz speak for just $5! That’s $70 off the regular admission price! Just use the promo code “KEY” at registration under the “Expo Hall and Keynote Pass” selection. Following Woz’s keynote, be sure to stop by the Percona booth, say “hello, Tom,” and I’ll give you a limited-edition Percona t-shirt.

In the meantime, help spread the word! Please share this tweet:

“Woz on your mind?” Tweet @Percona your questions for Apple’s Steve Wozniak who speaks April 14 at #PerconaLive! http://ow.ly/KTmES

Do that, then follow @Percona and I’ll send a DM for your address and will ship a t-shirt right to your door. See you at the conference!

The post ‘Woz on your mind?’ Share your questions for Steve Wozniak during his Percona Live keynote! appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

Paramiko Update Tutorial

There are situations when you need to update the python-paramiko library that is bundled with MySQL Workbench. This may be because you prefer
using the latest cutting edge versions, are just curious, or you can’t wait for a new Workbench version.

To update, first download the latest python-paramiko release from https://github.com/paramiko/paramiko/releases. After downloading and extracting the archive, you’ll see a paramiko folder inside of the extracted folder. This folder needs to be copied into the appropriate MySQL Workbench folder on the system.

* For Windows, this location is usually
WB_INSTALLDIR/python/site-packages/paramiko

* For OS X, this will be
/Applications/MySQLWorkbench.app/Contents/Resources/Libraries/paramiko

* For Linux, you should use your repository manager (yum, apt-get, etc.), but if it doesn’t have the latest paramiko version then you
should first uninstall your old version, download the latest paramiko, cd into the paramiko directory, and then execute “sudo python setup.py
install” to install system wide paramiko.

Note: since Paramiko 1.12, to use it, you also need the ecdsa package which can be downloaded from here:
https://github.com/warner/python-ecdsa/releases.
The same method applies; download and extract the contents of the archive, and you’ll see a ecdsa folder that should be placed next to the paramiko folder.

Please keep in mind that switching paramiko this way can break MySQL Workbench, so always make a backup first.


PlanetMySQL Voting: Vote UP / Vote DOWN

FoundationDB is acquired by Apple: My thoughts

TechCrunch reported yesterday that Apple has acquired FoundationDB. And while I didn’t see any mention if this news on the FoundationDB website, they do have an announcement saying: “We have made the decision to evolve our company mission and, as of today, we will no longer offer downloads.”

This is an unfortunate development – I have been watching FoundationDB technology for years and was always impressed in terms of its performance and features. I was particularly impressed by their demo at last year’s Percona Live MySQL and Expo. Using their Intel NUC-based Cluster, I remember Ori Herrnstadt showing me how FoundationDB handles single-node failure as well as recovery from complete power-down – very quickly and seamlessly. We have borrowed a lot of ideas from this setup for our Percona XtraDB Cluster Demos.

I think it was a great design to build a distributed, shared-nothing transaction aware key value store, and then have an SQL Layer built on top of it. I did not have a chance to test it hands-on, though. Such a test would have revealed the capabilities of the SQL optimizer – the biggest challenge for distributed relational database systems.

My hope was to see, over time, this technology becoming available as open source (fully or partially), which would have dramatically increased adoption by the masses. It will be interesting to see Apple’s long-terms plans for this technology.

In any case it looks like FoundationDB software is off limits. If you are an existing FoundationDB customer looking for alternatives, we here at Percona would be happy to help evaluate options and develop a migration strategy if necessary.

The post FoundationDB is acquired by Apple: My thoughts appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

Simple MySQL replication example on Kubernetes

I'm happy to show a simple example I developed for running simple MySQL replication on Kubernetes.

The reason I've done this is that I'm in the process of preparing a presentation for Percona Live and decided to start out with basic replication. I figured if I get that working, getting Galera replication using Percona XtraDB Cluster will be even easier since SST makes it easier and less complicated when a node joins the cluster versus a slave connecting to a master and having to concern itself with binary log position and getting a snapshot that corresponds to that.

Using my blog post to easily build a Kubernetes cluster with VMware kubernetes_cluster_vmware all one has to do to use these files is clone the mysql_replication_kubernetes repo

The README in the repo will explain how to run this simple proof-of-concept.

Example

On the master (where I usually create pods, services, replication controllers...) create the master pod and service:


core@master ~/mysql_project $ kubectl create -f mysql-master.json mysql-master core@master ~/mysql_project $ kubectl create -f mysql-master-service.json mysql-master

Create the slave pod and service:

core@master ~/mysql_project $ kubectl create -f mysql-slave.json mysql-slave core@master ~/mysql_project $ kubectl create -f mysql-slave-service.json mysql-slave

Verify things are running:

core@master ~/mysql_project $ kubectl get pods,services POD IP CONTAINER(S) IMAGE(S) HOST LABELS STATUS mysql-master 10.244.19.6 mysql-master capttofu/mysql_master_kubernetes:latest 192.168.1.19/192.168.1.19 name=mysql-master Running mysql-slave 10.244.88.3 mysql capttofu/mysql_slave_kubernetes:latest 192.168.1.21/192.168.1.21 name=mysql-slave Running NAME LABELS SELECTOR IP PORT kubernetes component=apiserver,provider=kubernetes <none> 10.100.0.2 443 kubernetes-ro component=apiserver,provider=kubernetes <none> 10.100.0.1 80 mysql-master name=mysql-master name=mysql-master 10.100.154.2 3306 mysql-slave name=mysql-slave name=mysql-slave 10.100.194.208 3306

Connect to the master (see the master-service's IP). Notice here that the same container that the master is run using is used simply for the MySQL client:

core@node_01 ~ $ docker run -it capttofu/mysql_master_kubernetes mysql -u root -proot -h 10.100.154.2 Welcome to the MySQL monitor. Commands end with ; or <snip> Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show slave hosts; +-----------+--------------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+--------------+------+-----------+--------------------------------------+ | 24698 | feec6e4827bc | 3306 | 29002 | dfcb0b0a-d4a6-11e4-b2db-02420af45803 | +-----------+--------------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec)

Connect to the slave:

core@node_01 ~ $ docker run -it capttofu/mysql_master_kubernetes mysql -u root -proot -h 10.100.194.208 <snip> Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.100.154.2 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 2803 Relay_Log_File: mysqld-relay-bin.000003 Relay_Log_Pos: 3015 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: mysql.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2803 Relay_Log_Space: 102663 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 29002 Master_UUID: d79ef03a-d4a6-11e4-b2db-02420af41306 Master_Info_File: /var/lib/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: 1 row in set (0.01 sec)

Excellent! A master slave setup!

Summary

This is a proof of concept. There is so much more to be done with this example pertaining to regular replication:

  • How to add slaves (if replicas for slaves is increased) to the master after the binary log has incremented
  • How to automatically perform a slave snapshot/backup and send that to the joining slave
  • Volumes...
  • Intelligent connection pooling for an application using this setup

Kubernetes is a rapidly developing project and expect many things to change and projects to emerge that solve many of these issues-- I may not be aware of something that has already done what my POC here accomplishes. In any case, this should prove to be a useful example for those who are MySQL users who want to acquaint themselves with Kubernetes!


PlanetMySQL Voting: Vote UP / Vote DOWN

Command line prompt

The mysql> command-line prompt is iconic, shown in countless documentation pages, forum posts, tutorials and manuals. It’s immediately identifiable – one look, and you immediately know the context in which commands are being executed. So it’s only with good reason that we would consider changing the prompt value to something else, and Daniël van Eeden provided a compelling suggestion to modify this to provide user, host and database context. Because the mysql prompt is user-configurable, this is easy to do dynamically:

mysql> prompt something> PROMPT set to 'something> ' something> select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) something>

Using the special character sequences defined at the bottom of one of the documentation pages, the prompt can supply with a number of different and useful information which provides context in the command line:

something> prompt \u@\h [\d]> PROMPT set to '\u@\h [\d]> ' root@localhost [(none)]> prompt \h:\p [\d]> PROMPT set to '\h:\p [\d]> ' localhost:3310 [(none)]> prompt \D> PROMPT set to '\D> ' Mon Mar 23 17:09:58 2015>

The prompt can also be set as a command-line argument to the client, or via the configuration file read by the client at startup. Users who manage many servers and need information identifying to which instance a given command line client is connected might want to leverage the configuration file option, as this will affect all client instances started by default.

We actually changed the default prompt to “\u@\h [\d]> ” as Daniël proposed, but pulled it back out of 5.7 after using it internally for a few days and realizing a few issues. This post aims to document why we reverted to the original mysql> prompt, the options we’ve considered as alternatives, and a call for feedback.

Copying SQL

The mysql> prompt length aligns with the prompt length for continuation prompts, where queries span multiple lines. This makes it easy to copy SQL commands from terminals:

mysql> SELECT 1, -> NOW();

When using prompts with differing length, copy operations pick up extraneous characters, requiring users to manipulate the contents to transform it back into executable SQL:

root@localhost [(none)] > SELECT 1, -> NOW();

An option would be to make the buffer the continuation prompt to the same length as the original prompt. With larger and dynamic prompts, the results might be unsatisfactory – the example above takes up 25 characters of my 80-character-wide terminal.

(None)

MySQL allows users to connect without specifying a default database, and that’s not going to change anytime soon. This results in “[(none)]” being displayed in the prompt. When I first encountered the prompt change, I had to ask what the “none” meant – I had no idea what that meant. Informed users will only need to be told once (or never, if they are smarter than me), but it’s bound to confuse less-experienced users.

Misleading user name

The MySQL ACL system makes displaying user names an interesting exercise. With wildcards, anonymous users and proxy accounts, there’s any number of possible values for “user name” to display. Do you want to know the user name you supplied to the client, the user name portion of the account used to authenticate, or the user name portion of the account associated with the privileges you have? They are different:

shell> bin\mysql.exe -ublah -P3310 Welcome to the MySQL monitor. Commands end with ; or \g. ... blah@localhost [(none)] > show grants; +--------------------------------------+ | Grants for @localhost | +--------------------------------------+ | GRANT USAGE ON *.* TO ''@'localhost' | +--------------------------------------+ 1 row in set (0.00 sec)

Similarly for proxy users:

pa@localhost [(none)] > SELECT USER(), CURRENT_USER(), @@session.proxy_user; +--------------+----------------+----------------------+ | USER() | CURRENT_USER() | @@session.proxy_user | +--------------+----------------+----------------------+ | pa@localhost | pb@localhost | 'pa'@'localhost' | +--------------+----------------+----------------------+ 1 row in set (0.00 sec) pa@localhost [(none)] > show grants; +----------------------------------------+ | Grants for pb@localhost | +----------------------------------------+ | GRANT USAGE ON *.* TO 'pb'@'localhost' | +----------------------------------------+ 1 row in set (0.00 sec)

Like the [(none)] value, experienced MySQL users will understand the difference between USER(), CURRENT_USER() and @@session.proxy_user, but this can easily confuse users new to MySQL.

Server vs. client host

I’m used to seeing user@host define MySQL accounts, but that’s not what \u@\h actually provides. As noted above, the user is the user name supplied to the client, rather than the account. But the host is the server host, not the host of the account.

shell> ipconfig ... Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : IPv4 Address. . . . . . . . . . . : 192.168.2.64 ... shell> bin\mysql -utodd -h192.168.2.54 Welcome to the MySQL monitor. Commands end with ; or \g. ... todd@192.168.2.54 [(none)] > select user(); +---------------+ | user() | +---------------+ | todd@GRAPHITE | +---------------+ 1 row in set (0.02 sec)

Note that using the \U option shows the full user name/host returned from USER():

todd@192.168.2.54 [(none)] > prompt \U > PROMPT set to '\U > ' todd@GRAPHITE >

It makes sense to display the host a client is connected to, rather than the host component of the account used to connect. But it does allow for confusion about what is being displayed.

Other common use cases

Daniël’s suggestion works great for DBAs who manage multiple MySQL installations and need to ensure they are executing commands against the intended instance.  It’s less useful for single-host deployments, or for use cases like MySQL Sandbox, where multiple instances may all exist locally.  I frequently have 5+ MySQL instances running locally for various testing purposes, and the proposed default prompt wouldn’t help me distinguish between them at all.  Such use cases would be better served by a prompt value like “\h:\p > “:

 

root@localhost [(none)] > prompt \h:\p > PROMPT set to '\h:\p > ' localhost:3310 > Alternatives

The need Daniël identified is something we want to address, but the issues listed above gave us second thoughts about changing the default prompt. We’ve identified some possible alternatives, which I’m listing below:

Add prompt help

The definition of special sequences isn’t easy to find in the documentation, and even some experienced MySQL users are unfamiliar with the options that exist. Those that do know the possibilities for customizing prompts sometimes find it difficult to quickly recall the special sequences they might need. We’d like to add this information to the client, so that it is readily available:

mysql> prompt ? *** PROMPT FORMAT OPTIONS *** \d - The default database \u - Your user name \h - The server host ... Define prompt macros

We know that \u@\h [\d]> reflects the needs of a common use case, as does \h:\p >. Another common use case likely involves outputting the date/time in the prompt for tracking purposes. Another common use case might be to simply include the default database.

We can provide macros which are easily remembered and cycled through, so that users don’t have to build their own prompts for these common use cases. For example:

mysql> prompt \1 PROMPT set to '\u@\h using \d> ' root@localhost using (none)> prompt \2 PROMPT set to '\h:\p> ' localhost:3310> prompt \3 PROMPT set to '\D> ' Fri Mar 20 14:08:59 2015> prompt Returning to default PROMPT of mysql> mysql> Multi-line prompts

One possible solution is to leverage multi-line prompts, so that contextual information is provided on one line, while the familiar mysql> prompt is retained:

root@localhost [(none)] > prompt \u@\h [\d]\nmysql> PROMPT set to '\u@\h [\d]\nmysql> ' root@localhost [(none)] mysql> SELECT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) root@localhost [(none)] mysql>

This makes copying SQL easy, but still displays context information for a specific use case, and which may confuse less-experienced users.

Add support for prompt in mysql_config_editor

As noted earlier, it’s easy enough to define a custom prompt dynamically, as a command-line option, or even globally within a configuration file. It would be useful if the custom prompts could be defined on a per-connection basis, and stored with other connection options in mysql_config_editor. Doing so would allow users to connect to a specific instance, and have a specific (non-default) prompt automatically associated.

Conclusion

Please let us know if you think the reasoning behind our decision to leave the prompt as-is is unsound in any way, or if you have particular affinity for any of the proposed alternatives. Thanks!


PlanetMySQL Voting: Vote UP / Vote DOWN

SQL, ANSI Standards, PostgreSQL and MySQL

I have recently been working with the Donors Choose Open Data Set which happens to be in PostgreSQL. Easy enough to install and load the data in PostgreSQL, however as I live and breath MySQL, lets load the data into MySQL.

And here is where start our discussion, first some history.

SQL History

SQL – Structure Query Language is a well known common language for communicating with Relational Databases (RDBMS). It is not the only language I might add, having both used many years ago and just mentioned QUEL at a Looker Look and Tell event in New York. It has also been around since the 1970s making it; along with C; one of oldest in general use programming languages today.

SQL became an ANSI standard in 1986, and an ISO standard in 1987. The purpose of a standard is to provide commonality when communicating or exchanging information; in our case; a programming language communicating with a RDBMS. There have been several iterations of the standard as functionality and syntax improves. These are commonly referred to as SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008 and SQL:2011.

And so, with SQL being a standard it means that what we can do in PostgreSQL should translate to what we can do in MySQL.

SQL Communication

Both products provide a Command Line Interface (CLI) client tool for SQL communication, mysql for MySQL and psql for PostgreSQL. No surprises there. Both use by default the semicolon ; as a SQL statement terminator, and both CLI tools use \q as a means to quit and exit the tool. Certainly not a standard but great for syntax compatibility.

DDL Specification

Our journey begins with defining tables.

DROP TABLE

Both products SQL syntax support DROP TABLE. Infact, both support the DROP TABLE [IF EXISTS] syntax.

DROP TABLE donorschoose_projects; DROP TABLE IF EXISTS donorschoose_projects; CREATE TABLE

Both support CREATE TABLE.

Both support defining columns in the typical format <column_name> <datatype>, and both support the NOT NULL attribute. Talking about specific datatypes for columns is a topic on its own and so I discuss this later.

The PostgreSQL syntax was a table option WITHOUT OIDS which is not valid in MySQL. It is also obsolescent syntax in PostgreSQL 9.3. From the PostgreSQL manual “This optional clause specifies whether rows of the new table should have OIDs (object identifiers) assigned to them. The default is to have OIDs. Specifying WITHOUT OIDS allows the user to suppress generation of OIDs for rows of a table. This may be worthwhile for large tables … Specifying WITHOUT OIDS also reduces the space required to store the table on disk by 4 bytes per row of the table, thereby improving performance.”

In this example as this is just for testing, dropping the WITHOUT OIDS syntax creates a mutually compatible syntax.

Comments

Both MySQL and PostgreSQL support -- as an inline comment in an SQL statement. No need to strip those out.

ALTER TABLE

Both support ALTER TABLE ADD CONSTRAINT syntax which in our example is used to define the PRIMARY KEY, however while the syntax remains the same, the choice of datatype affects the outcome.

The following works in both products when the datatype is CHARACTER(32). More about CHARACTER() later.

ALTER TABLE donorschoose_projects ADD CONSTRAINT pk_donorschoose_projects PRIMARY KEY(_projectid);

In our example dataset, the primary key is defined with a TEXT datatype, and in MySQL this fails.

ERROR 1170 (42000): BLOB/TEXT column '_projectid' used in key specification without a key length

As the data in the dataset for primary keys by further analysis is indeed a 32 byte hexadecimal value, this is changed to CHARACTER(32) to be compatible for this data loading need. This however is an important key difference in any migration process with other data sets.

Side Note

Both products support the definition of the PRIMARY KEY in the CREATE TABLE syntax two different ways.

CREATE TABLE demo_pk1 (id character(32) NOT NULL PRIMARY KEY); CREATE TABLE demo_pk2 (id character(32) NOT NULL, PRIMARY KEY(id)); CREATE INDEX

Both use CREATE INDEX syntax however with our sample dataset, this is the first observed difference in syntax with provided sample SQL statements.

PostgresSQL

CREATE INDEX projects_schoolid ON projects USING btree (_schoolid);

MySQL
The USING <type> qualifier must appear before the ON <table>.

CREATE INDEX USING btree projects_schoolid ON projects (_schoolid);

In both products USING btree is an optional syntax (for minimum compatibility) purposes so removing this provides a consistency.

Data Types

The following data types are defined in the PostgreSQL example data set. Each is discussed to identify a best fit in MySQL. For reference:

character

This data type is for a fixed width character field and requires a length attribute. MySQL supports CHARACTER(n) syntax for compatibility, however generally CHAR(n) is the preferred syntax. Indeed, PostgreSQL also supports CHAR(n).

The following showing both variants is valid in both products.

CREATE TABLE demo_character(c1 CHARACTER(1), c2 CHAR(1)); varchar/character varying

While this dataset does not use these datatypes, they are critical in the general conservations of character (aka string) types. This refers to a variable length string.

While character varying is not a valid MySQL syntax, varchar is compatible with both products.

CREATE TABLE demo_varchar(vc1 VARCHAR(10)); text

In PostgresSQL, text is used for variables of undefined length. The maximum length of a field is 1GB as stated in the FAQ.

In MySQL however TEXT only stores 2^16 characters (64K). The use of LONGTEXT is needed to support the full length capacity in PostgeSQL. This store 2^32 characters (~4GB).

Of all the complexity of this example dataset, the general use of text will be the most difficult to modify to a more applicable VARCHAR or TEXT datatype when optimizing in MySQL.

integer

PostgreSQL uses the integer datatype for a signed 4 byte integer value. MySQL supports the same syntax, however generally prefers to refer to the shorter INT syntax. Both products support both overall.

mysql> CREATE TABLE demo_integer(i1 INTEGER, i2 INT); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO demo_integer VALUES (1,-1); Query OK, 1 row affected (0.05 sec) mysql> SELECT * FROM demo_integer; +------+------+ | i1 | i2 | +------+------+ | 1 | -1 | +------+------+ 1 row in set (0.00 sec) demo=# CREATE TABLE demo_integer(i1 INTEGER, i2 INT); CREATE TABLE demo=# INSERT INTO demo_integer VALUES (1,-1); INSERT 0 1 demo=# SELECT * FROM demo_integer; i1 | i2 ----+---- 1 | -1 (1 row)

And just to note the boundary of this data type.

mysql> TRUNCATE TABLE demo_integer; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO demo_integer VALUES (2147483647, -2147483648); Query OK, 1 row affected (0.04 sec) mysql> SELECT * FROM demo_integer; +------------+-------------+ | i1 | i2 | +------------+-------------+ | 2147483647 | -2147483648 | +------------+-------------+ 1 row in set (0.00 sec) demo=# TRUNCATE TABLE demo_integer; TRUNCATE TABLE demo=# INSERT INTO demo_integer VALUES (2147483647, -2147483648); INSERT 0 1 demo=# SELECT * FROM demo_integer; i1 | i2 ------------+------------- 2147483647 | -2147483648 (1 row)

The difference is in out-of-bounds value management, and here MySQL defaults suck. You can read my views at DP#4 The importance of using sql_mode.

demo=# TRUNCATE TABLE demo_integer; TRUNCATE TABLE demo=# INSERT INTO demo_integer VALUES (2147483647 + 1, -2147483648 - 1); ERROR: integer out of range demo=# SELECT * FROM demo_integer; i1 | i2 ----+---- (0 rows) mysql> TRUNCATE TABLE demo_integer; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO demo_integer VALUES (2147483647 + 1, -2147483648 - 1); Query OK, 1 row affected, 2 warnings (0.07 sec) mysql> SELECT * from demo_integer; +------------+-------------+ | i1 | i2 | +------------+-------------+ | 2147483647 | -2147483648 | +------------+-------------+ 1 row in set (0.00 sec)

While not in this dataset, both support the bigint data type. While the PostgreSQL docs indicate bigint is 8 bytes, testing with PostgresSQL 9.3 failed. Something to investigate more later.

demo=# CREATE TABLE demo_bigint(i1 BIGINT); CREATE TABLE demo=# INSERT INTO demo_bigint VALUES (2147483647 + 1), (-2147483648 - 1); ERROR: integer out of range mysql> CREATE TABLE demo_bigint(i1 BIGINT); Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO demo_bigint VALUES (2147483647 + 1), (-2147483648 - 1); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * from demo_bigint; +-------------+ | i1 | +-------------+ | 2147483648 | | -2147483649 | +-------------+ 2 rows in set (0.01 sec)

And for reference, both products support smallint, a 2-byte integer.

Each product has additional integer data types.

numeric

For a fixed-precision number, PostgreSQL uses numeric but supports decimal.It would not be surprising to know that MySQL uses DECIMAL and for compatibility supports NUMERIC.

This leads to a side-bar discussion on knowing your data-types for your product. In a recent interview for a MySQL Engineer, a candidate (with SQL Server experience) provided a code example defining the NUMERIC datatype. I knew it was technically valid in MySQL syntax, but never actually seen this in use. When I asked the candidate for what was the syntax commonly used for a fixed-precision datatype they were unable to answer.

real/double precision

This dataset does not include these data types, however for reference, PostgresSQL uses real for 4 bytes, and double precision for 8 bytes. MySQL uses float for 4 bytes, and double for 8 bytes. MySQL however supports both PostgreSQL syntax options, however PostgreSQL supports float, but not double.

demo=# CREATE TABLE demo_floatingpoint(f1 FLOAT, f2 REAL, d1 DOUBLE, d2 DOUBLE PRECISION); ERROR: type "double" does not exist LINE 1: ...TE TABLE demo_floatingpoint(f1 FLOAT, f2 REAL, d1 DOUBLE, d2... demo=# CREATE TABLE demo_floatingpoint(f1 FLOAT, f2 REAL, d2 DOUBLE PRECISION); CREATE TABLE mysql> CREATE TABLE demo_floatingpoint(f1 FLOAT, f2 REAL, d1 DOUBLE, d2 DOUBLE PRECISION); Query OK, 0 rows affected (0.07 sec) date

Both PostgreSQL and MySQL use the date data type.

timestamp

Both PostgreSQL and MySQL use the timestamp data type to store date/time values. However, there is a difference in both precision and implementation here.

In PostgresSQL, timestamp supports a date before EPOCH, while in MySQL it does not. MySQL uses the DATETIME datatype.

Using PostgresSQL timestamp and MySQL DATETIME, both support microsecond precision. MySQL however only started to provide this in MySQL 5.6.

A key difference in column definition is the PostgreSQL timestamp without time zone syntax, used in our example dataset. Analysis of data loading will determine the impact here.

boolean

SQL:1999 calls for a Boolean datatype, and both PostgreSQL and MySQL support defining a column as BOOLEAN. MySQL however implicitly converts this to a SIGNED TINYINT, and any future DDL viewing shows this reference.

When referencing boolean, in PostgreSQL WHERE column_name = TRUE or WHERE column_name = t retrieves a true value. In MySQL WHERE column_name = TRUE or WHERE column_name = 1. When you SELECT a boolean, in PostgresSQL the answer is ‘t’, in MySQL, the answer is 1.

demo=# CREATE TABLE demo_boolean (b1 boolean); CREATE TABLE demo=# INSERT INTO demo_boolean VALUES (TRUE),(FALSE); INSERT 0 2 demo=# SELECT * FROM demo_boolean; b1 ---- t f (2 rows) mysql> CREATE TABLE demo_boolean (b1 boolean); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO demo_boolean VALUES (TRUE),(FALSE); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM demo_boolean; +------+ | b1 | +------+ | 1 | | 0 | +------+ 2 rows in set (0.00 sec) Other Data Types

Only the data types in this example have been reviewed.

Other syntax

In our sample SQL script, there is psql specific syntax to show a debugging line with \qecho .... For compatibility these are removed.

The loading of data with the \COPY <table_name> FROM PSTDIN WITH CSV HEADER is PostgreSQL specific and so loading the data is a future topic.

Finally, the VACUUM ANALYZE <table_name> command is also PostgreSQL specific and removed. This is a means effectively of optimizing and analyzing the table.

Both PostgreSQL and MySQL have an ANALYZE command, however the syntax is different, with the required TABLE keyword in MySQL.

PostgresSQL

ANALYZE donorschoose_projects; ANALYZE TABLE donorschoose_projects; ERROR: syntax error at or near "table"

MySQL

ANALYZE donorschoose_projects; ERROR 1064 (42000): You have an error in your SQL syntax;... ANALYZE TABLE donorschoose_projects;

MySQL has an OPTIMIZE TABLE syntax, however while technically valid syntax this is not compatible with the default storage table InnoDB.

mysql> OPTIMIZE TABLE donorschoose_projects; +----------------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------------------+----------+----------+-------------------------------------------------------------------+ | test.donorschoose_projects | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.donorschoose_projects | optimize | status | OK | +----------------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.32 sec)
PlanetMySQL Voting: Vote UP / Vote DOWN

How to Manage the World’s Top Open Source Databases: ClusterControl 1.2.9 Features Webinar Replay

Thanks to everyone who attended and participated in this week’s webinar on 'New Features - ClusterControl 1.2.9 Release'. If you missed the sessions or would like to watch the webinar again & browse through the slides, they are now available online.

Our speaker this time was Johan Andersson, CTO, Severalnines.

Watch the replay

Introducing the new ClusterControl 1.2.9 - with live demo from Severalnines AB

 

Read the slides

Slides: Introducing the new ClusterControl 1.2.9 - with live demo from Severalnines AB

 

With over 7,000 users to date, ClusterControl is the leading, platform independent automation and management solution for the MySQL, MongoDB and now Postgres databases, its latest main feature. 

Highlights in ClusterControl 1.2.9 include:

  • Support for PostgreSQL Servers
  • Advanced HAProxy Configurations and Built-in Stats
  • Hybrid Replication with Galera Clusters
  • Galera Replication Traffic Encryption
  • Encrypted Communication between ClusterControl and MySQL-based systems
  • Query Deadlock Detection in MySQL-based systems
  • Bootstrap Galera Cluster
  • Restore of Backups
  • New UI theme
  • RPC interface to ClusterControl
  • Chef Recipe and Puppet Manifest for ClusterControl
  • Zabbix Plugin for ClusterControl

 

RELATED BLOGS

 

ABOUT CLUSTERCONTROL

Setting up, maintaining and operating a database cluster can be tricky. ClusterControl gives you the power to deploy, manage, monitor and scale entire clusters efficiently and reliably. ClusterControl supports a variety of MySQL-based clusters (Galera, NDB, 5.6 Replication), MariaDB as well as MongoDB/TokuMX-based clusters - and now Postgres.

 

Blog category: Tags:
PlanetMySQL Voting: Vote UP / Vote DOWN

InnoDB全文索引:N-gram Parser

InnoDB默认的全文索引parser非常合适于Latin,因为Latin是通过空格来分词的。但对于像中文,日文和韩文来说,没有这样的分隔符。一个词可以由多个字来组成,所以我们需要用不同的方式来处理。在MySQL 5.7.6中我们能使用一个新的全文索引插件来处理它们:n-gram parser.

什么是N-gram?

在全文索引中,n-gram就是一段文字里面连续的n个字的序列。例如,用n-gram来对”信息系统”来进行分词,得到的结果如下:

N=1 : '信', '息', '系', '统'; N=2 : '信息', '息系', '系统'; N=3 : '信息系', '息系统'; N=4 : '信息系统';

 如何在InnoDB中使用N-gram Parser?

N-gram parser是默认加载到MySQL中并可以直接使用的。我们只需要在DDL中创建全文索引时使用WITH PARSER ngram。比如,下面的SQL语句在MySQL 5.7.6及更高版本上可以运行。

mysql > CREATE TABLE articles ( FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(100), FULLTEXT INDEX ngram_idx(title) WITH PARSER ngram ) Engine=InnoDB CHARACTER SET utf8mb4; Query OK, 0 rows affected (0.06 sec) mysql> # ALTER TABLE articles ADD FULLTEXT INDEX ngram_idx(title) WITH PARSER ngram; mysql> # CREATE FULLTEXT INDEX ngram_idx ON articles(title) WITH PARSER ngram;

我们引入了一个新的全局变量叫ngram_token_size。由它来决定n-gram中n的大小,也就是词的大小。它的默认值是2,这个时候,我们使用的是bigram。它的合法的取值范围是1到10。现在,我们很自然会想到一个问题:实际应用中应该如何设置ngram_token_size值的大小呢?当然,我们推荐使用2。但是你也可以通过如下这个简单的规则来可以选择任何合法的值:设置到你希望能查询到的最小的词的大小。如果你想查询到单个字,那么我们需要设置为1。 ngram_token_size的值设置的越小,全文索引占用的空间也越小。一般来说,查询正好等于ngram_token_size的词,速度会更快,但是查询比它更长的词或短语,则会变慢。

N-gram分词处理

N-gram parser和系统默认的全文索引parser有如下不同点:

  1. 词大小检查:因为有了ngram_token_size,所以innodb_ft_min_token_sizeinnodb_ft_max_token_size将不适用于n-gram。
  2. 无用词(stopword)处理:通常,对于一个新的词,我们会查找stopwords表,看是否有匹配的词。如果有,这个词就不会加入到全文索引中。但是在n-gram中,我们会查找stopwords表,看是否包含里面的词。这样处理的原因是,在中日韩的文本中,有很多没有意义的字符,词语和标点符号。比如,如果我们把‘的’加入到stopwords表中,那么对于句子‘信息的系统’,在默认情况下我们分词结果为‘信息’,‘系统’。其中‘息的’和‘的系’被过滤掉了。

我们可以通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHEINFORMATION_SCHEMA.INNODB_FT_TABLE_TABLE来查询哪些词在全文索引里面。这是一个非常有用的调试工具。如果我们发现一个包含某个词的文档,没有如我们所期望的那样出现在查询结果中,那么这个词可能是因为某些原因不在全文索引里面。比如,它含有stopword,或者它的大小小于ngram_token_size等等。这个时候我们就可以通过查询这两个表来确认。下面是一个简单的例子:

mysql> INSERT INTO articles (title) VALUES ('信息系统'); Query OK, 1 row affected (0.01 sec) mysql> SET GLOBAL innodb_ft_aux_table="test/articles"; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +--------+--------------+-------------+-----------+--------+----------+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +--------+--------------+-------------+-----------+--------+----------+ | 信息 | 1 | 1 | 1 | 1 | 0 | | 息系 | 1 | 1 | 1 | 1 | 3 | | 系统 | 1 | 1 | 1 | 1 | 6 | +--------+--------------+-------------+-----------+--------+----------+ 3 rows in set (0.00 sec)

 N-gram查询处理

文本查询(Text Searches)

  • 在自然语言模式(NATURAL LANGUAGE MODE)下,文本的查询被转换为n-gram分词查询的并集。例如,(‘信息系统’)转换为(‘信息 息系 系统’)。下面一个例子:
    mysql> INSERT INTO articles (title) VALUES ('信息系统'), ('信息 系统'), ('信息的系统'), ('信息'), ('系统'), ('息系'); Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST ('信息系统' IN NATURAL LANGUAGE MODE); +------------+-----------------+ | FTS_DOC_ID | title | +------------+-----------------+ | 1 | 信息系统 | | 6 | 息系 | | 2 | 信息 系统 | | 3 | 信息的系统 | | 4 | 信息 | | 5 | 系统 | +------------+-----------------+ 6 rows in set (0.01 sec)
  • 在布尔模式(BOOLEAN MODE),文本查询被转化为n-gram分词的短语查询。例如,(‘信息系统’)转换为(“‘信息 息系 系统'”)。
    mysql> SELECT * FROM articles WHERE MATCH(title) AGAINST('信息系统' IN BOOLEAN MODE); +------------+--------------+ | FTS_DOC_ID | title | +------------+--------------+ | 1 | 信息系统 | +------------+--------------+ 1 row in set (0.00 sec)

通配符查询(Wildcard Searches)

  • 如果前缀的长度比ngram_token_size小,那么查询结果将返回在全文索引中所有以这个词作为前缀的n-gram的词。
    mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST ('信*' IN BOOLEAN MODE); +------------+-----------------+ | FTS_DOC_ID | title | +------------+-----------------+ | 1 | 信息系统 | | 2 | 信息 系统 | | 3 | 信息的系统 | | 4 | 信息 | +------------+-----------------+ 4 rows in set (0.00 sec)
  • 如果前缀的长度大于等于ngam_token_size,那么这个查询则转换为一个短语(phrase search),通配符则被忽略。例如,(‘信息*’)转换为(‘”信息”‘),(‘信息系*’)转换为(‘”信息 息系”‘)。
    mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST ('信息*' IN BOOLEAN MODE); +------------+-----------------+ | FTS_DOC_ID | title | +------------+-----------------+ | 1 | 信息系统 | | 2 | 信息 系统 | | 3 | 信息的系统 | | 4 | 信息 | +------------+-----------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST ('信息系*' IN BOOLEAN MODE); +------------+--------------+ | FTS_DOC_ID | title | +------------+--------------+ | 1 | 信息系统 | +------------+--------------+ 1 row in set (0.00 sec)

短语查询(Phrase Searches)

  • 短语查询则被转换为n-gram分词的短语查询。比如,(‘信息系统’)转换为(‘”信息 息系 系统”‘)。
    mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST('"信息系统"' IN BOOLEAN MODE); +------------+--------------+ | FTS_DOC_ID | title | +------------+--------------+ | 1 | 信息系统 | +------------+--------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM articles WHERE MATCH (title) AGAINST ('"信息 系统"' IN BOOLEAN MODE); +------------+---------------+ | FTS_DOC_ID | title | +------------+---------------+ | 2 | 信息 系统 | +------------+---------------+ 1 row in set (0.01 sec)

如果您想了解更多关于InnoDB全文索引的详细内容,可以参考用户手册中InnoDB全文索引的部分,还有Jimmy在Dr. Dobb上的精彩文章。如果您想了解更多关于n-gram的详细内容,则可以参考用户手册中n-gram parser的部分。

我们很高兴在MySQL 5.7全文索引中增强对中日韩文的支持,这也是我们工作中很重要的部分,希望这个功能对大家有帮助。如果您有任何问题,可以在本blog中进行评论,提交一个服务需求,或者提交一个bug报告

最后,感谢您使用MySQL!


PlanetMySQL Voting: Vote UP / Vote DOWN

A few interesting findings on MariaDB and MySQL scalability, multi-table OLTP RO

It’s been almost a year since I benchmarked MariaDB and MySQL on our good old 4 CPU / 32 Cores / 64 Threads Sandy Bridge server. There seem to be a few interesting things happened since that time.

  • MySQL 5.6.23 peak throughput dropped by ~8% compared to 5.6.14. Looks like this regression appeared in MySQL 5.6.21.
  • 10.0.18 (git snapshot) peak threads increased by ~20% compared to 10.0.9 and reached parity with 5.6.23 (not with 5.6.20 though).
  • 10.1.4 (git snapshot) and 5.7.5 are the champions (though 10.1.4 was usually 1-5% faster). Both have similar peaks @ 64 threads. They seem to be pushing this system limits, since both have capacity to scale more.
  • 5.7.6 has serious (~50%) scalability regression compared to 5.7.5. There is heavy LOCK_plugin mutex contention, which seem to be caused by query rewrite plugin framework. I couldn’t find way to disable it. Hope this will be fixed in coming releases.

Raw data available at google doc.


PlanetMySQL Voting: Vote UP / Vote DOWN

MariaDB: The Differences, Expectations, and Future

Thu, 2015-03-26 07:48russelljtdyer

For my new book, Learning MySQL and MariaDB, Monty Widenius, one of the founders of MySQL and MariaDB, graciously contributed a Foreword. It's about six pages long and an interesting read for those who are familiar with MySQL and MariaDB. Of particular interest to newcomers to MariaDB, is the excerpt below on his perspective of MariaDB and his vision for its future, as well as MySQL.

Excerpt from Foreword of Learning MySQL and MariaDB (O'Reilly 2015)

"Regarding my hopes and expectations for the MariaDB database system, I’m working at the foundation to ensure that we get more companies actively involved in the development of MariaDB. That’s something we lacked during the history of MySQL. We want to develop something that will satisfy everyone—not only now, but for the future. To do that, we need more organizations involved. We’re happy to see Google involved in the MariaDB Foundation. I’d like to see 10 or 15 companies as significant as Google involved. That’s something they’ve managed to do at FOSS, the Free and Open Source Software Foundation. They have several companies that assist in development. That’s their strength. Their weakness is that they don’t have one company coordinating the development of software. My hope is that the MariaDB Foundation will act as a coordinator for the effort, but with many companies helping. That would benefit everyone. It is this collaborative effort that I don’t expect from Oracle regarding MySQL. That’s the difference and advantage of MariaDB. With Oracle, there’s no certainty in the future of the open source code of MySQL. With MariaDB, by design it will always be open source and everything they do will be open source. The foundation is motivated and truly want to be more closely aligned with open source standards.

"The MariaDB Foundation was created to be a sanctuary. If something goes wrong in the MariaDB Corporation, the Foundation can guarantee that the MariaDB software will remain open—always. That’s its main role. The other role is to ensure that companies that want to participate in developing MariaDB software can do so on equal terms as anyone else because the foundation is there. So if someone creates and presents a patch for MariaDB software, they can submit it to be included in the next release of MariaDB. With many other open source projects, it’s difficult to get a patch included in the software. You have to struggle and learn how to conform to their coding style. And it’s even harder to get the patch accepted. In the case of MySQL with Oracle, it could be blocked by Oracle. The situation is inherently different with MariaDB."

"For example, if Percona, a competitor of MariaDB Corporation, wants to add a patch to MariaDB software that will help their background program XtraBackup to run better, but the management of MariaDB Corporation doesn’t like that it would be helping their competitor, it doesn’t matter. MariaDB Corporation has no say in which patches are adopted. If the Foundation accepts the patch, it’s added to the software. The Foundation review patches on their technical merits only, not based on any commercial agenda."

"The open source projects that survived are those that were created for practical reasons. MySQL wasn’t in the beginning the best database solution. People complained that it didn’t have many features at that time. However, it was always practical. It solved problems and met the needs of developers and others. And it did so better than other solutions that were supposedly better choices. We did that by actively listening to people and with a willingness to make changes to solve problems. Our goal with MariaDB is to get back to those roots and be more interactive with customers and users. By this method, we can create something that might not be perfect for everyone, but pretty good."

"As for the future, if you want MariaDB to be part of your professional life, I can assure you that we will do everything possible to support and develop the software. We have many brilliant people who will help to ensure MariaDB has a long future."

"In the near term, I think that MariaDB version 10.1 will play a large role in securing the future of MariaDB. It offers full integration with Galera cluster—an add-on for MariaDB for running multiple database servers for better performance—because of the new encryption features. That’s important. In recent months, all other technologies have been overshadowed with security concerns because the systems of some governments and major companies have been hacked. Having good encryption could have stopped most of those attacks from achieving anything. These improvements will change the perception that open source databases are not secure enough. Many commercial database makers have said that MySQL and MariaDB are not secure, and they have been able to convince some businesses to choose a commercial solution instead as a result. With MariaDB 10.1, though, we can prove easily that their argument is not true. So that’s good. If you’ve chosen to use MariaDB, you can make this point when you’re asked about the difference between MySQL and MariaDB, and you can feel good about your choice over the long term for this same reason."

"Looking at the future, many companies are leery about using commercial database software because they don’t know for sure if the compiled code contains backdoors for accessing the data or if there is some special way in which the software is using encryption that could allow hackers to get at their databases. On the other hand, countries like Russia and China question whether open source databases are secure. The only way we can assure them of that is to provide access to the source code, and that means they must use open source software. So I do hope and expect that in the future we will see MySQL and MariaDB growing rapidly in these countries and similar organizations, because we can address their concerns when commercial solutions cannot. Ironically, a more transparent software system is preferred by a less transparent government. It’s better not only for less transparent organizations, but also for those that want to keep their databases more secure. This applies to an organization that wants to keep their data private and doesn’t want someone else such as a hacker, or a competitor, a government to have access to their data."

—Monty Widenius in Málaga, Spain, January 2015

Learning MySQL and MariaDB may be pre-ordered now. Booksellers will start shipping the first week of April 2015.

Tags: CommunityNewbie
PlanetMySQL Voting: Vote UP / Vote DOWN

How to Tell If It’s MySQL Swapping

On servers that are into the swap file and have multiple processes running, I often wonder if it’s MySQL that is in the swap or some other process.  With Linux this is a fairly easy thing to figure out.  Below is the format of a command to do just that:

echo 0 $(cat /proc/`pidof process`/smaps | grep TYPE | awk '{print $2}' | sed 's#^#+#') | bc

 

In the above command, TYPE refers to what type of Memory you want to examine.  Below are some of the options for this value:

TYPE Explanation Private Private memory used by the process.  You may be able to determine memory leaks by looking at this value over time. Pss Proportional Set Size.  This is the Rss adjusted for sharing.  For example, if a process has 10 MB private and 50 MB shared between other 5 processes, then the Pss value is (10 + 50/5) which is 20 MB. Referenced Amount of memory currently marked as referenced or accessed. Rss Resident memory usage.  This is all memory the process uses, including all memory this process shares with other processes.  It does not include swap. Shared This is memory that this process shares with other processes. Size The virtual size of the memory, although not always helpful. Swap The swap memory used by the process.

 

Below are examples you can use on the Linux command line:

echo 0 $(cat /proc/`pidof mysqld`/smaps | grep Rss | awk '{print $2}' | sed 's#^#+#') | bc echo 0 $(cat /proc/`pidof mysqld`/smaps | grep Swap | awk '{print $2}' | sed 's#^#+#') | bc

 

While there are other ways to get this information, this is one of the simplest I have found.


PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Statement Digests

Decoupling Statement Digests From Performance Schema

MySQL Statement Digests are a feature originally introduced as part of the MySQL Performance Schema in MySQL 5.6 to aggregate statement statistics based on the normalized statements executed within the server (for additional info, see here).

Statement Digests were previously only available as a part of the MySQL Performance Schema. In other words, they were only available when Performance Schema was enabled in a running MySQL Server. Now, from the MySQL 5.7.4 DMR on, this Statement Digest feature has been made available irrespective of the Performance Schema.

Why This Is Important

This change allows other MySQL Server Components and Plugins to make use of Statement Digests without being dependent on the Performance Schema configuration. One example of this is the new MySQL Query Rewrite plugin (available in the MySQL 5.7.6 DMR).

Some Additional Notes

There is no change in Performance Schema itself and it still works as it did before with regards to statement aggregation and summary information. All we’ve done is move the Statement Digest functionality to the SQL layer so that it can be used for features unrelated to Performance Schema.

The following MySQL Server options and status variables remain valid and unchanged within the Performance Schema context:

We also added a new max_digest_length MySQL Server option in order to provide greater flexibility in the behavior of Statement Digests. For more details on this new option, please see my previous post on the topic.

If you would like some additional details on this work, you can see the Worklog entry here. If you have any questions, please feel free to post them here on the blog post or in a support ticket.

As always, THANK YOU for using MySQL!


PlanetMySQL Voting: Vote UP / Vote DOWN

How to Cluster Atlassian Server Apps in your Data Center - JIRA, Confluence, Stash with MySQL Galera Cluster

Atlassian apps are widely used by development teams to facilitate software development and collaboration. For larger teams who require enterprise-grade tools, you might want to host the applications in your own data center and cluster them for high availability and performance. 

In this blog post, we are going to deploy a redundant active-passive setup for JIRA with clustered MySQL and NFS shared storage. Note that some Atlassian applications (JIRA, Confluence and Stash) already has built-in clustering support, and these require you to have a Data Center license. In this blog, we will show you how to cluster the regular/standalone JIRA server product by deploying an active JIRA server with an additional passive stand-by instance for failover purposes.

The following is our architecture diagram:

All hosts are running Debian Wheezy 64bit, we will use Galera Cluster for MySQL as database backend. Iptables is disabled to simplify the deployment. JIRA and MySQL services are connected via a virtual IP to provide single point of access.

Our setup consists of 4 servers:

  • lb1: HAproxy + keepalived (master)
  • lb2: HAproxy + keepalived (backup) + ClusterControl + garbd
  • jira1: JIRA (active) + database server
  • jira2: JIRA (passive) + database server

 

Prerequisite

1. Ensure all hosts have the following host definition inside /etc/hosts:

192.168.50.100 jira virtual-ip 192.168.50.101 lb1.local lb1 192.168.50.102 lb2.local lb2 clustercontrol 192.168.50.111 jira1.local jira1 mysql1 192.168.50.112 jira2.local jira2 mysql2

2. Ensure each host has proper FQDN set up as per host definition above. For example on lb1:

$ hostname -f lb1.local

 

Deploying Galera Cluster for MySQL

1. To set up Galera Cluster, go to the Galera Configurator to generate a deployment package. In the wizard, we used the following values when configuring our database cluster (note that we specified one of the DB nodes twice under Database Servers’ text field) :

Vendor : Codership MySQL Version : MySQL 5.6.x Infrastructure : none/on-premises Operating System : Debian 7.1.x Number of Galera Servers : 3 Max connections : 200 OS user : root ClusterControl Server : 192.168.50.102 Database Servers : 192.168.50.111 192.168.50.112 192.168.50.112

At the end of the wizard, a deployment package will be generated and emailed to you.

2. Download and extract the deployment package:

$ wget http://www.severalnines.com/galera-configurator3/tmp/wb06357009915302877/s9s-galera-codership-3.5.0.tar.gz $ tar -xzf s9s-galera-codership-3.5.0.tar.gz

3. Before we proceed with the deployment, we need to perform some customization when deploying a two-node Galera cluster. Go to ~/s9s-galera-codership-3.5.0/mysql/config/cmon.cnf.controller and remove the repeated node IP next to mysql_server_addresses so it becomes as below:

mysql_server_addresses=192.168.50.111,192.168.50.112

4. Now we are ready to start the deployment:

$ cd ~/s9s-galera-codership-3.5.0/mysql/scripts/install/ $ bash ./deploy.sh 2>&1 | tee cc.log

5. The database cluster deployment will take about 15 minutes, and once completed, the ClusterControl UI is accessible at https://192.168.50.102/clustercontrol . Enter the default admin email address and password on the welcome page and you should be redirected to the ClusterControl UI dashboard.

6. It is recommended to run Galera on at least three nodes. So, install garbd, a lightweight arbitrator daemon for Galera on the ClusterControl node from the ClusterControl UI. Go to Manage > Load Balancer > Install Garbd > choose the ClusterControl node IP address from the dropdown > Install Garbd.

You will now see the MySQL Galera Cluster with garbd installed as per below:

 

Deploying Load Balancers and Virtual IP

1. Before we start to deploy the load balancers, make sure lb1 is accessible using passwordless SSH from ClusterControl/lb2. On lb2, copy the SSH keys to lb1, 192.168.50.101:

$ ssh-copy-id 192.168.50.101

2. Login to ClusterControl, drill down to the database cluster and click the Add Load Balancer button. Deploy HAProxy on lb1 and lb2 using ‘Build from Source’, similar to the below:

3. Install Keepalived on lb1(master) and lb2(backup) with 192.168.50.100 as virtual IP:

4. The load balancer nodes have now been installed, and are integrated with ClusterControl. You can verify this by checking out the ClusterControl summary bar:

At the moment, MySQL Galera Cluster is load balanced with virtual IP, 192.168.50.100 listening on port 3307.

5. We need to add the load balancing definition for JIRA which will run on port 8080. Since we are sharing the same JIRA home directory through NFS, we need to disable failback, which means if jira1 goes down and comes back up, it will remain passive. The other node will hold the active status, and if it goes down, then jira1 will become active again.

To achieve this, we need to configure the HAProxy with stick-table. The failover scenario is further explained further down in the ‘Verifying the Architecture’ section. On lb1 and lb2, open /etc/haproxy/haproxy.cfg and add the following lines:

peers LB peer lb1.local 192.168.50.101:1234 peer lb2.local 192.168.50.102:1234 frontend jira_8080_fe bind *:8080 default_backend jira_8080_be backend jira_8080_be mode http option httpchk OPTIONS /secure option forwardfor option httplog balance source stick-table type ip size 1 nopurge peers LB stick on dst server jira1.local 192.168.50.111:8080 check server jira2.local 192.168.50.112:8080 check backup

6. Restart HAProxy to apply the changes by killing the process:

$ killall -9 haproxy && /usr/sbin/haproxy -D -f /etc/haproxy/haproxy.cfg -sf

** HAProxy installation from source does not come with init script, so kill and start the process as mentioned above to restart it. Alternatively, you can just kill the HAProxy process and let ClusterControl recover it.

 

Setting up NFS Server

The following steps should be performed on lb1.

1. Install NFS server:

$ apt-get update $ apt-get install -y nfs-kernel-server nfs-common rpcbind

2. Create a directory to be mounted as NFS shares:

$ mkdir -p /exports/jira

3. Add the following line into /etc/exports:

/export/jira 192.168.50.0/24(rw,sync,no_subtree_check,no_all_squash,no_root_squash)

4. Restart NFS daemon to export the share directory:

$ service nfs-kernel-server restart

 

Setting up NFS Client

The following steps should be performed on jira1 and jira2.

1. Install NFS client:

$ apt-get update $ apt-get install -y nfs-common rpcbind

2. Create a mount point for NFS:

$ mkdir -p /mnt/jira

3. Ensure you can see the NFS mount from the client host:

$ showmount -e 192.168.50.101 Export list for 192.168.50.101: /export/jira 192.168.50.0/24

4. Add the following line into /etc/fstab:

192.168.50.101:/export/jira /mnt/jira nfs rw,sync,hard,intr 0 0

5. Mount the NFS share as listed inside /etc/fstab and change the permission of Jira mount point to 777:

$ mount -a $ chmod 777 /mnt/jira

Verify that you have mounted the NFS share correctly:

$ df -h Filesystem Size Used Avail Use% Mounted on rootfs 19G 2.6G 16G 15% / udev 10M 0 10M 0% /dev tmpfs 201M 228K 201M 1% /run /dev/mapper/debian74-root 19G 2.6G 16G 15% / tmpfs 5.0M 0 5.0M 0% /run/lock tmpfs 402M 0 402M 0% /run/shm /dev/sda1 228M 18M 199M 9% /boot 192.168.50.101:/export/jira 19G 1.5G 17G 9% /mnt/jira

 

Installing JIRA on the first node

The following steps should be performed on jira1. We will need to completely set up JIRA on the first node before proceeding to the second JIRA node. 

1. Download Atlassian JIRA installer and start the installer:

$ wget https://www.atlassian.com/software/jira/downloads/binary/atlassian-jira-6.4-x64.bin $ chmod 755 atlassian-jira-6.4-x64.bin $ ./atlassian-jira-6.4-x64.bin

** Accept the default values except for ‘Default location for JIRA’, where you need to specify the NFS mount point, /mnt/jira as the default location for JIRA data. JIRA installation directory should be installed under /opt/atlassian/jira.

2. Download the MySQL Java connector from MySQL’s download page, extract and copy the JAR file to JIRA library: 

$ wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.34.tar.gz $ tar -xzf mysql-connector-java-5.1.34.tar.gz $ cd mysql-connector-java-5.1.34 $ cp mysql-connector-java-5.1.34-bin.jar /opt/atlassian/jira/lib/

3. Restart JIRA to activate the MySQL Java connector and to start the installation process:

$ service jira stop $ service jira start

Ensure the JIRA service is started correctly by looking into the following log file:

$ tail -100f /opt/atlassian/jira/logs/catalina.out

4. Create a schema for JIRA by using Manage > Schemas and Users > Create Database and create a database called "jira". Then assign all privileges to the schema with a wildcard host (‘%’).

5. Now, open a browser and open the JIRA installation page through virtual IP at http://192.168.50.100:8080 . From the installation wizard page, choose ‘I’ll set it up myself’ > My Own database, specify the database credentials as per below screenshot:

6. Next, specify the virtual IP as the Base URL:

7. Choose the type of JIRA that you want to install and enter the license and wait JIRA to finalize the installation. Then, setup the Administrator account and click Next.

You should now see the JIRA system dashboard:

Installation of JIRA on the first node is complete.

 

Installing JIRA on the second node

The following steps should be performed on jira2.

1. Download Atlassian Jira installer and start the installer:

$ wget https://www.atlassian.com/software/jira/downloads/binary/atlassian-jira-6.4-x64.bin $ chmod 755 atlassian-jira-6.4-x64.bin $ ./atlassian-jira-6.4-x64.bin

** Accept the default values. The JIRA installation directory should be under /opt/atlassian/jira, while the data directory should be under /var/atlassian/application-data/jira.

2. Download the MySQL Java connector from MySQL’s download page: 

$ wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.34.tar.gz $ tar -xzf mysql-connector-java-5.1.34.tar.gz $ cd mysql-connector-java-5.1.34 $ cp mysql-connector-java-5.1.34-bin.jar /opt/atlassian/jira/lib/

3. Before starting up the JIRA service on this node, change the jira.home value to the NFS mount point inside the JIRA application properties file:

$ vim /opt/atlassian/jira/atlassian-jira/WEB-INF/classes/jira-application.properties

Update the following line:

jira.home = /mnt/jira

4. Restart JIRA to apply the changes:

$ service jira stop $ service jira start

Monitor the bootstrap output from /opt/atlassian/jira/logs/catalina.out. You should see the following error:

****************************************** JIRA startup failed, JIRA has been locked. ******************************************

The failure is expected since the home directory is currently being locked by the first instance. To perform failover in case the primary JIRA (jira1) goes down, refer to the next section.

 

Failover and Recovery

Take note that, JIRA failover will not be automatic. If jira1 goes down, we need to manually perform the following steps on jira2:

1. Remove the JIRA lock file under shared directory:

$ rm -Rf /mnt/jira/.jira-home.lock

2. Restart Jira on jira2:

$ service jira stop && service jira start

Wait for the bootstrap to complete and JIRA should be started. We should able to access Jira using the virtual IP, http://192.168.50.100:8080/ .

Again, we are trying to achieve a better setup by having a standby server immediately ready if the primary JIRA goes down. When jira1 comes back up, it will not be started since the lock was acquired by jira2. You should see following in the catalina.out for jira1:

****************************************** JIRA startup failed, JIRA has been locked. ******************************************

Repeat the same steps if jira2 goes down. 

 

Verifying the Architecture

1. Check the HAProxy statistics at ClusterControl > Nodes > choose the load balancer node, you should see some bytes in and out on the jira_8080_be and haproxy_192.168.50.101_3307 sections:

2. Check and observe the traffic on your database cluster from the ClusterControl overview page at https://192.168.50.102/clustercontrol :

The steps performed in this blog post are also applicable for other Atlassian apps like Confluence, Stash or any of the other server products. This is not as good as Atlassian’s Data Center clustering product, but it sure provides redundancy at all layers. Happy clustering!

Blog category: Tags:
PlanetMySQL Voting: Vote UP / Vote DOWN

Follow up on MySQL 5.6 GTIDs: Evaluation and Online Migration

One year ago, I blogged about Evaluation and Online Migration of MySQL 5.6 GTIDs.  At that time, we setup the following test environment where:

  • A is a production master with GTIDs disabled,
  • D to Z are standard slaves with GTIDs disabled,
  • B is an intermediate master running my recompiled version of MySQL implementing the ANONYMOUS_IN-GTID_OUT mode (see the details my previous post),
  • C is a slave with GTID enabled.

    -----
    | A |
    -----
      +------+--- ... ---+
      V      V           V
    -----  -----       -----
    | B |  | D |       | Z |
    -----  -----       -----
      |
      V
    -----
    | C |
    -----

Today, doing some cleanup, we dig out the 2 servers running this experiment (B and C):
  • they have an uptime of more than 200 days,
  • their gtid_executed is at more than 1.500.000.000,
  • and they are still replicating.
I find this cool !
PlanetMySQL Voting: Vote UP / Vote DOWN

Yelp IT! A talk with 3 Yelp MySQL DBAs on Percona Live &amp; more

Founded in 2004 to help people find great local businesses, Yelp has some 135 million monthly unique visitors. With those traffic volumes Yelp’s 300+ engineers are constantly working to keep things moving smoothly – and when you move that fast you learn many things.

Fortunately for the global MySQL community, three Yelp DBAs will be sharing what they’ve learned at the annual Percona Live MySQL Conference and Expo this April 13-16 in Santa Clara, California.

Say “hello” to Susanne Lehmann, Jenni Snyder and Josh Snyder! I chatted with them over email about their presentations, on how MySQL is used at Yelp, and about the shortage of women in MySQL.

***

Tom: Jenni, you and Josh will be co-presenting “Next generation monitoring: moving beyond Nagios ” on April 14.

You mentioned that Yelp’s databases scale dynamically, and so does your monitoring of those databases. And to minimize human intervention, you’ve created a Puppet and Sensu monitoring ensemble… because “if it’s not monitored, it’s not in production.” Talk to me more about Yelp’s philosophy of “opt-out monitoring.” What does that entail? How does that help Yelp?

Jenni: Before we moved to Sensu, our Nagios dashboards were a sea of red, muted, acknowledged, or disabled service checks. In fact, we even had a cluster check to make sure that we never accidentally put a host into use that was muted or marked for downtime. It was possible for a well-meaning operator to acknowledge checks on a host and forget about it, and I certainly perpetrated a couple of instances of disks filling up after acknowledging a 3am “warning” page that I’d rather forget about. With Sensu, hosts and services come out of the downtime/acknowledgement state automatically after a number of days, ensuring that we’re kept honest and stay on top of issues that need to be addressed.

Also, monitoring is deployed with a node, not separate monitoring configuration. Outside of a grace period we employ when a host is first provisioned or rebooted, if a host is up, it’s being monitored and alerting. Also, alerting doesn’t always mean paging. We also use IRC and file tickets directly into our tracking system when we don’t need eyes on a problem right away.

Tom: Susanne, in your presentation, titled “insert cassandra into prod where use_case=?;” you’ll discuss the situations you’ve encountered where MySQL just wasn’t the right tool for the job.

What led up to that discovery and how did you come up with finding the right tools (and what were they) to run alongside and support MySQL?

Susanne: Our main force behind exploring other datastores alongside MySQL was that Yelp is growing outside the US market a lot. Therefore we wanted the data to be nearer to the customer and needed multi-master writes.

Also, we saw use cases where our application data was organized very key-value like and not relational, which made them a better fit for a NoSQL solution.

We decided to use Cassandra as a datastore and I plan to go more into detail why during my talk. Now we offer developers more choices on how to store our application data, but we also believe in the “right tool for the job” philosophy and might add more solutions to the mix in the future.

Tom: Jenni, you’ll also be presenting “Schema changes multiple times a day? OK!” I know that you and your fellow MySQL DBAs are always improving and also finding better ways of supporting new and existing features for Yelp users like me. Delivering on such a scale must entail some unique processes and tools. Does this involve a particular mindset among your fellow DBAs? Also, what are some of those key tools – and processes and how are they used?

Jenni: Yelp prizes the productivity of our developers and our ability to iterate and develop new features quickly. In order to do that, we need to be able to not only create new database tables, but also modify existing ones, many of which are larger than MySQL can alter without causing considerable replication delay. The first step is to foster a culture of automated testing, monitoring, code reviews, and partnership between developers and DBAs to ensure that we can quickly & safely roll out schema changes. In my talk, I’ll be describing tools that we’ve talked about before, like our Gross Query Checker, as well as the way the DBA team works with developers while still getting the rest of our work done. The second, easy part is using a tool like pt-online-schema-change to run schema changes online without causing replication delay or degrading performance

Tom: Josh, you’ll also be speaking on “Bootstrapping databases in a single command: elastic provisioning for the win.” What is “elastic provisioning” and how are you using it for Yelp’s tooling?

Josh: When I say that we use elastic provisioning, I mean that we can reliably and consistently build a database server from scratch, with minimal human involvement. The goal is to encompass every aspect of the provisioning task, including configuration, monitoring, and even load balancing, in a single thoroughly automated process. With this process in place, we’ve found ourselves able to quickly allocate and reallocate resources, both in our datacenters and in the cloud. Our tools for implementing the above goals give us greater confidence in our infrastructure, while avoiding single-points of failure and achieving the maximum possible level of performance. We had a lot of fun building this system, and we think that many of the components involved are relevant to others in the field.

Tom: Susanne and Jenni, last year at Percona Live there was a BoF session titled “MySQL and Women (or where are all the women?).” The idea was to discuss why there are “just not enough women working on the technology side of tech.” In a nutshell, the conversation focused on why there are not more women in MySQL and why so relatively few attend MySQL conferences like Percona Live.

The relative scarcity of women in technical roles was also the subject of an article published in the August 2014 issue of Forbes, citing a recent industry report.

Why, in your (respective) views, do you (or don’t) think that there are so few women in MySQL? And how can this trend be reversed?

Susanne: I think there are few women in MySQL and the reasons are manifold. Of course there is the pipeline problem. Then there is the problem, widely discussed right now, that women who are entering STEM jobs are less likely staying in there. These are reasons not specific for MySQL jobs, but rather for STEM in general. What is more specific for database/MySQL jobs is, in my opinion, that often times DBAs need to be on call, they need to stay in the office if things go sideways. Database problems tend often to be problems that can’t wait till the next morning. That makes it more demanding when you have a family for example (which is true for men as well of course, but seems still to be more of a problem for women).

As for how to reverse the trend, I liked this Guardian article because it covers a lot of important points. There is no easy solution.

I like that more industry leaders and technology companies are discussing what they can do to improve diversity these days. In general, it really helps to have a great professional (female) support system. At Yelp, we have AWE, the Awesome Women in Engineering group, in which Jenni and I are both active. We participate in welcoming women to Yelp engineering, speaking at external events and workshops to help other women present their work, mentoring, and a book club.

Jenni: I’m sorry that I missed Percona Live and this BoF last year; I was out on maternity leave. I believe that tech/startup culture is a huge reason that fewer women are entering and staying these days, but a quick web search will lead you to any number of articles debating the subject. I run into quite a few women working with MySQL; it’s large, open community and generally collaborative and supportive nature is very welcoming. As the article you linked to suggests, MySQL has a broad audience. It’s easy to get started with and pull into any project, and as a result, most software professionals have worked with it at some time or another.

On another note, I’m happy to see that Percona Live has a Code of Conduct. I hope that Percona and/or MySQL will consider adopting a Community Code of Conduct like Python, Puppet, and Ubuntu. Doing so raises the bar for all participants, without hampering collaboration and creativity!

* * *

Thanks very much, Susanne, Jenni and Josh! I look forward to seeing you next month at the conference. And readers, if you’d like to attend Percona Live, use the promo code Yelp15 for 15% off! Just enter that during registration. If you’re already attending, be sure to tweet about your favorite sessions using the hashtag #PerconaLive. And if you need to find a great place to eat while attending Percona Live, click here for excellent Yelp recommendations.

The post Yelp IT! A talk with 3 Yelp MySQL DBAs on Percona Live & more appeared first on MySQL Performance Blog.


PlanetMySQL Voting: Vote UP / Vote DOWN

7 quick MySQL performance tips for the small business

We’ve heard lots in recent years about Big Data and the alternative models of data management and processing, like Hadoop and NoSQL. But truth be told, relational databases are still the workhorses of most of today’s small and medium sized businesses. Relational DBs date back over 40 years and SQL skills are fairly common, and they’re known to be highly secure.

 

MySQL is the world’s second most popular relational database management system (RDMS) and is the most popular open-source version of the database. It’s easily accessible and is often known to be part of the LAMP web development stack, standing for the ‘M’ in the acronym of popular tools, along with Linux, Apache, and PHP/Perl/Python. The fact that MySQL is free, easy to setup and scales fast are some of the main reasons why it’s the best match for many SMBs.

 

 

But as with any relational database, MySQL can be a beast when it doesn’t work right, causing your applications to slow down or stop performing and putting your business in jeopardy. That’s why you want to pay close attention to MySQL performance and do everything possible to keep things moving along. In the following we’ve accumulated 7 quick MySQL performance tips that will help keep your environment running smoothly and effectively, and your business applications running at their peak.

 

1. Understand the 4 fundamentals

 

In order to function correctly a database needs four essential components – CPU, memory, disk, and network. If any of these aren’t working properly, or are erratic, then the database server will also perform poorly. The key here is to balance these four areas properly. For example, don’t have your servers running with fast CPU and disk space, but then become short on memory allocation. Keeping an eye on these four focus areas will address the majority of your performance issues and help keep your applications running optimally and at scale.

 

2. Partition your tables

 

As your database scales up from a few thousand rows to hundreds of thousands of rows, performance lags behind and the queries take longer to process. That’s why it’s important to use partitions. The principle of partitions is about breaking the database information into smaller, more manageable chunks to provide quicker processing times. For instance, say you decide to separate all rows where the column zipcode has a value between 70000 and 79999. You would use the RANGE command to partition this segment of data. In other words, PARTITION BY RANGE (zipcode).

 

 

According to one expert, indexing is the most misunderstood part of database management, but if properly implement the key to highest returns on performance. He goes on to point out 3 primary points to remember when it comes to indexing. We summarize these elements here:

 

* It’s much more efficient for a database to find groups of rows rather than churning through individual rows, which leads to random disk operations. Keep things simple by setting your operations to find groups of adjacent rows rather than single rows.

* Indexing should allow the database to read rows in their desired order rather than sorting, which ultimately proves to be costly.

* Use “covering indexes” when possible, which contains all and possibly more than the columns you need for your query. By using SELECT * FROM tablename WHERE criteria, this kind of index will usually will use indexes to speed up the resolution of which rows to retrieve using criteria, but then it will go to the full table to retrieve the rows.

 

4. Pay attention to your storage engine

 

MySQL supports two main storage engine types called InnoDB or MyISAM. There’s a lot of discussion about the differences and which engine is best for which set of circumstances. The best advice on the matter seems to be that for beginners and novices MyISAM is the best bet, while InnoDB tends to be a little more complicated and is more difficult for some to use and navigate.

 

5. Don’t overly-focus on configurations

 

One site has this to say about the matter of MySQL configuration settings, which is worth quoting here:

The defaults that ship with MySQL are one-size-fits-none and badly outdated, but you don’t need to configure everything. It’s better to get the fundamentals right and change other settings only if needed. In most cases, you can get 95 percent of the server’s peak performance by setting about 10 options correctly. The few situations where this doesn’t apply are going to be edge cases unique to your circumstances.

 

6. Draw upon the expertise of your peers

 

Fortunately, it helps that MySQL has been around for 20 years there’s a ton of industry experience. Don’t go it alone, but rather leverage the knowledge of your peers to solve questions and to optimize your environment. Aside from the standard forums, blogs, and Q&A sites, there are a good number of outlets for gaining insights and knowledge on topics whenever you’re in need of serious expertise. Conferences, trade shows, and local user group events can be a great way to get quick and informative answers to the most pressing issues within your MySQL environment.

 

 

7. Leverage Monitis MySQL Monitoring

 

If you’re looking for best-in-class web monitoring and performance tracking then you need to head over to Monitis. With its industry-leading global service, Monitis lets businesses monitor their network anytime and from anywhere, including website uptime monitoring, full page load and transaction monitoring, and web load testing. The benefits and takeaways here are peace of mind and less stress. Last November the company introduced a new MySQL Monitoring feature to help administrators more effectively monitor their MySQL environment and make sure applications run at the speed and at the quality that customers require today. By checking active queries, identifying which ones are being sent or received, and locating potential hangups and problems from the slowest-running queries, Monitis MySQL Monitoring can display all of your information in easy and intuitive to read graphs. By keeping your applications running smoothly and more effectively, Monitis alleviates the stress and helps you focus on running your business.

 


PlanetMySQL Voting: Vote UP / Vote DOWN

isolation levels

I gots to know…

Big web companies who use MySQL – what isolation level(s) do you use? Do you take the default? Do you think about it at all? How about if you use, eg, memcached, what there? And, does your code think about optimistic or pessimistic locking?

I’ve been reading up on isolation levels after seeing Peter Bailis’
http://www.bailis.org/blog/understanding-weak-isolation-is-a-serious-problem/
(thanks to Mark Callaghan for re-tweeting about it).

I’m learning things…  here’s one for today –

I always thought that the “repeatable” in repeatable read applied to the data in general. Meaning that a query run twice in a transaction would “repeat” the same results. I guess it actually is supposed to apply to records?  Ie, other transactions’ updates to an existing row will not affect our view of the record.  According to ISO ANSI 92, apparently, repeatable read does allow the appearance of “phantom” (newly added) rows. (MySQL seems to be less permissive than that.)

But enough of my talking – I’m going back to my reading.  So, big MySQL users – let me know your thoughts on isolation levels, application-wide.  Do you care about them?  How do you enforce, or not enforce them at an application level, and why?



PlanetMySQL Voting: Vote UP / Vote DOWN

An Inside Look: Building Database Driven Apps with Go

Have you downloaded our eBook, Building Database Driven Apps with Go? It is the best resource to quickly learn how to connect a database from Go programs.

This excerpt presents a quick introduction to the major functionality of database/sql in the form of a fully functioning Go program.

Before you begin, ensure you have access to a MySQL database, as we’ll use MySQL for the example. If you don’t have an instance of MySQL that’s appropriate for testing, you can get one in seconds with the MySQL Sandbox utility.

Create a new Go source file, hello_mysql.go, with the following source code (download). You may need to adjust the connection parameters as needed to connect to your testing database. Note also that the example assumes the default test database exists and your user has rights to it:

Run your new Go program with go run hello_mysql.go. It’s safe to run it multiple times. As you do, you should see output like the following as it continues to insert rows into the table:

Congratulations! You’ve written your first program to interact with a MySQL server using Go, and here are a few highlights:

  • You imported database/sql and loaded a driver for MySQL.

  • You created a sql.DB with a call to sql.Open(), passing the driver name and the connection string.

  • You used Exec() to create a table and insert a row, then inspect the results.

  • You used Query() to select the rows from the table, rows.Next() to iterate over them, and rows.Scan() to copy columns from the current row into variables.

  • You used .Close() to clean up resources when you finished with them.

What might surprise you is that the code you’ve just run is not some kind of overly simplified, silly example. It is very similar to the code you’ll use in production systems under high load, including error handling. Much of this code is discussed in further sections of this book, available for download here.


PlanetMySQL Voting: Vote UP / Vote DOWN

Pages