Activities

October 2009
M T W T F S S
« Aug   Nov »
 1234
567891011
12131415161718
19202122232425
262728293031  

MySQL Server optimization tricks

These variables will help him to optimize or tuning mysql easily.

1. Lock and unlock mysql tables

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

b. Unlocking tables

UNLOCK TABLES;

2. Variables to be monitored
Threads_connected
Created_temp_disk_tables
Handler_read_first
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’;
set sql_log_off = 1;
turns it back on

5. monitor the mysql
show status;

6. Repairing 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-watching
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.

9.Howto 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)
)

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.

# show indexes from employee_records2;

Mysql optimization for Core2duo 4GB Window 2003 server


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>