A Guide to Technology

Does your website take too much time to load? The load time of your website could be a crucial parameter that makes it impressive and useful for its end users.

Normally the websites with too many graphics and heavy designs take long time to load. However, sometimes even simple and light weight  designed websites take exceptionally long time to load.

While there are many factors that contribute to the load time, one important factor is the SQL performance. If your website uses a very big database and there are many DB queries then this too contributes to the long load times.

In this case you have 2 tricks to enhance the performance of your website:

1. Enable Mysql query Cache
2. Or Install APC Cache

Enable MySql query catch

Before you do this, you need to set few variables in mysql configuration file (usually is my.cnf or my.ini)

- 1st, set query_cache_type to 1. (There are 3 possible settings: 0 (disable / off), 1 (enable / on) and 2 (on demand).

query_cache_type = 1

- 2nd, set query_cache_size to your expected size. I’d prefer to set it at 1000MB.

query_cache_size = 1000M

If you set your query_cache_type = 2 ( on demand ), you would want to modify your sql query to support cache.

SELECT SQL_CACHE field1, field2 FROM table1 WHERE field3 = ‘yes’

To check if your mysql servers already enable query cache, simply run this query:-

SHOW VARIABLES LIKE ‘%query_cache%’;

You will see this result:-

+——————-+———+
| Variable_name | Value |
+——————-+———+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_size | 20971520 |
| query_cache_type | ON |
+——————-+———+
4 rows in set (0.06 sec)

To check if your MySQL query cache is working, simply perform a sql query for 2 times and check the query cache variable like below:- SHOW STATUS LIKE ‘%qcache%’;

+————————-+———-+
| Variable_name | Value |
+————————-+———-+
| Qcache_queries_in_cache | 1 |
| Qcache_inserts | 3 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_free_memory | 20947592 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 4 |
+————————-+———-+

Install APC Cache

1. ssh telnet into your server as root user
2. Grab APC 3.0.8 by typing
   

   cd /usr/local/src

   wget http://pecl.php.net/get/APC-3.0.8.tgz

   wait for package to download

3. find phpize and php-config paths type:
   

   whereis phpize

   output looks like:

   phpize: /usr/bin/phpize /usr/local/bin/phpize

   whereis php-config

   output looks like:

   php-config: /usr/bin/php-config /usr/local/bin/php-config

4. extract files type:
   

   gunzip -c APC-3.0.8.tgz | tar xf -

5. change to directory:
   

   cd APC-3.0.8

6. install referencing the paths in step #3 type the following 4 lines one after the other
   

   /usr/bin/phpize

   ./configure –enable-apc –enable-apc-mmap –with-apxs=/usr/local/apache/bin/apxs –with-php-config=/usr/bin/php-config

   make

   make install

   Take note of where it copies apc.so file i.e.

   /usr/lib/php/extensions/no-debug-non-zts-20020429/apc.so

7. Now locate php.ini type:
   

   whereis php.ini

   output looks like:

   php: /usr/bin/php /usr/lib/php /usr/lib/php.ini /usr/local/lib/php.ini
   /usr/include/php /usr/man/man1/php.1

8. Edit php.ini using pico, vi or nano text editors in linux and put this line
   

   extension=”/usr/lib/php/extensions/no-debug-non-zts-20020429/apc.so”

   apc.shm_size = 32

9. save php.ini file and restart Apache for APC to load
   

   /etc/rc.d/init.d/httpd stop

   /etc/rc.d/init.d/httpd start

10. check phpinfo.php for APC section
11. apc.php admin file in /usr/local/src/APC-3.0.8 can be copied to password protected web site directory and viewed. Edit apc.php top line to change password to something different from default.