When data and concurrency grow, together with queries complexity,
a standard configuration does not fit anymore. This content walks
through the ways MySQL has to adapt to an ever-increasing amount
of data in context of tuning, under the heading
“multiplication”:
—Scaling up through partitioning and how partitioning can help
manage/archive data
—How to relocate binlog, undo, redo, tablespaces, or disk
temporary tables and more on different mount points to take
advantage of multiple storages and tackle I/O bottlenecks
—All improvements to parallel slave replication
—A quick OS approach, to verify swapping and affinity tuning take
the most out of the machine.
All of this with different approaches to monitor the instance to
spot what parts of the system and what queries need to be
considered, mainly using:
—Sys Schema
— …
MySQL Support Blogs
Support Blogs:
This is an aggregated feed of various blogs of Oracle support staff who work and support MySQL customers and users. There is an RSS feed to which you can subscribe.
If you need to do bulk importing into InnoDB Cluster, it is
certainly possible to do so by using any of:
- mysqldump logical dump
- tab separated dump, exported with SELECT..INTO OUTFILE statement or by using mysqldump with --tab option. And import with LOAD DATA..INFILE
Unfortunately both imports will add load to instances and to
channels interconnecting instances: data imported on the primary
instance needs to be replicated to the rest of instances. And the
bigger the data to import, the higher the load (and this could
end up affecting performance). The import operation could be
batched to reduce load, and Group Replication allows at least to
throttle workload with …
Oracle 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.
…
MySQL 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.
…
MySQL Connector/Python 8 made the C Extension the default for the
platform/Python version combinations supporting it. One thing
that was missing from the C Extension implementation (unless you
used the _mysql_connector module) was support for
prepared statements. That has been taken care of with the release
of version 8.0.17.
The two main advantages of using prepared statements are security and performance. The security comes in as you can pass query parameters and have them applied server-side, so you are sure they are quoted and escaped correctly taking the data type into consideration. The performance benefit happens, when you execute the same query (except for the parameters) several times as MySQL will prepare it …
The sys schema was created to make it easier to use
the Performance Schema. This included several functions, for
example to convert the picoseconds used by the Performance Schema
into human readable strings. In MySQL 8.0.16, three of these
functions have been implemented as native functions in MySQL
Server.
…
MySQL Server has since version 5.6 supported connection attributes for the clients. This has allowed a client to provide information such as which program or connector the client is, the client version, the license, etc. The database administrator can use this information for example to verify whether all clients have been upgraded, which client is executing a particular query, and so forth.
In MySQL 8.0.16 this feature has been included for the X DevAPI in the MySQL connectors as well, including MySQL Connector/Python which I will cover in this blog. First though, let’s take a look at how the attributes are exposed in MySQL Server.
…
MySQL 8.0.16 was released last week and includes many great
features including support for CHECK constraints and
upgrades without the need of mysql_upgrade. As usual
there are also several contributions from the community. These
are the ones, I would like to highlight in this blog to say
“thank you for the contributions”.
…
In my blog yesterday, I wrote about the new
reporting framework in MySQL Shell. It is part of the 8.0.16
release. I also noted that it includes the possibility to create
your own custom reports and use those with the \show
and \watch commands. This blog will explore how you
can create a report and register it, so it automatically is
available when you start MySQL Shell.
…
Readers of my blog know that I like how MySQL Shell allows you to customize it and use it’s Python and JavaScript support to create custom libraries with tools that help with your daily tasks and even creating auto-refreshing reports. Lefred has even taken this a step further and started to port Innotop to MySQL Shell.
One disadvantage of …
MySQL stores several files on disk. Even in MySQL 8 where the data dictionary is stored in InnoDB tables, there are still all the tablespace files. Different file system behave differently, and one particular challenge is case sensitivity. On Microsoft Windows, the case does not matter, on Linux the case is important, and on macOS the case of the file names is preserved but the operating system by default makes it look like it is case insensitive.
Which convention that is the correct depends on your personal
preference and use case. Between case sensitivity and case
insensitivity, it basically boils down to whether
mydb, MyDB, and MYDB
should be the same identifier or three different ones. …
Today is World Backup Day, so I thought I would use the opportunity to discuss some best practices and general considerations regarding backing up MySQL instances. While I focus on MySQL, several of these tips apply to backups in general.
…
When you write stored procedures in MySQL, you sometimes need to
generate queries on the fly, for example as you process the
result of another query. This is supported using prepared statements. This blog explores how
you can take advantage of the sys schema to simplify
the use of dynamic queries.
Executing a query using the sys.execute_prepared_stmt() …
A great way to install MySQL when you need to do quick tests is to use a sandbox tool. This allows you to perform all the installation steps with a single command making the whole process very simple, and it allows for automation of the test. Giuseppe Maxia (also known as the Data Charmer, @datacharmer on Twitter) has for many years maintained sandbox tools for MySQL, first with MySQL Sandbox and now with dbdeployer.
One of the most recent features of dbdeployer is the support for MySQL NDB Cluster. In this blog, I will take this feature and test it. First, I will …
I few months ago, I wrote about using the Django framework with MySQL 8. There are also other Python frameworks that are worth considering. In this blog, I will look at using SQLAlchemy with MySQL 8.
In order for you to be able to use MySQL 8 with SQLAlchemy, you need three pieces of software: MySQL Server, MySQL Connector/Python, and SQLAlchemy. I will go through the installations, then I will look at a code example.
…
MySQL changed the timestamp format in the log files in MySQL 5.7.
Since then, I have a few times seen questions about the new
format, and how to change the time zone that is used. Latest in a
comment to my blog about log_slow_extra in
8.0.14, where the question was what T and Z in the timestamp
(for example 2019-01-31T07:24:06.100447Z) means. In
this blog, I will discuss the timestamp format and show you how
you can change the time zone used.
…
The X DevAPI allows you to work with JSON documents and SQL
tables at the same time. Furthermore, the CRUD style API is more
intuitive than SQL statements for some programmers. Either way,
the X DevAPI allows you to mix JSON documents, SQL tables, CRUD
methods, and SQL statements to give you the best of all worlds.
In MySQL Connector/Python, the X DevAPI is implemented in the
mysqlx module.
This blog will look at how MySQL Connector/Python handles
expressions, and how you in version 8.0.14 and later need to use
the mysqlx.expr() method to explicitly
define expressions.
…
When things go horrible wrong and a process crashes, one of the most powerful things to investigate the cause of the crash is a core dump. As the amount of memory allocated to processes such as MySQL has increased – in some cases approaching 1TiB of memory – enabling core dumps can cause problems of their own. MySQL Server 8.0.14 and later supports an option to reduce the size of the core dump which will be discussed in this blog.
Typically the largest single user of memory for MySQL is the InnoDB buffer pool. This is used to cache the data and indexes for tables using the InnoDB storage engine (the default). It is rarely important to know what is stored in the InnoDB buffer pool when investigating a crash, so a good …
MySQL Server has a feature where you can insert data into a table from a file with the data delimited by commas, tabs, or another delimiter. This is particularly useful when you need to bulk import data, for example when restoring a backup or migrating data from one system to another including from another database product than MySQL. The mysqldump backup utility is an example of a program that supports exporting the data to delimited text files.
The statement to load the data is LOAD DATA INFILE. By default the file must
be server-side and MySQL Server will load it without …
The slow query log is the trusted old method of recording slow query, so the database administrator can determine which queries are in the most need for optimization. Since MySQL 5.6, it has to some extend been overshadowed by the Performance Schema which has lower overhead and thus allows collecting statistics about all queries. The slow query log has one major advantage though: the data is persisted. In MySQL 8.0.14 which was recently released, there is an improvement for the slow query log: additional statistics about the recorded queries.
…
Content reproduced on this page is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.