The Oracle Cloud Infrastructure (OCI) command line interface (CLI) allows users to manage OCI resources. In this post, we will discuss how to use the OCI CLI to create a MySQL HeatWave inbound replication channel.

MySQL使用B树索引进行查找，但自适应哈希索引（AHI）是一种更快的方法。AHI在内存中构建的哈希索引可以加速B树索引的查找，根据查询的重复性自动调整使用情况。在大数据集上，AHI可以显著提高性能，但也需要一定的开销。

MariaDB和MySQL是两个流行的数据库管理系统，具有类似的功能和目的。MariaDB起初是MySQL的一个分支，但现在已经有了很大的差异。这两个数据库都提供强大的事务支持、ACID合规性和丰富的管理工具生态系统。它们在性能、可靠性和与现有应用程序的兼容性方面表现出色。MariaDB由社区支持，强调开源创新，而MySQL由Oracle支持，专注于稳定性和与其他Oracle产品的集成。两者都有开源和企业版本，后者以额外的功能为代价。选择MariaDB和MySQL之间的因素包括所需的功能、性能要求、生态系统集成和长期路线图的一致性。Percona for MySQL也被提供为一个替代选择，它提供了MariaDB或MySQL Enterprise的强大特性，而不需要额外的费用或供应商锁定。它提供了兼容性、高性能、零锁定、数据持久性、高可用性、健康监控、支持云原生策略和成本优化。最终，最佳的数据库选择取决于项目目标、团队技能和运营要求。

MySQL是一种常用的关系型数据库管理系统，常见的面试题涉及索引、事务和锁等方面。索引是加快数据库查询和更新速度的特殊文件，常见的实现方式有B树和B+树。死锁是多个事务相互占用资源导致的循环等待现象，可以通过约定访问顺序和锁定资源来解决。视图是一种虚拟表，提高了复杂SQL语句的复用性和数据安全性。SQL语句主要分为DDL、DQL、DML和DCL四类。

When talking about storage performance, we often hear three terms that are important in describing storage performance. They are bandwidth latency I/O operations per second (IOPS) When you talk to storage vendors and cloud providers, they will gladly provide you with numbers on bandwidth and IOPS, but latency numbers will be hard to come by. To evaluate storage, especially for MySQL, you need just one number, and that is the 99.5 percentile commit latency for a random 16 KB disk write. But let’s start at the beginning. Bandwidth The bandwidth of an IO subsystem is the amount of data it can read or write per second. For good benchmarking numbers, this is usually measured while doing large sequential writes. A benchmark would, for example, write and read megabyte sized blocks sequentially to a benchmark files, and then later read them back. Many disk subsystems have caches, and caches have capacities. The benchmark will exercise the cache and measure cache speed, unless you manage to disable the caches. Or deplete them, by creating a system workload large enough to exceed the cache limit. For example, on a server with 128 GB of memory, your benchmark will either have to disable these caches, or benchmark with a file of 384 GB or larger to actually measure storage system performance. Writes can be forced to disk, at the filesystem level, by using the means of fdatasync(), O_DIRECT or O_SYNC, depending on what you want to measure and what your operating system and drivers provide. But if the storage itself provides caches, it may be that these are persistent (such as battery backed memory), and while that counts as persistent, it may not be the storage performance we want to measure. It could be that this is what we get until the cache is full, and then we get different numbers from the next tier of storage. Depending on what we need for our evaluation, we might need to deplete or disable this cache layer to get numbers from the tier we care about. Strangely, read benchmarking is harder – we often can easily bypass writes caches by asking for write-though behavior. But if data is in memory, our read request will never reach the actual storage under test. Instead it will be served by some higher, faster tier of the storage subsystem. That’s good in normal operations, but bad if we want to find out worst-case performance guarantees. From an uncached enterprise harddisk you would expect on the upside of 200 MB/sec streaming write bandwidth, from an uncached enterprise flash twice that, around 400 MB/sec written. For short time or from specialized storage you can get up to 10x that, but sustained it is often disappointingly low. By bundling storage in a striped RAID-0 variant, you can get a lot more. By providing an abundance of battery-backed memory, you can get speeds that saturate your local bus speed or whatever network is in the path. Latency Write and read latency is the amount of time it takes to write a piece of data. For a database application developer, write latency usually amounts to “how long to I have to wait for a reasonably sized COMMIT to return”. To better understand database write latency, we need to understand database writes. The long version of that is in explained in MySQL Transactions - the physical side . The short version is that MySQL prepares a transaction in memory in the log buffer, and on commit writes the log buffer to the redo log. This is a sequentially structured, on-disk ring buffer that accepts the write. A commit will return once data is in there (plus replication shenanigans, if applicable). For a fast database, it is mandatory for the redo log to be on fast, persistent storage. Writes to the database happen in relatively tiny blocks, much smaller than actual database pages. Later, out of the critical path, the database writes out the actual modified data pages as 16 KB random writes. This is important, but it happens a long time after the commit in a batched fashion, often minutes after the actual commit. Still, a filesystem benchmark that uses randomly written 16 KB pages usually characterizes the behavior of a storage subsystem in a way that good predictions of its real world performance can be made. So run a fio benchmark that writes single-threadedly to a very large test file, doing random-writes with a 16 KB block size, and look for the clat numbers and the latency histogram in the benchmark. It might look like this: ... write: IOPS=2454, BW=38.4MiB/s (40.2MB/s)(11.2GiB/300001msec) ... clat percentiles (usec): | 1.00th=[ 202], 5.00th=[ 237], 10.00th=[ 249], 20.00th=[ 269], | 30.00th=[ 285], 40.00th=[ 302], 50.00th=[ 314], 60.00th=[ 330], | 70.00th=[ 347], 80.00th=[ 396], 90.00th=[ 482], 95.00th=[ 570], | 99.00th=[ 2212], 99.50th=[ 2966], 99.90th=[ 6915], 99.95th=[ 7832], | 99.99th=[ 9503] ... Be sure to check the units, here usec (µs, microseconds, 1E-06 sec). fio changes them around as needed. Then look at the numbers in the clat histogram. You will see relatively linear numbers, here up to the 95th percentile, and then a sudden and steep increase. Most storages are bimodal, and you will see “good” behavior and numbers in the low percentiles, and then numbers from the “bad” writes above. It is important to treat both numbers as differently and remember the cutoff percentile as well. This storage has a clat of about 0.57 ms, until the 95th percentile, and then 2.0 ms or more for about 5% of all writes. That’s not good, but these numbers are from a specific multithreaded test, not single-threaded writing, and I just included them to show what they would look like. For reference, a local NVME disk should give you <100 µs for the good writes, and present a cutoff at the 99.90th percentile (data from 2019 ). A good disk-based NetApp filer connected with FC/AL, as sold in 2012, would present itself at ~500 µs write latency, and with a very high cutoff at the 99.5th or 99.9th percentile You would be mostly talking to the battery backed storage in that unit, so even with hard disks in it, it would be decent enough to run a busy database on it. If your storage is worse, latency-wise, than this 12-year-old piece of hardware, you will probably not be happy with it under load for database workloads. IOPS “I/O Operations Per Second” sounds a lot like latency, but is not. It is the total I/O budget of your storage. For old enterprise harddisks with a 5 ms seek time, you get around 200 IOPS from a single hard-disk. With a disk array, a multiple of that – more if you have more spindles. That is why in the old times before flash, DBAs strongly preferred arrays from many tiny disks over arrays from few large disks. That is also why disk-based database write-performance is completely dependent on the presence of battery backed storage. With modern flash and NVME, paths to the actual storage in the flash chips are parallel. This is one of the big advantages of NVME over SSD or SATA, in fact. A single enterprise NVME flash drive can provide you with up to 800.000 IOPS. But that is not a latency number. A single commit will still take around 1/20.000 sec, around 50µs. So if you execute single row insert/commit pairs in a loop, you will not see more than 20.000 commit/s, even if the single NVME drive can do 40x that. To leverage the full potential of the drive, you will need to access it 40-way parallel. Most databases cannot do that. That is, because the workload itself that a database executes is inherently dependent on the order of commits. Each transaction creates a number of locks that are necessary for the transaction to be executed correctly. Rows that are being written to are exclusively locked (X-locked), rows that are being referenced by foreign key constraints can be share-locked (S-locked), and additional locks may be taken out explicitly. Most people are running MySQL in a simplified model (for example using group replication) where only X-locks matter, and the set of primary keys that are written to (and hence get X-locked) is the write-set. Two transactions are potentially parallel-executable when the intersection of their write-set is empty, that is, when they write to row sets that are non-overlapping. Most execution models for MySQL (expecially regarding parallel replication ) will try to execute transactions in parallel until they find an overlap. Then they will block until all transactions are done, and start the next batch of parallel transactions. This is not optimal, but simple, and prevents re-ordering of transactions in a way that is easy for developers to understand. It will also not break heartbeat writes that are often used to check for replication delay. In reality, the execution-width of such a transaction stream varies wildly and is completely subject to what the applications do. For execution guarantees and service-level objectives, when using MySQL, the IOPS budget of the storage is immaterial. We can only make guarantees for the database based on storage latency. That is not a number you will find in the AWS catalog. So go, measure. In the workloads I have seen, over a longer stretch of time I have often seen a degree of parallel execution hovering around 3-5. So your 2012 Netapp filer with 500 µs commit latency will, if the IOPS budget is big enough, execute around 2000 sequential commits/s, and will perform with around 6000-10.000 IOPS. You can guarantee only 2000 commit/s, because that is worst case performance, and as a DBA you do not control application workload. Jitter Jitter is the amount of variance you get in latency. Most of the time, we care a lot about jitter, especially with transactional workloads. Jitter matters, because if we hum along with 10.000 write-commit/s, and we get a hiccup of 1/10 s (100ms), we are likely looking at 1000 stalled processes in SHOW PROCESSLIST. That is, if our max_connections is too low, we die. Also, even if we don’t die, the applications people will hate us. You can see the effect of jitter or lock pileups, for example by running innotop -m Q -d 0.1 for a minute. If things are fine, the number of shown active, non-idle connections will be relatively constant and smaller than the number of CPU threads available to your machine. TL;DR Bandwidth is the MB/s you get from your storage. Work with 200 MB/s for disk, 400 MB/s for bulk flash, and celebrate burst speeds of 4 GB/s for a short time. Latency, for MySQL, is how long you wait for a 16 KB random-write to happen in fio. 2012’s NetApp gives you 500 µs, 0.5 ms. Today’s NVME gives you 100 µs, 0.1 ms. The cloud with very remote storage often gives you 1-2 ms. commit-rate = 1/latency is the number of turns per second you get from a for-loop running “insert-commit”. IOPS is the total, parallel IO budget for your database. IOPS/commit-rate is the required degree of parallelism needed to eat the whole buffet, that is, use up all IOPS. Transaction parallelism in MySQL is application-dependent, and is usually defined by how long a run of intersection-free write-sets you can find. For the cases and workloads I have seen (webshops), it varies wildly over time, and is often 4-ish. A single MySQL instance will usually not be able to consume all IOPS offered by even a single enterprise NVME drive (800k IOPS). This would require a 40-wide parallel path, 10x more than what workloads typically can offer. Jitter matters. That is why we look at 99th percentile and higher for completion latency (clat) in fio. Cloud storage often sucks. 1 ms or even 3 ms clat give you 300-1000 commit/s, in a loop. Often that is borderline insufficient. You can weaken persistence guarantees (innodb_flush_log_at_trx_commit), complicate your model by introducing other technologies (that are faster, because they have weaker persistence guarantees), or run MySQL yourself on i3 instances with local storage (but that is not why you chose the cloud). Guarantees can be made only on things you control, and that may be bandwidth and latency, but never parallelism. Parallelism is controlled by the application, not by the DBA.

MySQL是一种开源免费的关系型数据库管理系统，适合Java企业级开发。它支持扩展，与Oracle和Sql Service相比，具有不同的系统支持、费用和性能。索引是一种特殊的文件，用于快速查询和更新数据库表中的数据，包括普通索引、唯一索引和复合索引。B树和B+树的区别在于节点存储方式。死锁是多个事务相互占用资源导致的循环现象，可以通过约定访问顺序、锁定资源和升级锁定颗粒度来解决。视图是一种虚拟表，提高了复杂SQL语句的复用性和表操作的安全性。SQL语句主要分为DDL、DQL、DML和DCL四类。

MySQL中的UPDATE JOIN句子用于根据相关联表的条件来更新方针表的数据。通过UPDATE JOIN，可以实现灵敏和高效的数据更新操作。在使用UPDATE JOIN时需要注意衔接条件和WHERE子句的精确性，以避免意外的结果。

This is the second article in our blog series about MySQL NDB Cluster replication, it describes the basic concept and functional parts of a single-channel replication which is used for replicating data between clusters.

本文介绍了在CentOS 7上安装MySQL 5.7的步骤，包括下载组件、安装依赖、创建用户和用户组、安装离线依赖、导入so库、解压MySQL文件、授权文件夹、创建必要文件夹和文件、初始化MySQL、复制启动脚本、修改配置文件、添加环境变量、创建Service和开机启动。

MySQL外键用于定位表记录的列或列组，维护引用完整性，提高查询性能。定义外键约束的语法包括引用表和列、删除和更新操作。可以通过创建新表或修改现有表来添加外键。可以使用SELECT语句查看外键约束，并使用ALTER TABLE语句删除外键。禁用外键检查需谨慎，以避免数据完整性问题。