Home

I Am Speaking at Oracle Developer Live – MySQL 2020

It is a year of online conferences and next week, the time has come to Oracle Developer Live for MySQL. It consists of a keynote at the start of the day and two tracks at the rest of the day. The event takes place in two rounds: On 13 October the event takes place during American business hours, and on 15 October it happens during European, African, Middle Eastern, and Asian-Pacific business hours/evening. I am honoured to have been given the opportunity to speak about MySQL performance tuning. On 13 October, I will speak at 12:05pm Pacific Time and on 15 October at 10:05 Central European Time/1:35pm Indian time/7:05pm Australian Eastern Daylight Time. A teaser: you will learn about the

Read More »

Happy Birthday MySQL

Today 23 May 2020, it is 25 years since the first release of MySQL. So, I would like to take the opportunity to wish MySQL – and Sakila – a happy birthday. My own MySQL journey started in 2006 when I at a job interview was told that if I got the job, I would need to learn MySQL before starting. Since the job involved PHP coding, I got the book Web Database Applications with PHP and MySQL as well as Managing and Using MySQL. Around a week later, I started in my first job involving MySQL at Noggin Pty Ltd. This was in the days of MySQL 5.0 when stored functions, procedures, and triggers were new, and statement based

Read More »

MySQL Compressed Binary Logs

Table Of Contents ConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionOn a busy server, the binary logs can end up being one of the largest contributors to amount of disk space used. That means higher I/O, larger backups (you are backing up your binary logs, right?), potentially more network traffic when replicas fetch the logs, and so on. In general, binary logs compress well, so it has been a long time wish for a feature that allowed you to compress the logs while MySQL are still using them. Starting from MySQL 8.0.20 that is now possible. I will take a look at the new feature in this post. Configuration The binary log compression feature is controlled by two variables, one for enabling

Read More »

Apress Blog: MySQL Performance Tuning Best Practices

Table Of Contents ConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionTo celebrate the publishing of my new book MySQL 8 Query Performance Tuning, the Apress team invited me (thanks Jonathan and Liz) to write a post for the Apress blog. I decided to write about my top six best practices: Be wary of best practices Monitor Work methodically Consider the full stack Make small, incremental changes Understand the change Yes, my first best practice is to be wary of best practices. Read why I added that and the other best practices at MySQL Performance Tuning Best Practices.

Read More »

MySQL 8.0.20: Index-Level Optimizer Hints

Table Of Contents ConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionThe Short StoryThe DetailsHow About USE INDEX?MySQL introduced optimizer hints in version 5.7 and greatly extended the feature in MySQL 8. One thing that has been missing though is the ability to specify index hints using the syntax of optimizer hints. This has been improved of in MySQL 8.0.20 with the introduction of index-level optimizer hints for the FORCE and IGNORE versions of the index hints. This blog will look at the new index hint syntax. The Short Story To make a long story short, consider this query in 8.0.19 and earlier: This query has two index hints, IGNORE INDEX in the second line and USE INDEX FOR ORDER BY in the

Read More »

New Book: MySQL 8 Query Performance Tuning

Table Of Contents ConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionThe Short StoryThe DetailsHow About USE INDEX?Book StructureChaptersPart I: Getting StartedPart II: Sources of InformationPart III: ToolsPart IV: Schema Considerations and the Query OptimizerPart V: Query AnalysisPart VI: Improving QueriesI have over the last few years been fortunate to have two books published through Apress, Pro MySQL NDB Cluster which I wrote together with Mikiya Okuno and MySQL Connector/Python Revealed. With the release of MySQL 8 around a year ago, I started to think of how many changes there has been in the last few MySQL versions. Since MySQL 5.6 was released as GA in early 2013, some of the major features related to performance tuning includes the Performance Schema which was

Read More »

Back Up MySQL View Definitions

Table Of Contents ConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionThe Short StoryThe DetailsHow About USE INDEX?Book StructureChaptersPart I: Getting StartedPart II: Sources of InformationPart III: ToolsPart IV: Schema Considerations and the Query OptimizerPart V: Query AnalysisPart VI: Improving QueriesIf you want to back up your table and views, stored procedures, or stored function definitions, you can use mysqldump or mysqlpump to export the schema without the data. However, if you just want the views you need to look for another option. This blog shows how MySQL Shell comes to the rescue. There are a couple of approaches to get the view definitions. One option is to consider the information_schema.VIEWS view which has the following columns: This looks good, but there are

Read More »

MySQL Server 8.0.18: Thanks for the Contributions

Table Of Contents ConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionThe Short StoryThe DetailsHow About USE INDEX?Book StructureChaptersPart I: Getting StartedPart II: Sources of InformationPart III: ToolsPart IV: Schema Considerations and the Query OptimizerPart V: Query AnalysisPart VI: Improving QueriesIn my blog series about external contributions to MySQL 8 we have reached version 8.0.18 which was released Monday 14 October 2019. Again the community has contributed to make MySQL better. Thank you. The contributions to MySQL 8.0.18 includes several patches from Facebook as well as patches from Gillian Gunson, Przemysław Skibiński (Percona), Daniel Black, and Satya Bodapati (also Percona). The contributions are: MySQL now provides more control over the use of compression to minimize the number of bytes sent over connections to

Read More »

Create MySQL Test Instance with Oracle Cloud Free Tier

Table Of Contents ConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionThe Short StoryThe DetailsHow About USE INDEX?Book StructureChaptersPart I: Getting StartedPart II: Sources of InformationPart III: ToolsPart IV: Schema Considerations and the Query OptimizerPart V: Query AnalysisPart VI: Improving QueriesOracle announced this week at Oracle OpenWorld that it has introduced a new cloud offer called Oracle Cloud Free Tier. As the name suggest, it allows you to run a few limited instances in Oracle Cloud for free. I will in this blog show how you can use the free tier to setup a MySQL test instance. The first step is to sign up for the cloud service which you do by opening https://www.oracle.com/cloud/free/ and click on the Start for free button near

Read More »

MySQL Server 8.0.17: Thanks for the Contributions

Table Of Contents ConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionThe Short StoryThe DetailsHow About USE INDEX?Book StructureChaptersPart I: Getting StartedPart II: Sources of InformationPart III: ToolsPart IV: Schema Considerations and the Query OptimizerPart V: Query AnalysisPart VI: Improving QueriesMySQL 8.0.17 was released Monday and it includes great features such as the Clone feature and multi-valued indexes. There are also several nice contributions from the community. These are the changes that this blog is about. The contributions to MySQL Server 8.0.17 include patches from Facebook, Daniël van Eeden, Mattias Jonsson, and Simon Mudd (all from Booking.com), Daniel Black, Yibo Cai (from Arm Technology), Josh Braden, and Zhou Mengkang. The larger contributions are: The mysql client program now sends os_user and os_sudouser connection

Read More »