MySQL uses internal temporary tables to execute some queries. Usually the tables are stored in memory and on disk if some conditions are met :
Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:
Presence of any string column in a GROUP BY or
DISTINCT clause larger than 512 bytes
Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the …
Percona Server version 5.5.35-33.0
Percona is glad to announce the release of Percona Server 5.5.35-33.0 on December 20th, 2013 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.35, including all the bug fixes in it, Percona Server 5.5.35-33.0 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the …
[Read more]It has been a busy week for the Pythonic MySQL Team at Oracle: MySQL Utilities 1.3.6 and a brand new Utilities 1.4.1 Alpha has been release which includes Fabric. Yes, and of course, the one binding them all: Connector/Python v1.1.4 has been released as GA (General Available). Check out the Change History if you want to keep up with what is being added and changed.
New features found in v1.1:
Percona is glad to announce the release of Percona Toolkit 2.2.6 on December 20th, 2013 (Downloads are available here and from the Percona Software Repositories). This release is the current GA (Generally Available) stable release in the 2.2 series. This release has 16 bug fixes and a few new features. One bug fix is very important, so 2.2 users are strongly encouraged to upgrade:
Fixed bug 1223458: pt-table-sync deletes child …
[Read more]
The MySQL Utilities Team is pleased to announce the latest GA
release of MySQL Utilities. This release includes a number of
improvements for usability, stability, and a few enhancements. We
have also included a performance upgrade for exporting,
importing, and copying databases.
Improvements
The following highlights a few of the more significant
improvements.
* mysqldbexport, mysqldbimport, and mysqldbcopy have
multiprocessing support that allows for much improved
performance
* mysqlfrm can now generate a .frm file with storage engine
substitution
* Mac OS X packages added!
* mysqlserverinfo now includes the log files (error, general,
slow)
* mysqlprocgrep can now search and kill processes by id
* mysqlmetagrep can now search the body of routines with the new
--body option
* all utilities report license type with --version and
--help
* all utilities have the new …
MySQL 5.6 introduced the ability to expire passwords. Many work
environments have rules where it is mandatory to change passwords
on a regular basis. It is easy to expire a single account with a
ALTER USER 'dave'@'localhost' PASSWWORD EXPIRE;
command.
The mysql.user table
The mysql.user table now has a PASSWORD_EXPIRED column. A user attempting to login with an expired account using a client that supports, they will be prompted to change their password.
An example of being forced into sandbox mode and changing the password. Note that user does not have SUPER or other admin level privs to change passwords.
SET PASSWORD does not check to see if you are reusing your old …
[Read more]A decade ago MySQL folks were in love with the concept of a relay slave for MySQL high availability across data centers. A relay is a single slave in a remote data center that receives replication from the global master and, in turn, replicates to all the other local slaves in that data center. This saved a lot of bandwidth, especially back in the days before memcached when scaling reads meant lots of slaves. Sending 20 copies of your replication stream cross-WAN gets expensive.
In Galera and Percona XtraDB Cluster (PXC), by default when a transaction commits on a given node it is sent to every other node in the cluster from that node. That is, the actual writeset payload (the RBR events) are sent over the network to every other node, so the bandwidth to replicate is roughly:
<writeset size> * …[Read more]
quilt series that tries to address the
inefficiencies described in http://kristiannielsen.livejournal.com/17598.html
December 19, 2013 By Severalnines
Database clusters are pretty sophisticated distributed systems with complex dependencies between nodes. The failure of a node will generally impact the overall cluster, as the remaining nodes need to reconfigure themselves to continue to operate without the failed node. Since re-introducing a node will also affect the existing cluster, the timing could therefore be dependent on the state of the other nodes in the cluster. Repair and restarts often needs to be performed in a particular order in compliance with the redundancy model of the cluster so as not to jeopardize the normal functioning of existing nodes.
Webinar: Repair and Recovery for your MySQL, MariaDB and MongoDB/TokuMX clusters
…
[Read more]In short, during an optimization or a MySQL query involving a lot of replace(replace(replace…))) I decided to compile PREG as lib_mysqludf_preg from UDF Repository for MySQL in a view to use PCRE functions directly in MySQL.
Too bad MySQL still cant not use functions for index creation, but this is not the theme of this post.
So, fist install MariaDB 10.0.6:
set the MariaDB 10.0.6 repo, I used the ftp mirror at
ftp.ulak.net.tr
root@seik-centos-01:[Wed Dec 18 23:55:12][/tmp]$ cat /etc/yum.repos.d/MariaDB.repo # MariaDB 10.0 CentOS repository list - created 2013-12-18 18:07 UTC # http://mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB #baseurl = http://yum.mariadb.org/10.0/centos6-amd64 baseurl = ftp://ftp.ulak.net.tr/pub/MariaDB/mariadb-10.0.6/yum/centos6-amd64/ gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 …[Read more]
