[MDEV-20613] wordpress benchmark shows very unstable with mariadb 10.4.x Created: 2019-09-18  Updated: 2021-09-16  Resolved: 2021-09-16

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.4.7, 10.4.8
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: yuma Assignee: Axel Schwenke
Resolution: Won't Fix Votes: 0
Labels: need_feedback
Environment:

ubuntu 19.04


 Description   

when running wordpress benchmark with https://gitlab.devtools.intel.com/DCSP/HHVM/oss-performance-php, the result is fluctuating from 200 to 4600, this problem does not exist on 10.3.17.

Details:
wordpress transaction rate

10.4.8 10.3.17
4679.19 4736.24
4674.74 4723.28
202.87 4720.29
4675.32 4723.07
792.11 4720.11

Env:
ubuntu 19.04
php7.3
siege 4.0.4

Steps:
git clone the workload from https://github.com/facebookarchive/oss-performance and set up through below steps:
$cd oss-performance-php
$wget https://getcomposer.org/installer -O composer-setup.php
$php -r "if (hash_file('sha384', 'composer-setup.php') === 'a5c698ffe4b8e849a443b120cd5ba38043260d5c4023dbf93e1558871f1f07f58274fc6f4c93bcfd858c6bd0775cd8d1')

{ echo 'Installer verified'; }

else

{ echo 'Installer corrupt'; unlink('composer-setup.php'); }

echo PHP_EOL;"
$php composer-setup.php --disable-tls
$php composer.phar install
$php perf.php --wordpress --php=/usr/bin/php-fpm (Note: update the path to the php-fpm on your machine)

And get result as below, Siege RPS:
{
"Combined":

{ "Siege requests": 277358, "Siege wall sec": 0.04, "Siege RPS": 4625.72, "Siege successful requests": 264034, "Siege failed requests": 1, "Nginx hits": 277578, "Nginx avg bytes": 30166.824334061057, "Nginx avg time": 0.04225272896268245, "Nginx P50 time": 0.042, "Nginx P90 time": 0.057, "Nginx P95 time": 0.062, "Nginx P99 time": 0.072, "Nginx 200": 252279, "Nginx 502": 1, "Nginx 301": 11943, "Nginx 404": 13355, "canonical": 1 }

}

 Comments   
Comment by Axel Schwenke [ 2019-09-18 ]

I could not even clone the repository:

$git clone  https://gitlab.devtools.intel.com/DCSP/HHVM/oss-performance-php
 
Cloning into 'oss-performance-php'...
 
fatal: unable to access 'https://gitlab.devtools.intel.com/DCSP/HHVM/oss-performance-php/': Could not resolve host: gitlab.devtools.intel.com

and indeed:

dig gitlab.devtools.intel.com
 
 
 
; <<>> DiG 9.11.3-1ubuntu1.5-Ubuntu <<>> gitlab.devtools.intel.com
 
;; global options: +cmd
 
;; Got answer:
 
;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 35197
 
;; flags: qr rd ra; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 1
 
 
 
;; OPT PSEUDOSECTION:
 
; EDNS: version: 0, flags:; udp: 4096
 
; COOKIE: c7f64d296981f4a35fead9535d81dc27b31e4a0192072447 (good)
 
;; QUESTION SECTION:
 
;gitlab.devtools.intel.com.     IN      A
 
 
 
;; AUTHORITY SECTION:
 
intel.com.              860     IN      SOA     ns1.intel.com. hostmaster.intel.com. 128392 43200 300 1209600 900

Also: did you use any my.cnf or was MariaDB running with defaults?

Comment by yuma [ 2019-09-18 ]

update the workload link from github
For my.cnf, the data is collected based on custermized my.cnf. But seems default my.cnf also have this problem, I'll double confirm.

# this is only for the mysqld standalone daemon
 
[mysqld]
 
 
 
# Disabling symbolic-links is recommended to prevent assorted security risks
 
symbolic-links=0
 
 
 
# Settings user and group are ignored when systemd is used.
 
# If you need to run mysqld under a different user or group,
 
# customize your systemd unit file for mariadb according to the
 
# instructions in http://fedoraproject.org/wiki/Systemd
 
open_files_limit=4000
 
# Having the query cache on makes mysql threads wait for acquiring
 
# the query cache lock, making the system manifest low CPU utilization
 
query_cache_limit=0
 
query_cache_size=0
 
query_cache_type=0
 
#Enable networking
 
bind-address = 0.0.0.0
 
#Fine tuning
 
key_buffer              = 16M # TODO: This should actually be key_buffer_size
 
max_allowed_packet      = 16M
 
thread_stack            = 192K
 
thread_cache_size       = 2048
 
#thread_handling=pool_of_threads
 
skip-external-locking
 
key_buffer_size = 384M
 
max_allowed_packet = 1M
 
table_open_cache = 512 
sort_buffer_size = 2M
 
read_buffer_size = 2M
 
read_rnd_buffer_size = 8M
 
myisam_sort_buffer_size = 64M
 
 
 
innodb_buffer_pool_size = 8G
 
innodb_buffer_pool_instances = 8
 
aria_pagecache_buffer_size = 128M
 
tmp_table_size = 1G
 
max_heap_table_size = 1G
 
#innodb_thread_concurrency = 80
 
#innodb_adaptive_hash_index = OFF
 
# The thread_concurrency is deprecated, no need to use it
 
# thread_concurrency = 72
 
# Increase number of maximum connections (used by oss-performance)
 
max_connections        = 1000
 
 
 
myisam-recover         = BACKUP
 
#table_cache = 64
 
skip-grant-tables

Comment by Marko Mäkelä [ 2019-09-18 ]

mayujsw, could you please post some profiler output that highlights the new bottlenecks in 10.4? Myself, I would use sudo perf record during the workload, and then analyze sudo perf report. Alternatively, one could check the live output of sudo perf top when the workload is in a steady state for a longer period.

Comment by Axel Schwenke [ 2019-09-24 ]

I reproduced a performance regression from MariaDB 10.3 (tested: 10.3.18) to MariaDB 10.4 (tested: 10.4.6, 10.4.8). The overall RPS (HTTP requests per second) was ~1000 for MariaDB 10.3 and dropped to ~420 for MariaDB 10.4. This is the average for the 1 minute benchmark duration. More research is needed.

Comment by Axel Schwenke [ 2019-09-24 ]

'perf top' during the benchmark shows heavy activity of __pthread_mutex_lock from libpthread.so for 10.4.18 where no such activity can be seen for 10.3.18.

Comment by yuma [ 2019-10-18 ]

Thanks Axel, so is there any more progress on this issue

Comment by Axel Schwenke [ 2021-09-16 ]

10.4 is very old. If this problem still exists with a newer version, please open a new Jira issue.

Generated at Thu Feb 08 09:00:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.