Smile -- toss the feathers

mysql optimization

-:MYSQL OPTIMIZATION:-

First Way:-

1) query_cache_type: Specifies the operating mode of the query cache.
Three possible values can be assigned to this variable: 0, 1, and 2.


Finding info about Query_cache_type

show variables like “query_cache_type”;

Setting Size of Query_cache_type

set GLOBAL query_cache_type=0;// off
set GLOBAL query_cache_type=1;// on
set GLOBAL query_cache_type=2;// no Sql cache

2) query_cache_limit: Specifies the maximum size that a result set can be in order to be cached. For example, if the limit is set to 2M, no result set larger than 2MB will be cached. The default limit is 1MB.

Finding info about Query_cache_limit

show variables like “query_cache_limit”;

Setting Size of Query_cache_limit

set GLOBAL query_cache_limit=1000000;// 10MB set


3) query_cache_size: Specifies the amount of memory allocated for caching queries. By default, this variable is set to 0, which means that query caching is turned off. To implement query caching, you should specify a query_cache_size setting in the [mysqld] section of your option file. For example, the setting query_cache_size=10M enables query caching and allocates 10M of memory to the cache.

Finding info about Query_cache_size

show variables like “query_cache_size”;

Setting Size of Query_cache_size

set GLOBAL query_cache_size=1000000;// 10MB set





Second Way:-

1) use a text editor such as Vim or Notepad to modify your option file to include a setting for the query_cache_size system variable. For Linux users, add a query_cache_size entry to the [mysqld] section of the my.cnf file in the root directory.

query_cache_size=10M

2) For the changes in the option file to take effect, you must shut down the MySQL server. In Linux, execute the following command at your operating system’s command prompt:

mysqladmin -u root -p shutdown

When prompted for a password, enter your password and press Enter. The service is stopped.

3) Now you must restart the MySQL server. In Linux, execute the following command at your operating system’s command prompt:

mysqld_safe --user=mysql &

4). Open the mysql client utility.

5). Now view the settings for the query_cache_size system variable again and you are ready to go with new settings.


How It Works

All the steps that you took in this exercise should be familiar to you from previous chapters. To begin, you used the SHOW VARIABLES statement to view the default settings for each system variable related to the query cache. For example, the following SHOW VARIABLES statement retrieved the current setting for the query_cache_limit system variable:

SHOW VARIABLES LIKE ‘query_cache_type’;

The results indicated that the query cache is on. When you viewed the setting for the query_cache_limit variable, you saw that each results set can be 1048576, or 1M. Next you viewed the query_cache_size variable, which was set to 0. This meant that no SELECT statement result sets were being cached.

Once you verified the settings for all three system variables, you added or updated the following setting in the [mysqld] section of your option file:

query_cache_size=10M

This entry sets the query cache size to 10M. You implemented the new setting by stopping the server and then restarting it. From there, you opened the MySQL client utility and used the SHOW VARIABLES statement once more to verify the query_cache_size system setting. The results indicated that the new setting had been implemented. Now your SELECT statements will be cached, which should improve the performance of your SELECT statements. You could have also specified different settings
for the query_cache_type and query_cache_limit system variables in your option file, but it wasn’t necessary. Because you didn’t, the default values for both variables are used.