MySQL

  • 关闭 InnoDB 的 redo log

    ·

    在 MySQL 实例恢复时(尤其是逻辑备份的恢复),为了获得更快的恢复速度,通常会关闭二进制日志(Binary Log),并且将 InnoDB 的日志持久化级别调整到最低。从 MySQL 8.0.21起[1],更进一步的,可以彻底的关闭 InnoDB redo 从而获得更好导入速度。后续的 8.4 / 9.0 / 9.1 可以使用该特性。

    在本文的测试中,可以看到关闭 InnoDB redo log 导入速度可以提升约 26%

    使用场景

    最为常见的就是在进行大量数据导入时,希望能够加速数据导入的过程。

    管理命令

    可以使用如下的命令关闭/或打开 InnoDB redo log:

     ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG

    关闭 InnoDB redo log

    mysql>  ALTER INSTANCE DISABLE INNODB REDO_LOG;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW STATUS LIKE '%Innodb_redo_log_enabled%';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | Innodb_redo_log_enabled | OFF   |
    +-------------------------+-------+
    1 row in set (0.02 sec)
    
    

    打开 InnoDB redo log

    mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;
    Query OK, 0 rows affected (1.02 sec)
    
    mysql> SHOW STATUS LIKE '%Innodb_redo_log_enabled%';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | Innodb_redo_log_enabled | ON    |
    +-------------------------+-------+
    1 row in set (0.00 sec)

    执行该命令的权限

    因为该命令对数据库影响巨大,所以也引入独立的权限 INNODB_REDO_LOG_ENABLE来管理该命令的执行权限。具体参考:

    mysql> GRANT INNODB_REDO_LOG_ENABLE ON *.* to 'data_load_admin';

    性能对比

    这里做应该简单的性能对比,看看关闭 InnoDB Redo Log 导入速度会提升多少。

    # mysql -uroot test -e "show status like 'Innodb_redo_log_enabled'"
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | Innodb_redo_log_enabled | ON    |
    +-------------------------+-------+
    #  mysql -uroot test -e "truncate table passenger"
    # time mysql -uroot test < passenger.1000.sql > /dev/null
    
    real	0m3.109s
    user	0m0.017s
    sys	0m0.013s
    # mysql -uroot test -e "truncate table passenger"
    # mysql -uroot test -e "ALTER INSTANCE DISABLE INNODB REDO_LOG"
    # time mysql -uroot test < passenger.1000.sql > /dev/null
    
    real	0m2.286s
    user	0m0.022s
    sys	0m0.009s

    在这个初步测试中,可以观察到,在关闭 InnoDB Redo 之后,到如时间从 3.109s 降低到了 2.286s,在该导入中,节省时间约 26%的时间。

    参考文档

  • Oracle Cloud 是所有云平台最先支持 9.0 版本的。这里,我们来看看该版本的“标准性能”表现如何。

    测试实例与环境说明

    这里使用的实例类型是:MySQL.4,单个节点为4 ecpu 32gb,测试区域选择的是“东京”(ap-tokyo-1),多可用区(FAULT DOMAIN)的版本,测试实例存储空间大小为 100 gb。即:

    instance_type=MySQL.4
    vcpu_per_node=4
    memory_size_per_node=32
    region=tokyo
    availability=multi-az
    storage_size=100
    db_version=8.0.39/8.4.2/9.0.1

    性能对比

    本次测试分别测试了 8.0.39/8.4.2/9.0.1 这三个版本。详细的性能对比如下:

    threads/qpsMySQL80MySQL84MySQL90
    4355136063360
    8593653785256
    16805481867287
    32831780297817
    48813082047911
    64783879818060
    96850484308172
    128819882868000
    192804380538112
    256790780347536
    384820980558151
    512838680307872

    性能概述

    从该“标准”测试来看,9.0.1的性能较为稳定。从上述数据中来看,似乎略微低于 8.0和8.4 版本,但经过调查,主要原因是由于云平台 CPU 资源多少所导致的,而并不是数据库本身的问题。

    此外,在今年5月份观察到的8.4性能退化问题(参考),目前也已经解决。

  • 在不同的云厂商,购买相同规格的MySQL实例(如4vCPU-16GB),获得的性能相同吗?

    threads/qpsaliyun_202409_hangzhou->stdbenchtencent_202409_beijing_exclusive->stdbenchhwcloud_202409_beijing_x86->stdbenchbaiducloud_202409_beijing->stdbenchaws_202409_tokyo_m6i->stdbenchazure_202409_east_asia_4c16g->stdbenchgcp_202409_tokyo_80_enterprise->stdbenchoci_202409_tokyo_8039->mysql_on_4_ecpu
    47102559225572206163920257233551
    897029936467441013313365413415936
    161466016141822972986427654825028054
    3222155223361352012022121571036348578317
    4827905247701784916448165161197367458130
    6432704264952011418187181181276180717838
    9636846290772088321007207821330096758504
    128396972991820128210292244613388106208198
    192389993061020521220912259013478115078043
    256383563105221187216652232312985118727907
    384396793122421729211672190212904121318209
    512403333180522647216272159112930121068386
    have_sslDISABLEDDISABLEDDISABLEDDISABLEDYESYESYESYES
    innodb_buffer_pool_size9.75GB12GB9GB12GB11GB12GB11GB17GB
    innodb_doublewriteONONONONOFFOFFONON
    innodb_flush_log_at_trx_commit11111111
    innodb_flush_methodO_DIRECTO_DIRECTO_DIRECTfsyncO_DIRECTfsyncO_DIRECTO_DIRECT
    innodb_io_capacity200002000012000200020020050001250
    innodb_read_io_threads44484NA42
    innodb_write_io_threads44484NA44
    log_binONONONONOFFONONON
    performance_schemaOFFOFFOFFOFFOFFONONON
    rpl_semi_sync_master_enabledONONONONNANANANA
    rpl_semi_sync_master_timeout1000100001000010000NANANANA
    sync_binlog11110001111
    thread_pool_size84NANANA4NA16
    version8.0.368.0.30-txsql8.0.28-2310038.0.32-2.0.0.28.0.358.0.37-azure8.0.31-google8.0.39-cloud
    cpu_capacity80.493.3163.673.9110.956.349.9114.7

    测试结果概述

    在本次测试中:阿里云RDS MySQL性能表现最好,极限的QPS达到了4万;其次是腾讯云,达到了3.2万;第二梯队是华为云、百度云和AWS,极限的QPS约2.2万;之后是Azure、Google云,极限QPS约1.2万;最后是Oracle云,极限QPS约8500。详细的数据和趋势图,可以参考以上的图、表,这里不再详述。

    (more…)
  • 在不同的云厂商,购买相同规格的MySQL实例(如4vCPU-16GB),获得的性能相同吗?

    为了回答上面的疑问,于是就开启了我的数据库性能测试之旅。这是第二次测试,上一次是在2023年12月(参考:云数据库(RDS MySQL)性能深度测评与对比)。

    性能测试结果与概述

    (more…)
  • 原本我一直是使用各个YUM仓库中默认的sysbench版本的,主要是考虑安装部署方便,而一直以来这个版本似乎都没有遇到什么问题,直到…尝试自动化的对Azure上的MySQL进行压测。

    默认的sysbench版本是1.0.17版(Mar 15, 2019),首先,是遇到了如下的报错信息:

    sysbench 1.0.17 (using system LuaJIT 2.0.4)
    
    FATAL: unable to connect to MySQL server on host 'xxx.mysql.database.azure.com', port 3306, aborting...
    FATAL: error 3159: Connections using insecure transport are prohibited while --require_secure_transport=ON

    在该版本中,sysbench提供了参数--mysql-ssl=[on|off]以尝试解决这个问题,但对于最新版本的MySQL,该参数很多时候都无法正常工作,如果你也尝试了该参数,那么会报如下错误:

    FATAL: unable to connect to MySQL server on host 'xxx.mysql.database.azure.com', port 3306, aborting...
    FATAL: error 2026: SSL connection error: SSL_CTX_set_default_verify_paths failed

    如果,恰好,你注意上面的报错可能是CA证书的问题,于是你也像我一样尝试通过 下载证书的方式去解决,并通过GitHub上的issue,成功将CA证书改名成cacert.pem,并放在当前的路径下,那么你还会遇到如下报错:

    SSL error: Unable to get certificate from 'client-cert.pem'
    FATAL: unable to connect to MySQL server on host 'xxx.mysql.database.azure.com', port 3306, aborting...
    FATAL: error 2026: SSL connection error: Unable to get certificate

    好了,也许你再成功生成client-cert.pemclient-key.pem的话,也许你真的能够运行成功。不过,再向后,我就没有尝试了,原因是,如果每一次测试都去获得各一个身份认证的密钥对,那么实在是太复杂了。

    最终,我根据AWS上这篇文章(Running sysbench on RDS MySQL… via SSL/TLS)的建议,尝试自己编译安装了最新版本的sysbench,也就是1.1.0版本。然后简单的使用参数--mysql-ssl=preferred--mysql-ssl=required彻底的解决了这个问题。

    参考文档

  • 在开始创建Compute/Database资源之前,需要先完成认证,再将需要的基础资源准备好。基础资源包括VCN/Subnet等相关的网络基础组件。本文将通过简单的示例展示这些基础组件的创建。

    区域/Compartment/AD

    和所有的Provider一样,我们会在Oracle Cloud的provider基础配置中配置好region。在资源创建的时候,核心资源都需要配置compartment_id,可以简单理解为,该资源属于哪个逻辑组(关于Compartment)。例如,一个典型的、简单的VCN的创建代码如下:

    provider "oci" {
      region           = var.region
    }
    
    # 创建一个新的compartment,他的parent compartment是tenancy_id
    resource "oci_identity_compartment" "oic" {
        #Required
        compartment_id = var.tenancy_id
        description = "for database benchmark"
        name = var.naming
    }
    
    resource "oci_core_vcn" "ocv" {
        #Required
        compartment_id = oci_identity_compartment.oic.id
        cidr_block = "172.17.0.0/16"
        display_name = var.naming
    }

    创建一个具备“公网”能力的子网

    这里说的“公网”能力,包括了两个方面:

    • 一个是可以被公网访问,这样就可以ssh登录并管理
    • 一个是可以访问公网,这样就可以通过wget/git/yum等工具安装软件

    这里配置要求比较严谨,与AWS有一些类似。分为以下几个步骤:

    • 创建一个VCN(oci_core_vcn),创建一个对应的子网(oci_core_subnet)
      • 需要注意的是,在创建了VCN之后,OCI Terraform会默认的创建一组:默认的安全组(default_security_list_id)、默认的路由表(default_route_table_id)、默认的dhcp(default_dhcp_options_id)
    resource "oci_core_vcn" "ocv" {
        #Required
        compartment_id = oci_identity_compartment.oic.id
        cidr_block = "172.17.0.0/16"
        display_name = var.naming
        dns_label    = var.naming
    }
    
    # Creates a subnet
    resource "oci_core_subnet" "subnet_primary" {
      availability_domain = data.oci_identity_availability_domain.oad.name
      cidr_block          = "172.17.1.0/24"
      display_name        = "domain_primary"
      dns_label           = "tfsubnet"
      security_list_ids   = [oci_core_vcn.ocv.default_security_list_id]
      compartment_id      = oci_identity_compartment.oic.id
      vcn_id              = oci_core_vcn.ocv.id
      route_table_id      = oci_core_vcn.ocv.default_route_table_id
      dhcp_options_id     = oci_core_vcn.ocv.default_dhcp_options_id
    }
    • 创建一个具备互联网访问规则的网关
    resource "oci_core_internet_gateway" "internet_gateway" {
      compartment_id = oci_identity_compartment.oic.id
      display_name   = "InternetGateway"
      vcn_id         = oci_core_vcn.ocv.id
    }
    
    resource "oci_core_default_route_table" "route_table_for_internet" {
      manage_default_resource_id = oci_core_vcn.ocv.default_route_table_id
      display_name               = "RouteTableForInternet"
    
      route_rules {
        destination       = "0.0.0.0/0"
        destination_type  = "CIDR_BLOCK"
        network_entity_id = oci_core_internet_gateway.internet_gateway.id
      }
    }
    • 最后,添加合适的端口访问规则:
    resource "oci_core_security_list" "osl" {
      compartment_id = oci_identity_compartment.oic.id
      vcn_id         = oci_core_vcn.ocv.id
      display_name   = "${var.naming}SecurityList"
    
      ingress_security_rules {
        protocol  = "6" // tcp
        source    = "0.0.0.0/0"
        stateless = false
    
        tcp_options {
        #   source_port_range {
        #     min = 100
        #     max = 100
        #   }
    
        #  // These values correspond to the destination port range.
          min = 22
          max = 22
        }
      }
    }

    Availability Domains

    和Compartment一样,这是另一个Oracle Cloud上必须得,但是似乎必要性并不强的概念。在Oracle Cloud上,整体的资源位置从大到小:region -> Availability Domains -> Fault Domain。其中,Fault Domain可以理解为其他云的zone的概念,代表了一个IDC机房(可能是相邻的多个building),通常,3个Fault Domain构成一个Availability Domains。在一个Region通常只有一个Availability Domains,也有部分Region有2~3个Availability Domains

    在Terraform中,如果确定了Region,我们需要使用data.oci_identity_availability_domain获取对应availability_domain的信息:

    data "oci_identity_availability_domain" "oad" {
        #Required
        compartment_id = oci_identity_compartment.oic.id
        ad_number = 1
    }

    创建计算资源(Compute)

    选择合适的image

    这里参考了example public_ip.tf@GitHub,使用了较为“直接”的方式(缺乏扩展性)获取需要镜像:

    variable "instance_image_ocid" {
      type = map(string)
    
      default = {
        # See https://docs.oracle.com/en-us/iaas/images/image/abf452f1-bf22-4837-b47b-79945ed26bee/
        # CentOS-7
        ap-tokyo-1  = "ocid1.image.oc1.ap-tokyo-1.aaaaaaaa4hzluwszvbv3m3m27pvly5qm6ldnjgibjxrexuhe4ky5ncijjsra"
      }
    }

    这里的ocid则是根据 Images@Oracle Cloud Infrastructure Documentation 列出的所有镜像选择而来。更具扩展性的做法应该是通过terraform data对象去获取。

    计算实例的配置

    在OCI中,计算实例的配置,相对来说是比较简单的:

    # Creates an instance (without assigning a public IP to the primary private IP on the VNIC)
    resource "oci_core_instance" "oi" {
      availability_domain = data.oci_identity_availability_domain.oad.name
      compartment_id      = oci_identity_compartment.oic.id
      display_name        = var.naming
      fault_domain        = var.zone_primary
      shape               = var.vm_instance_type
      shape_config {
        memory_in_gbs = 2
        ocpus = 1
      }
    
      source_details {
        source_type = "image"
        source_id   = var.instance_image_ocid[var.region]
        boot_volume_size_in_gbs = 50
      }
    
      create_vnic_details {
        assign_public_ip = true
        display_name     = "Vnic${var.naming}"
        subnet_id        = oci_core_subnet.subnet_primary.id
        hostname_label   = var.naming
      }
      metadata = {
        ssh_authorized_keys = var.publickey
      }
      preserve_boot_volume = false
    }

    上面通过:

    • source_details描述了使用的镜像以及启动盘的大小
    • create_vnic_details描述了VNIC的主要配置,包括所属子网、是否有绑定公网IP等
    • metadata则描述了ssh的公钥信息,实现秘钥对登录

    创建数据库实例

    在OCI上创建MySQL实例比较简单,选项也不多,实际在通过Terraform配置也比较简单和顺利:

    resource "oci_mysql_mysql_db_system" "om" {
    
      display_name = var.naming
      compartment_id = oci_identity_compartment.oic.id
    
      availability_domain = data.oci_identity_availability_domain.oad.name
      fault_domain = var.zone_primary
      is_highly_available = true
    
      admin_password = var.db_pass
      admin_username = var.db_user
    
      shape_name = var.rds_instance_type
      data_storage_size_in_gb = 100
    
      subnet_id = oci_core_subnet.subnet_primary.id
    
      ## this appear as optional in documentation
      ## but it is a must to add it
    
      deletion_policy {
        #Optional
        # automatic_backup_retention = false
        final_backup = "SKIP_FINAL_BACKUP"
        is_delete_protected = false
      }
    }

    问题

    400-InvalidParameter

    参数值错误有很多,根据报错这里是automaticBackupRetention相关的参数值错误,对应在Terraform中是deletion_policy中的automatic_backup_retention配置项,该选项并不是必须的,暂时删除解决。

    ╷
    │ Error: 400-InvalidParameter, Request contains an invalid value for 'com.oracle.oci.mysql.model.CreateDbSystemDetails$Builder["deletionPolicy"]->com.oracle.oci.mysql.model.CreateDeletionPolicyDetails$Builder["automaticBackupRetention"]'
    │ Suggestion: Please update the parameter(s) in the Terraform config as per error message Request contains an invalid value for 'com.oracle.oci.mysql.model.CreateDbSystemDetails$Builder["deletionPolicy"]->com.oracle.oci.mysql.model.CreateDeletionPolicyDetails$Builder["automaticBackupRetention"]'
    │ Documentation: https://registry.terraform.io/providers/oracle/oci/latest/docs/resources/mysql_mysql_db_system
    │ API Reference:
    │ Request Target: POST https://mysql.ap-tokyo-1.ocp.oraclecloud.com/20190415/dbSystems
    │ Provider version: 5.42.0, released on 2024-05-19.
    │ Service: Mysql Db System
    │ Operation Name: CreateDbSystem
    │ OPC request ID: ...
    │
    │
    │   with oci_mysql_mysql_db_system.om,
    │   on rds.mysql.tf line 6, in resource "oci_mysql_mysql_db_system" "om":
    │    6: resource "oci_mysql_mysql_db_system" "om" {
    │

    参考链接