Optimize mySQL and Apache Print

Optimize mySQL:

Memory usage is an important thing when consider the server. Normally mySQL and Apache takes high memory on the server. Here we are going to discuss

” How to Optimize the mySQL and Apache service to consume low memory ? ”

Instructions for Optimization:

• login to the root shell
• to optimize mySQL we need to edit the mySQL configuration file. So, edit the my.cnf file
• vi /etc/my.cnf – to edit the conf file, Normally it looks like the bellow

     [mysqld]
     port            = 3306
     socket          = /var/lib/mysql/mysql.sock
     skip-locking
     key_buffer = 16K
     max_allowed_packet = 1M
     table_cache = 4
     sort_buffer_size = 64K
     read_buffer_size = 256K
     read_rnd_buffer_size = 256K
     net_buffer_length = 2K
     thread_stack = 64K

     # For low memory, Berkeley DB should not be used so keep skip-bdb uncommented unless required
     skip-bdb

     # For low memory, InnoDB should not be used so keep skip-innodb uncommented unless required
     skip-innodb

     # Uncomment the following if you are using InnoDB tables
     #innodb_data_home_dir = /var/lib/mysql/
     #innodb_data_file_path = ibdata1:10M:autoextend
     #innodb_log_group_home_dir = /var/lib/mysql/
     #innodb_log_arch_dir = /var/lib/mysql/
     # You can set .._buffer_pool_size up to 50 - 80 %
     # of RAM but beware of setting memory usage too high
     #innodb_buffer_pool_size = 16M
     #innodb_additional_mem_pool_size = 2M
     # Set .._log_file_size to 25 % of buffer pool size
     #innodb_log_file_size = 5M
     #innodb_log_buffer_size = 8M
     #innodb_flush_log_at_trx_commit = 1
     #innodb_lock_wait_timeout = 50

     [mysqldump]
     quick
     max_allowed_packet = 16M

     [mysql]
     no-auto-rehash
     # Remove the next comment character if you are not familiar with SQL
     #safe-updates

     [isamchk]
     key_buffer = 8M
     sort_buffer_size = 8M

     [myisamchk]
     key_buffer = 8M
     sort_buffer_size = 8M

     [mysqlhotcopy]
     interactive-timeout

Every parameter which is present the my.cnf file, have their own effect in the mySQL service. We will see about some parameters below,

query_cache_size:

MySQL provides one feature that can prove very handy – a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.

key_buffer :

The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).

table_cache:

The default is 64. Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.

sort_buffer:

The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.

thread_cache:

If you have a busy server that’s getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.

Apache:

The biggest problem with Apache is the amount of ram is uses. We will discuss the following techniques for speeding up Apache and lowering the ram used.

• Loading Fewer Modules
• Handle Fewer Simultaneous Requests
• Recycle Apache Processes
• Use KeepAlives, but not for too long
• Lower your timeout
• Log less
• Don’t Resolve Hostnames
• Don’t use .htaccess

Loading Fewer Modules :

First thing, get rid of unnecessary modules. Look through your config files and see what modules you might be loading. If you’re not using modules, by all means, don’t load them. It saves quit ram usage.

Handle Fewer Simultaneous Requests:

In http.conf file,

StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 20
MaxRequestsPerChild 0

Try give some value as small.

Lower Your Timeout:

Since you’re limiting the number of processes, you don’t want one to be “stuck” timing out for too long, so i suggest you lower your “normal” Timeout variable as well.

Don’t Use .htaccess:

You have  probably seen the AlowOverride None command.  This says, “don’t look for .htaccess files” Using .htaccess will cause Apache to

• look for files frequently
• parse the .htaccess file for each request. If you need per-directory changes, make the changes inside your main Apache configuration file, not in .htaccess.

Have a nice day!…