MySQL Tuning
Posted by John
on Monday, 03 December 2007
Thanks to PickledOnion and SliceHost for posting this on their tutorials site, here's a quick example to improve your MySQL speed.
Tuning your Install
Remote onto your box via SSH, and...
sudo nano /etc/mysql/my.cnf
Go down till you see a section for the [mysqld] settings, then add...
default-storage-engine = MyISAM
This will set MySQL to use the MyISAM storage engine (lighter than InnoDB) for all newly created databases.
Now look for anything called...
#skip-innodb
delete the # to make skip loading the InnoDB engine, if you're not using it, changing it to...
skip-innodb
NOTE: Ubuntu Gutsy MySQL 5 uses InnoDB by default (i've found) , so if you haven't set the type and your on Ubuntu Gutsy with a database already on there; then chances are it's under InnoDB. so skip this one for now (see bottom).
Now navigate to the Fine Tuning section and change the values to:
# * Fine Tuning
#
key_buffer = 16K
max_allowed_packet = 1M
thread_stack = 64K
thread_cache_size = 4
Add these also,
sort_buffer = 64K
net_buffer_length = 2K
Save the file and restart MySQL with...
sudo /etc/init.d/mysql restart
Job done, you can run:
top
To see your memory usage (exit with 'q')
Convert Tables to MyISAM
Log into your MySQL server via...
mysql -u root
Now type,
show databases;
Choose your database,
use my_db;
Now see what tables are around,
show tables;
Now you can run a command to convert your tables over to the MyISAM format,
ALTER TABLE my_table ENGINE = MyISAM;
More here...
Restart MySQL
Easily done via,
sudo /etc/init.d/mysql restart
And If All Fails
Now for me those settings weren't completely ideal for Typo5's performance so I switched them back to the default settings, with my other apps they were fine (think they're good for memory-starved situations).
The default settings are...
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
#sort_buffer = 64K
#net_buffer_length = 2K
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
Typo Tuning
Posted by John
on Friday, 09 November 2007
A note on permissions from the Typo README:
"Typo needs write access to several directories in order to function correctly. These need to be writable by the user that runs the Typo process--in a hosted environment this may be your user; on dedicated systems it may be something like 'httpd' or 'www-data'.
The specific directories in question are 'log/' (and everything underneath it), 'cache/', and 'public/'. Strictly speaking, Rails will continue to work if public isn't writable, but Typo's page caching code will work properly and this will cause Typo to be slower and use much more CPU time. For the security conscious, Rails really only needs the ability to change a half-dozen files and subdirectories under public/, ask on the Typo mailing list for more details.
aka...
cd /mytypoinstall
mkdir cache
chmod -R a+rw cache
chmod -R a+rw public
chmod -R a+rw log
Done.
Tuning your SVN Deployment
Posted by John
on Thursday, 23 August 2007
One of the problems you may run into with a project that has been deployed with SVN is choosing what it should ignore so that the production app does not fall over itself.
E.g. accidentally putting log files under version control and then deploying them to the server knowing your box will alter these over time.
So how can you get around this, pretty simple really; but first off try the following commands...
Reverting a Directory
svn revert log/*
This basically tells svn to revert everything in the log directory to it's previous state.
Ignoring .log files
svn propset svn:ignore ".log" log
This tells svn to ignore any .log files within the log directory, so the log files generated on your production box aren't the ones from your laptop.
Ignoring mongrel .pid's
svn propset svn:ignore ".pid" pid
This like the command above will ignore the .pid files generated on your laptop, not taking these to your production box.
Ignoring the contents of a Directory
To go one step more, how about when you want the entire contents of a directory ignored from SVN. So say the photos generated on your production box don't get overwritten from your development laptop.
svn propset svn:ignore '*' photo
Enjoy,
