Activities

November 2009
M T W T F S S
« Oct   Dec »
 1
2345678
9101112131415
16171819202122
23242526272829
30  

MySQL server Optimization

Recently I have got an requirement for optimization MYSQL server server on Windows server.

My searching was found something is,

1. MySQL Loves RAM. So always add more RAM on Mysql server

2. Choose Linux as OS because it’s support better I/O disk operation than windows does. Technically twice better than windows without any tuning.

3. There are 2 type of optimization. Query and server optimizations. Querry optimization shows the better performance all the time and server optimization would benefit on the time when server has maximum concurrent conncetion or a peak time of server.

4. Server optimization always related to OS and attached hardware.

5. Not only indexing did all the query performance but also we have to utilize well it with our sql statement. Adding views and store procedure

Using the benchmark() Function

A quick test of your server speed is to use the benchmark() MySQL function to see how long it takes to process a given expression. You can make the expression something simple, such as 10 + 10, or something more extravagant, such as extracting pieces of dates.

No matter the result of the expression, the result of benchmark() will always be 0. The purpose of benchmark() is not to retrieve the result of the expression but to see how long it takes to repeat the expression for a specific number of times. For example, the following command executes the expression 10 + 10 one million times:

mysql> SELECT BENCHMARK(1000000,10+10);
+————————–+
| BENCHMARK(1000000,10+10) |
+————————–+
| 0 |
+————————–+
1 row in set (0.14 sec)

This command executes the date extraction expression, also one million times:

mysql> SELECT BENCHMARK(1000000, EXTRACT(YEAR FROM NOW()));
+———————————————-+
| BENCHMARK(1000000, EXTRACT(YEAR FROM NOW())) |
+———————————————-+

| 0 |
+———————————————-+
1 row in set (0.20 sec)

1. Lock and unlock mysql tables

LOCK TABLE Books READ;
LOCK TABLES Books READ, BookOrders WRITE;

Unlock tables

UNLOCK TABLES;

2. Variables to monitored
Threads_connected
Created_temp_disk_tables
Handler_read_first
http://aarklondatabasetrivia.blogspot.com/2009/04/how-to-lock-and- unlock-tables-in-mysql.html

innodb_buffer_pool_wait_free
key_reads
max_used_connections
open_tables==< tables_cache Select_full_join slow_queries uptime 3. REPAIR TABLE

quick;

4. Enabling/disabling query log without restart in mysql
set sql_log_off = ‘ON’;
turns of the log without restarting

set sql_log_off = ‘OFF’; or  set sql_log_off = 1;

turns it back on

5. Monitor the mysql
> show status;

6. Corrupted data in Mysql
CHECK TABLE table;
REPAIR TABLE table;

7. Performing Database Maintenance
> ANALYZE TABLE orders;
>CHECK TABLE orders, orderitems;
> OPTIMIZE TABLE

8. Errors
a. Too Many Connections, ERROR 1040: Too many connections
[mysqld]
max_connections=200

b. ERROR 2006: MySQL server has gone away
The default timeout interval is eight hours, but this value can be changed using the wait_timeout system variables value is in seconds. But changing “max_allowed_packet” would be a great workaround in most of the cases.

 max_allowed_packet=20M

9. How to enable caching in MySQL
mysql> SET GLOBAL query_cache_size = 16777216; (16Mb)
You can setup them in /etc/my.cnf (Red Hat) or /etc/mysql/my.cnf (Debian) file:

 vi /etc/my.cnf
  query_cache_size = 268435456
  query_cache_type=1
  query_cache_limit=1048576

10. max_write_lock_count = 1
By starting mysqld with a low value for the max_write_lock_count system variable you are forcing

MySQL to temporarily elevate the priority of all SELECT statements that are waiting for a table. After a specific number of inserts to the table occur. This allows READ locks after a certain number of WRITE locks. Is that clear?

11. Creating a MySQL Index – New Table
CREATE TABLE employee_records (
name VARCHAR(50),
employeeID INT, INDEX (employeeID)
)

12. Creating a MySQL Index – Existing Table

CREATE TABLE employee_records2 (name VARCHAR(50), employeeID INT);
CREATE INDEX id_index ON employee_records2(employeeID)
We keep our existing employeeID field and create a new index id_index that is made up of employeeID data.

13. Listing index for a table
show indexes from employee_records2;

14. Disable the general query log at run time:
SET GLOBAL general_log = ‘OFF’;
SET GLOBAL general_log = ‘ON’;

15. Show the mysql version
SELECT VERSION();

16. Enabling slow_log query
SET GLOBAL slow_query_log = ‘ON’;
set GLOBAL slow_query_log_file = “E:/slow_log_querries.log”;

17 . Repairing mysql tables
CHECK TABLE postcode_cords FAST QUICK;
ALTER TABLE postcode_cords ENGINE=INNODB;
OPTIMIZE TABLE postcode_cords ;

16. Creating index
CREATE INDEX idx_username ON users(username);
Here : idx_username is indexer name
username : Indexed fields on the table

17. Listing index of a table
SHOW INDEX FROM users;

Some optimized server variables on my.ini on Intel core2 duo 4 GB windows 2003 server.

[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir=”D:/Program Files/MySQL/MySQL Server 5.1/”
datadir=”D:/Program Files/MySQL/MySQL Server 5.1/data/”
default-character-set=latin1
default-storage-engine=INNODB
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
thread_cache_size=16

### Added the optimized values
max_heap_table_size=64M
query_cache_size=32M
query_cache_type=1
query_cache_limit=1M
max_connections=200
table_cache=1024
tmp_table_size=50M
thread_concurrency=2
log_slow_queries=d:/Temp/mysqld.slow.log
long_query_time=2

read_rnd_buffer_size=30M
read_buffer_size=20M
sort_buffer_size=20M
table_open_cache = 800
max_allowed_packet = 1M
skip-locking
key_buffer_size=400M

#*** MyISAM Specific options
myisam_max_sort_file_size=1M
myisam_sort_buffer_size=205M

#*** INNODB Specific options ***
innodb_additional_mem_pool_size=10M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=8499K
innodb_buffer_pool_size=1000M
innodb_log_file_size=170M
innodb_thread_concurrency=8
innodb_open_files=300
innodb_support_xa=false
innodb_additional_mem_pool_size=20M
innodb_log_buffer_size=14M

-Njoy

3 comments to MySQL server Optimization

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>