Description:
The --skip-thread-priority configuration variable is now deprecated and starting with MySQL 6.0 thread scheduling will no longer be performed (see bug #37536). I executed Sysbench Read-Only and Read-Write tests to determine if --skip-thread-priority was still causing performance degradataion when the number of sysbench concurrent threads was greater than the system core count. This is still the case on MySQL 5.4.0-Beta and the underlying cause is that mysqld threads are being preempted while holding LOCK_open in open_table and close_open_tables
(see attached file longDescription.txt)
How to repeat:
This can be repeated by running sysbench read-only test where number of threads is at lease 2x the number of cores. Both sysbench and mysqld contend for the same cpu resource, that is a host-only run. So for a 16-core system run a test with 32 threads:
1. Start mysql with --skip-thread-priority
mysqld_safe --default-storage-engine=innodb --skip-thread_priority &
2. Execute sysbench read-only tests:
sysbench --mysql-user=mysql --mysql-engine-trx=yes --max-time=$RUNTIME --max-requests=0 --test=oltp --oltp-dist-type=special --oltp-table-size=10000000 --oltp-read-only=on --num-threads=32 run
3. shutdown and restart mysql with thread scheduling
mysqld_safe --default-storage-engine=innodb &
4.Execute sysbench read-only tests:
sysbench --mysql-user=mysql --mysql-engine-trx=yes --max-time=$RUNTIME --max-requests=0 --test=oltp --oltp-dist-type=special --oltp-table-size=10000000 --oltp-read-only=on --num-threads=32 run
my.cnf parameters:
[client]
user=mysql
port = 3306
socket = /tmp/mysql.sock
# The MySQL server
[mysqld]
datadir=/data/mysql-5.1/var
port = 3306
socket = /tmp/mysql.sock
query_cache_size = 0
max_connections=512
innodb_data_home_dir = /data/mysql-5.1/var
innodb_data_file_path = ibdata1:2000M;ibdata2:100M:autoextend
innodb_log_group_home_dir = /home2/mysql-5.1/logs
innodb_buffer_pool_size = 4096M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 400M
innodb_log_buffer_size = 8M
Suggested fix:
By starting mysqld in FX scheduling class w/ priority 59 and --skip-thread-priority, sysbench performance is equivalent or better than running with default thread scheduling. If not starting mysqld as root, the user must be given proc_priocntl privilege.
This patch to mysqld_safe will start mysqld in FX scheduling class priority 59 w/ --skip-thread-priority. If run by a non-root user and it doesn't have proc_priocntl privilege a message will be output to the error log and stdout and mysqld will start with default TS scheduling class.
*** scripts/mysqld_safe Fri Jan 23 13:56:44 2009
--- scripts/mysqld_safe Tue Jan 27 17:36:08 2009
***************
*** 481,486 ****
--- 481,514 ----
fi
fi
+ # If OS is Solaris verify user is has proc_priocntl privilege
+ # and start mysqld in FX class with priority 59
+ # Use NOHUP_NICENESS to do this.
+ SKIP_THREAD_PRIORITY=""
+ if uname -s > /dev/null 2>&1
+ then
+ if test `uname -s` = "SunOS"
+ then
+ # force --skip-thread-priority. This should be
+ # removed in MySQL 6.x and after
+ SKIP_THREAD_PRIORITY="--skip-thread-priority"
+ # If root don't need any privileges
+ if test `id -u` -eq 0
+ then
+ NOHUP_NICENESS="nohup priocntl -e -c FX -p 59 -m 59"
+ else
+ # check for proc_priocntl privilege
+ if ppriv $$ | grep proc_priocntl > /dev/null 2>&1
+ then
+ NOHUP_NICENESS="nohup priocntl -e -c FX -p 59 -m 59"
+ else
+ log_error "[Warning]: $USER does not have proc_priocntl priv. mysqld will run in Solaris TS scheduling class. For optimal performance under heavy load grant $USER proc_priocntl privilege (see man privileges)."
+ fi
+ fi
+ fi
+ fi
+
+
# Try to set the core file size (even if we aren't root) because many systems
# don't specify a hard limit on core file size.
if test -n "$core_file_size"
***************
*** 534,540 ****
cmd="$NOHUP_NICENESS"
for i in "$ledir/$MYSQLD" "$defaults" "--basedir=$MY_BASEDIR_VERSION" \
! "--datadir=$DATADIR" "$USER_OPTION"
do
cmd="$cmd "`shell_quote_string "$i"`
done
--- 562,568 ----
cmd="$NOHUP_NICENESS"
for i in "$ledir/$MYSQLD" "$defaults" "--basedir=$MY_BASEDIR_VERSION" \
! "--datadir=$DATADIR" "$SKIP_THREAD_PRIORITY" "$USER_OPTION"
do
cmd="$cmd "`shell_quote_string "$i"`
done
