Author Topic: Re: Help Customizing MySQl for Qube support  (Read 3680 times)

westernx

  • Hero Member
  • *****
  • Posts: 55
Re: Help Customizing MySQl for Qube support
« on: September 21, 2010, 11:18:06 PM »
In regards to an older topic http://www.pipelinefx.com/forum/index.php?topic=802.0

John,

Can I still increase my MySQL open files limit to even more?  And if so can I also ajust the max_connections in /etc/my.cnf and some settings in the supe qb.conf for even more performance?  We currently only have 50 render servers,  and our supervisor is an older Xserver with 4 cores and 4 gigs of RAM.

currently ;

Code: [Select]
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 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 202   |
+-----------------+-------+
1 row in set (0.00 sec)

« Last Edit: September 21, 2010, 11:30:22 PM by ryjguy7 »

westernx

  • Hero Member
  • *****
  • Posts: 55
Re: Help Customizing MySQl for Qube support
« Reply #1 on: September 21, 2010, 11:20:19 PM »
Our current /etc/my.cnf;

Code: [Select]
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 4500
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 32
query_cache_size = 256M
max_connections = 202
thread_concurrency = 8
skip-thread-priority
skip-innodb
open_files_limit = 10000
« Last Edit: September 21, 2010, 11:27:40 PM by ryjguy7 »

westernx

  • Hero Member
  • *****
  • Posts: 55
Re: Help Customizing MySQl for Qube support
« Reply #2 on: September 21, 2010, 11:25:04 PM »
Settings in /etc/qb.conf;

Code: [Select]
supervisor_max_clients = 256
supervisor_max_threads = 192
supervisor_idle_threads = 64

Settings in /Library/StartupItems/MySQLCOM/MySQLCOM;

Code: [Select]
launchctl limit maxfiles 8192 10240
ulimit -n 10240

. . . essentially I want to improve performance even more . . . if this is possible.

R
« Last Edit: September 21, 2010, 11:28:18 PM by ryjguy7 »

jburk

  • Administrator
  • *****
  • Posts: 493
Re: Help Customizing MySQl for Qube support
« Reply #3 on: September 22, 2010, 03:10:44 PM »
To increase the number of filehandles, you'll first have to increase the kernel's limit.  Do so at your own risk, and I recommend trying it in small-ish increments to avoid swamping your system.

If you need to go to a really large number of open filehandles, I'd recommend that your next supervisor be on a RHEL/CentOS machine...
 
The default limits on OSX are:
Code: [Select]
jburk> sysctl kern.maxfiles;sysctl kern.maxfilesperproc
kern.maxfiles: 12288
kern.maxfilesperproc: 10240

You'll need to create /etc/sysctl.conf, and add the new values into it (check this thread: http://discussions.apple.com/thread.jspa?threadID=2374157)
Code: [Select]
sudo vi /etc/sysctl.conf
kern.maxfiles=25000 (arbitrary value)
kern.maxfilesperproc 20000

Then instead of playing with launchctl, edit the MySQLCOMM StartupParameters.plist, and set the limits in there with "HardResourceLimits" and "SoftResourceLimits":
Code: [Select]
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>Description</key>
<string>MySQL 5.1.44-community</string>
        <key>OrderPreference</key>
<string>None</string>
<key>Provides</key>
<array>
<string>MySQL</string>
</array>
<key>Uses</key>
<array>
<string>Network</string>
<string>Resolver</string>
</array>
<key>HardResourceLimits</key>
<dict>
<key>NumberOfFiles</key>
<integer>20000</integer>
</dict>
<key>SoftResourceLimits</key>
<dict>
<key>NumberOfFiles</key>
<integer>20000</integer>
</dict>
</dict>
</plist>



westernx

  • Hero Member
  • *****
  • Posts: 55
Re: Help Customizing MySQl for Qube support
« Reply #4 on: September 22, 2010, 04:45:16 PM »
. . . cool.  I do have a really good file server running Suse Enterprise 10.  I would put a supervisor on that machine but I am worried about slowing down the network, as it manages our RAID system for VFX.  But it has like 24GB of RAM and 8 Intel Xeon cores at 2.5GHz.  It does have a smaill local raid though of three drives.  I guess I could put the mysql databases on that raid, or link the /var/spool/qube/job folder to that RAID.  What do you think? . . .