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

westernx · « **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)

westernx · « **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

westernx · « **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

jburk · « **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 · « **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? . . .