Mysql

如何加速 SELECT BENCHMARK(1000000,ENCODE(‘hello’,RAND()));

  • July 3, 2017

我想要一些加快以下查詢的想法:

SELECT BENCHMARK(1000000,ENCODE('hello',RAND()));

目前,我一直在玩 my.cnf 選項,我可以在 9.5 秒內執行它……但是,有什麼方法可以讓它更快嗎?

什麼樣的變數與這個查詢更相關?更快的 CPU 會產生更好的結果,它需要更快的磁碟,還是需要更多/更快的 RAM?

我正在使用一個 50Gb 的持久 SSD 磁碟,它應該給我 1500 次 iops(我知道這不是最快的……但是這對這個測試有影響嗎?)

這是 Percona Server for MySQL 的全新安裝,沒有任何數據庫。

mysql  Ver 14.14 Distrib 5.7.18-15, for debian-linux-gnu (x86_64) using  6.3

系統是 Google Cloud 上的 2 Skylake CPU 核心,具有 6 GB RAM。

cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 85
model name      : Intel(R) Xeon(R) CPU
stepping        : 3
microcode       : 0x1
cpu MHz         : 2000.064
cache size      : 56320 KB
physical id     : 0
siblings        : 2
core id         : 0
cpu cores       : 1
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc eagerfpu pni pclmulqdq ssse3 fma cx16 sse4_1 sse4_2 x2apic movbe popcnt aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms rtm avx512f avx512dq rdseed adx clflushopt clwb avx512cd avx512bw avx512vl xsaveopt
bugs            :
bogomips        : 4000.12
clflush size    : 64
cache_alignment : 64
address sizes   : 46 bits physical, 48 bits virtual
power management:

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 85
model name      : Intel(R) Xeon(R) CPU
stepping        : 3
microcode       : 0x1
cpu MHz         : 2000.064
cache size      : 56320 KB
physical id     : 0
siblings        : 2
core id         : 0
cpu cores       : 1
apicid          : 1
initial apicid  : 1
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc eagerfpu pni pclmulqdq ssse3 fma cx16 sse4_1 sse4_2 x2apic movbe popcnt aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms rtm avx512f avx512dq rdseed adx clflushopt clwb avx512cd avx512bw avx512vl xsaveopt
bugs            :
bogomips        : 4000.12
clflush size    : 64
cache_alignment : 64
address sizes   : 46 bits physical, 48 bits virtual
power management:

這是我目前的測試配置:

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/run/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/run/mysql/mysql.sock
pid-file                       = /var/run/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 1G
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now                 = 1
innodb                         = FORCE

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 128
thread-cache-size              = 100
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 2000
join-buffer-size               = 512K
sort-buffer-size               = 512K

# LOGGING #
log-error                      = /var/log/mysql/mysql-error.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 0
slow-query-log-file            = /var/log/mysql/mysql-slow.log

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 64M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 512M

# extra
innodb_doublewrite = 0
innodb_log_buffer_size = 64M

實際上,在測試了幾台伺服器之後,出現了一種模式。

這個查詢依賴於一個快速的 CPU,所以當我使用一個專用的 cpu 實例,而不是我們在雲服務上看到的共享核心時,查詢的速度會提高。

因此,CPU 受限。

引用自:https://serverfault.com/questions/859177