Help Customizing MySQl for Qube support

[westernx]

Hello,

  I am interested in configuring our mac mysql server to be optimized for our current qube setup.  We are running an Xserve 10.5.8 with a qube supervisor 5.4.6 on the same machine.  We have a render farm of 50 servers all with 8 core Intel Xeon's @ 2.5GHz.  We have about 30 desktop clients running qube 5.4.6, some running 64bit fedora 8, but most renders are run from clients using Mac OS X 10.5.8.  We are experiencing unnecessary slow rendering. I would like to configure /etc/my.cnf for

mysql  Ver 14.12 Distrib 5.0.82, for apple-darwin9.0 (i386) using  EditLine wrapper

. . . on the Mac OS X server to be setup efficiently.  The current default setup of /etc/my.cnf is ;

[client]
port      = 3306
socket      = /var/mysql/mysql.sock

[mysqld]
port      = 3306
socket      = /var/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
skip-thread-priority

log-bin=mysql-bin

server-id   = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Any help would be greatly appreciated,

ryjguy7

[jburk]

First thing is to allow the mysql user on your server to open more than the default number of file handles (512).

Put the following into the MySQL startup file, usually located at /Library/StartupItems/MySQLCOM/MySQLCOM

launchctl limit maxfiles 8192 10240   
ulimit -n 10240

make these my.cnf changes:
========================
# you have an 8-core box, right? concurrency should be set to nCores * 2
thread_concurrency = 16
thread_cache_size = 32

query_cache_size = 256M
key_buffer_size = 384M

table_cache = 4500
open_files_limit = 10000

skip-innodb

# !! COMMENT out the log-bin line; you don't want/need binary logging!
#log-bin
# ====== < end of my.cnf changes >  ==================


restart your mysql (sudo SystemStarter restart MySQL), and verify that you can open a larger number of files, and you have bin-logging disabled:

mysql> SHOW VARIABLES LIKE 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 10240 |
+------------------+-------+

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+

and run similar commands for all the other settings that you just modified.  Or you can just run "SHOW GLOBAL VARIABLES" and sort through it.

[westernx]

. . . very cool, thanks. should anything be changed in the supe qb.conf?

ryjguy7

[westernx]

. . . actually the OS X server is only 4 core . .

ryjguy7

[westernx]

. . . so I changed

thread_concurrency = 8
thread_cache_size = 16

in conjunction with the 4 cores on the server . . .

[westernx]

. . . wow I was looking around on the website and found your tuning for high performance case study ;

http://www.pipelinefx.com/download/16

. . . kinda a funny 'cause I worked for Vanguard in IT on Space Chimps . . . you might remember me, Ryan

anyway here's my email

letourneau.ryan@gmail.com

Thanks for your help

[jburk]

Yup, I remember you.  Are you at Western Post/KeyPics now?

qb.conf should have:

supervisor_max_threads = 128 or higher.  192 might even work
supervisor_max_clients = 256
supervisor_idle_threads = 35 or so. 

keep an eye on the number of supe threads "ps -ef|grep supervisor" and if the number is often above 35, set it to 50 or 64.

Ensure you tweak the mysql max_connections to be at least 10 higher than qube's supervisor_max_threads.  You can set it while the mysql is running, just be sure to also put the change in the my.cnf

check the output from qbadmin s --config to check your current values, then restart the supe and re-run it to make sure your changes took.

[westernx]

. . . thanks again!  Ya, I took over for Matt here at Keypics.  Just trying to get some better performance outa' things here . . .

Ryan

[westernx]

. . . o.k!  It seems all the new settings are improving rendering performance greatly.

Jobs are now consistently progressing, where as before they would reach a certain point and seem to hang.

. . . mysql settings ;

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 10240 |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 140   |
+-----------------+-------+
1 row in set (0.00 sec)

. . . and the supe qb.conf in Qube! ;

supervisor_max_clients = 256
supervisor_max_threads = 128
supervisor_idle_threads = 64

with a ps -e | grep supervisor

we are getting about 70 - 75 spawned supe threads