In my previous post I wrote about how I achived a >40% speedup on sysbench read-only using profile-guided optimisation (PGO). While this is a preliminary result, I though it was so interesting that it deserved early mention. The fact that any benchmark can be improved that much shows clearly that PGO is something worth looking into. Even if we will probably not improve all workloads by 40%, it seems highly likely that we can obtain significant gains also for many real workloads.

I had one or two interesting comments on the post that raise valid concerns, so I wanted to write a follow-up here, explaining some of the points in more details and going deeper into the performance counter measurements. As I wrote before, actual observations and measurements are crucial to fully understand performance of complex code on modern CPUs. Intuition and …

Continuing my investigation of single-threaded performance in the MariaDB server, I managed to increase throughput of single-threaded read-only sysbench by more than 40% so far:

I use read-only sysbench 0.4.12 run like this:

    sysbench --num-threads=1 --test=oltp --oltp-test-mode=simple --oltp-read-only --oltp-skip-trx run

And mysqld is run with minimal options:

    sql/mysqld --no-defaults --basedir=X --datadir=Y --innodb-buffer-pool-size=128M

With modern high-performance CPUs, it is necessary to do detailed measurements using the built-in performance counters in order to get any kind of understanding of how an application performs and what the bottlenecks are. Forget about looking at the code and counting instructions or cycles as we did in the old days. It no longer works, not even to within an order of magnitude.

Many installations of MySQL server come with a built-in database called test. It's initially empty, and you might wonder what it's for, or even if you can delete it without any problems.

What is it for? 

The test database is installed by the MySQL Server RPM as part of the mysql_install_db process, and some other package managers run that script too. If you run that script as part of a manual install of MySQL, you'll get the same effect. It creates the database by creating an empty directory called "test" in the data directory, and creates wide-open access to the database test and any database with a name beginning with test_ by inserting a couple of rows into the mysql.db table that give everyone full access to create or use those databases.

The configuration is designed to make it easy for new users to create a playground or sandbox database to work with, one that doesn't require asking the DBA …

MySQL is an excellent choice for a low cost embedded database. Over 3,000 ISVs, OEMs and VARs choose MySQL for this purpose and benefit from significant cost savings when compared to using a competing database product.

The MySQL for Developers training course teaches you how to plan, design and implement applications using MySQL. Expert Oracle University instructors teach you through realistic examples, interactive instruction and hands-on exercises.

This course brings you lots of interesting facts. For example, the 'libmysqld' library is effectively a MySQL server in its own right. it is shipped as part of the standard MySQL installation. Developers with C/C++ ability can embed this library easily within their applications and it can do most of what a …

Sequel Pro

I often use Sequel Pro when I'm getting up to speed on the data model for a project or when I just want to debug in a more visual way than with the mysql command-line client. It's a free OS X application that lets you inspect and manage MySQL databases. I also find it very useful for making small changes to the data while I develop and test web apps.

Quickly Copy Data Between Databases

I recently needed a way to copy a few dozen records from one camp to another. I tried using the "SELECT...INTO OUTFILE" method but ran into a permissions issue with that approach. Using mysqldump was another option but that seemed like overkill in …

In the last 10 years I have worked a lot with replication systems, and I have developed a keen interest in the topic of multiple masters in a single cluster. My interest has a two distinct origins:

• On one hand, I have interacted countless times with users who want to use a replication system as a drop-in replacement for a single server. In many cases, especially when users are dealing with applications that are not much flexible or modular, this means that the replication system must have several points of data entry, and such points must work independently and in symbiosis with the rest of the nodes.
• On the other hand, I am a technology lover (look it up in the dictionary: it is spelled geek), and as such I get my curiosity stirred whenever I discover a new possibility of implementing multi-master systems.

The double nature of this professional curiosity makes me sometimes forget that the …

Busy database-backed websites often hit scalability limits in the database first. In tuning MySQL, one of the first things to look at is the max_connections parameter, which is often too low. (Of course another thing to look at is appropriate fragment caching in your app server, HTTP object caching in your web server, and a CDN in front of it all.)

When using MySQL 5.5 from Oracle's RPMs through cPanel (MySQL55-server-5.5.32-1.cp1136) on RHEL 5.10 x86_64, there is an interesting problem if you try to increase the max_connections setting beyond 214 in /etc/my.cnf. It will silently be ignored, and the limit remains 214:

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 214   |
+-----------------+-------+
1 row in set (0.00 sec)

The problem is that the maximum number of open files allowed is too small, by default 1024, to …

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 DBA skills are highly sought after in the market place. The MySQL for Database Administrators course is a highly popular course created by the MySQL experts to give you hands-on experience with tasks ranging from installing MySQL to investigating different replication scenarios and planning for disaster recovery.

You can take this 5-day instructor-led course as a:

• Live-Virtual Event: Take this class from your own desk - no travel required. Choose from a selection of events on the schedule to suit different timezones.
• In-Class Event: Travel to an education center to take this class. Below is a selection of the events already on the schedule for this course.

December 5, 2013 By Severalnines

Thanks to everyone who attended yesterday’s webinar; if you missed the sessions or would like to watch the webinar again & browse through the slides, they are now available online.

Thanks again to our speaker, Seppo Jaakola from Codership, the creators of Galera Cluster, for this in-depth talk on Galera Cluster Best Practices - Zero Downtime Schema Changes.

Webinar topics covered

• How to perform Zero Downtime Schema Changes
• 2 main methods: TOI and RSU
• Total Order Isolation: predictability and consistency
• Rolling Schema Upgrades
• pt-online-schema-change
…