TechniTip.Net

MySQL Performance Tips

January 11th, 2009 Category: PHP/MySQL

Optimizing a MySQL server is probably not very easy. It depends on your database structure, how many users are accessing your server and so on.

Basics

But there are a few things which could help to improve your performance. On my configuration I’ve added skip-name-resolve in my.cnf:

[mysqld]
skip-name-resolve

Now the MySQL server will not do any DNS name resolves. Please note that permissions based on hostnames will no longer work!

For security reasons I would also recommend binding only to the localhost address. Using this the MySQL server is not reachable on the Ethernet interface. In earlier version of MySQL this was usually done with the skip-networking parameter.

[mysqld]
bind-address    = 127.0.0.1

And I’ve added skip-external-locking and skip-locking:

[mysqld]

skip-external-locking
skip-locking

This could also improve your performance.

Search Length

For text search queries with a allowed length of 2 characters add this line:

ft_min_word_len="2"

Otherwise default minimum length is 3 characters, which means that a search query “select * like ‘%AB%’” will never return any data.

Cache Settings

Caching seems to be a very important thing when optimizing your server. My feeling is that the standard configuration of a MySQL server is not really designed for machines with 1 GByte memory or above.

Below is my configuration, with a few cache settings increased.

[mysqld]
query_cache_limit    =  1M
query_cache_size     = 32M
thread_cache_size    =  8
join_buffer          =  1M
table_cache          = 16M
sort_buffer_size     =  2M
read_buffer_size     =  2M
read_rnd_buffer_size =  2M
tmp_table_size       = 48M
max_allowed_packet   = 16M
thread_stack         = 128K
key_buffer_size      = 32M

This configuration works on a machine with 1 GByte RAM and several databases running. Basically I recommend to increase the cache size settings. And memory helps almost always.

Applies to MySQL version: 5.0.32

Essential Security Tips for Windows XP

January 1st, 2009 Category: General

My personal recommendations for secure internet usage are easy and help a lot to avaid getting infected with unwanted software like a virus or trojaner. So take the following tips to heart:

• Activate Windows update and always install the updates
• Use a firewall/router. Best case is e.g. a hardware DSL router. If now hardware firewall can be used activate at least the Windows firewall
• Use a secure browser and mail reader like Firefox and Thunderbird
• Install a virus scanner which automatically updates it’s signatures

Using this few tips you are already quite secure and it’s not much effort.