Optimizing caches in MySQL 4.1

Snapdragon · Unread post by **Snapdragon** » Sun Jul 09, 2006 12:35 am

Unread post by **scott** » Sun Jul 09, 2006 9:26 am

What did you set your cache size to? I set mine to type 1, and 32M:

query_cache_type=1
query_cache_size = 32M

Snapdragon · Unread post by **Snapdragon** » Sun Jul 09, 2006 12:36 pm

Cache has been 64MB ever since I bought the 4GB of RAM and you told me to upgrade to 4.0

That's held until recently... I need to tweak all the cache variables now.

Unread post by **scott** » Sun Jul 09, 2006 1:29 pm

Or break the domains up, you might have just exceeded the capacity of the box.

Snapdragon · Unread post by **Snapdragon** » Mon Jul 10, 2006 8:26 am

Naw.. I'm not there yet. I just need to let it stretch it's legs into the memory space more.

I had a load of 118.13 yesterday morning, and the machine still responded. I was unzipping a tarball that had a stupidity large log file in it.

jason|xoxide · Mon Jul 10, 2006 10:30 am

For reference, I have included my my.cnf below. This server is a Dual Xeon 2.8GHz w/ 4GB of RAM and (3) 73GB 10K RPM SCSI drives in RAID 5. It runs a few vBulletin sites and averages about 60 queries/sec during off times and jumps as high as 200 queries/second during the evenings.

The biggest performance increase I got, aside from tweaking the buffer sizes over and over until I found what worked best for me, was to move the temp directory to tmpfs.

Also, if you don't use innodb, disable it.

Code: Select all

[mysqld]
safe-show-database
old-passwords
low_priority_updates = 1
thread_concurrency = 4
#tmpdir = /mysqltmp     # On disk for larger operations
tmpdir = /tmp           # On tmpfs for smaller, faster operations
ft_min_word_len = 3
ft_max_word_len = 12
max_connections = 256
key_buffer = 64M
myisam_sort_buffer_size = 256M
join_buffer_size = 1M
read_buffer_size = 512K
sort_buffer_size = 4M
table_cache = 2048
thread_cache_size = 64
interactive_timeout = 300
wait_timeout = 300
connect_timeout = 10
tmp_table_size = 64M
read_rnd_buffer_size = 2097152
bulk_insert_buffer_size = 8M
max_heap_table_size = 64M
max_allowed_packet = 8M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 32M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
myisam_max_extra_sort_file_size = 10737418240
myisam_max_sort_file_size = 10737418240
myisam_repair_threads = 2

[mysqld_safe]
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
ft_min_word_len = 3
ft_max_word_len = 12
key_buffer = 128M
sort_buffer = 128M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

EDIT: ... and ALWAYS 'nice' your manual file operations to +19 to make sure that they don't interfere with anything else running on your box. This is especially important if you don't have a high-end disk setup.

Unread post by **scott** » Mon Jul 10, 2006 6:18 pm

tmpfs is a ram disk, literally working 1 million times faster than disk writes. Same effect as query caching basically, keeps things off the disk.

Snapdragon · Unread post by **Snapdragon** » Fri Jul 21, 2006 6:12 am

jason|xoxide wrote:For reference, I have included my my.cnf below.
EDIT: ... and ALWAYS 'nice' your manual file operations to +19 to make sure that they don't interfere with anything else running on your box. This is especially important if you don't have a high-end disk setup.

Awesome work, thanks muchly.

I got my hands on some used equipment and I'm going to be moving my DB stuff to a pair of dual xeon 2.0 boxes (with HT), and running their disk into a fiber channel chassis with 10 15K 18G drives. THAT should give me some performance

Then I'm going to switch to Virtuozzo w/ Plesk on another pair of dual xeons.

That should set me up for a while... I just have to read up and learn about MySQL load balancing now.