To Backup only MYSQL database schema

CREATE DATABASE nplay_temp;

mysqldump --user xxxx --password=xxxx --databases xxxx --no-data > /tmp/xxxx_20101027.sql

Few site for Reference for MySQL DBA

http://hackmysql.com/

http://www.mysqlperformanceblog.com/

of course

http://dev.mysql.com/

Not exactly for this one, but you may refer the idea

http://dbaspot.com/

MYSQL - To Delete records from tableA that matching records from tableB

DELETE FROM t1 USING nplay_offline.sms_out_message AS t1 INNER JOIN nplay_delete.sms_out_message AS t2 WHERE t1.id=t2.id;

VI - to search and replace string in the file

:%s/sms_out_message_20101013/sms_out_message/g

UPDATE on INNODB status - INNODB_TABLE_LOCKS=1

Default value of INNODB_TABLE_LOCKS=ON, which means that Lock tables causes innodb to lock a table internally is autocommit=0;

MySQL InnoDB deadlock issue - lock mode AUTO-INC

AUTO-INC works as table level lock, which means other threads can't perform an insert in this table.

Hence, it means the insert statement would cause the threads to keep the lock to the end of the current insert SQL statements.

Suggested fix: you would have your own mechanism to generate the unique key to replace the "auto-increment" ID/fields. So that your insert statement may execute concurrently

Shell Scripts to run MySQL Query

 DB_NAME=SomeDB USER_NAME=someuser IP_ADDR=localhost PASSWORD=somepassword  somevar=`echo "select * from <table_name> where <condition>" | mysql -h $IP_ADDR -u$USER_NAME -p$PASSWD $DB_NAME `  echo $somevar

IT person 的生涯

如果你是一个靠近30岁的programmer了，你有没有想过，其实这条路对你来说并不是那么适合的？
每天不断的抱怨，不断的开始讨厌一些在工作上给你难堪的人？
然后你会发现到，别人把你当傻瓜来使，当踏脚板来用，在人前把你批得一无是处 ...
这就是一个programmer 生涯即将结束的征兆？
还是，这个纯粹是职场上的一种正常的现象？
其实有少许灰心...
我发现自己的梦想渐渐离我而去，我不够努力？
不够专心？还是根本没机会发挥？

84 tips for mysql optimization

MySQL is a widely used and fast SQL database server. It is a client/server implementation that consists of a server daemon (mysqld) and many different client programs/libraries.

1. Kaj (Most Excellent Obvious Facilitator) Index stuff.
2. Ronald Don't Index Everything
3. Use benchmarking
4. Minimize traffic by fetching only what you need.
   1. Paging/chunked data retrieval to limit
   2. Don't use SELECT *
   3. Be wary of lots of small quick queries if a longer query can be more efficient
5. Use EXPLAIN to profile the query execution plan
6. Use Slow Query Log (always have it on!)
7. Don't use DISTINCT when you have or could use GROUP BY
8. Use proper data partitions
   1. For Cluster. Start thinking about Cluster *before* you need them
9. Insert performance
   1. Batch INSERT and REPLACE
   2. Use LOAD DATA instead of INSERT
10. LIMIT m,n may not be as fast as it sounds
11. Don't use ORDER BY RAND() if you have > ~2K records
12. Use SQL_NO_CACHE when you are SELECTing frequently updated data or large sets of data
13. avoid wildcards at the start of LIKE queries
14. avoid correlated subqueries and in select and where clause (try to avoid in)
15. config params –
16. no calculated comparisons — isolate indexed columns
17. innodb_flush_commit=0 can help slave lag
18. ORDER BY and LIMIT work best with equalities and covered indexes
19. isolate workloads don't let administrative work interfere with customer performance. (ie backups)
20. use optimistic locking, not pessimistic locking. try to use shared lock, not exclusive lock. share mode vs. FOR UPDATE
21. use row-level instead of table-level locking for OLTP workloads
22. Know your storage engines and what performs best for your needs, know that different ones exist.
    1. use MERGE tables ARCHIVE tables for logs
23. Optimize for data types, use consistent data types. Use PROCEDURE ANALYSE() to help determine if you need less
24. separate text/blobs from metadata, don't put text/blobs in results if you don't need them
25. if you can, compress text/blobs
26. compress static data
27. don't back up static data as often
28. derived tables (subqueries in the FROM clause) can be useful for retrieving BLOBs w/out sorting them. (self-join can speed up a query if 1st part finds the IDs and use it to fetch the rest)
29. enable and increase the query and buffer caches if appropriate
30. ALTER TABLE…ORDER BY can take chronological data and re-order it by a different field
31. InnoDB ALWAYS keeps the primary key as part of each index, so do not make the primary key very large, be careful of redundant columns in an index, and this can make the query faster
32. Do not duplicate indexes
33. Utilize different storage engines on master/slave ie, if you need fulltext indexing on a table.
34. BLACKHOLE engine and replication is much faster than FEDERATED tables for things like logs.
35. Design sane query schemas. don't be afraid of table joins, often they are faster than denormalization
36. Don't use boolean flags
37. Use a clever key and ORDER BY instead of MAX
38. Keep the database host as clean as possible. Do you really need a windowing system on that server?
39. Utilize the strengths of the OS
40. Hire a MySQL ™ Certified DBA
41. Know that there are many consulting companies out there that can help, as well as MySQL's Professional Services.
42. Config variables & tips:
    1. use one of the supplied config files
    2. key_buffer, unix cache (leave some RAM free), per-connection variables, innodb memory variables
    3. be aware of global vs. per-connection variables
    4. check SHOW STATUS and SHOW VARIABLES (GLOBAL|SESSION in 5.0 and up)
    5. be aware of swapping esp. with Linux, "swappiness" (bypass OS filecache for innodb data files, innodb_flush_method=O_DIRECT if possible (this is also OS specific))
    6. defragment tables, rebuild indexes, do table maintenance
    7. If you use innodb_flush_txn_commit=1, use a battery-backed hardware cache write controller
    8. more RAM is good so faster disk speed
    9. use 64-bit architectures
43. Know when to split a complex query and join smaller ones
44. Debugging sucks, testing rocks!
45. Delete small amounts at a time if you can
46. Archive old data — don't be a pack-rat! 2 common engines for this are ARCHIVE tables and MERGE tables
47. use INET_ATON and INET_NTOA for IP addresses, not char or varchar
48. make it a habit to REVERSE() email addresses, so you can easily search domains
49. –skip-name-resolve
50. increase myisam_sort_buffer_size to optimize large inserts (this is a per-connection variable)
51. look up memory tuning parameter for on-insert caching
52. increase temp table size in a data warehousing environment (default is 32Mb) so it doesn't write to disk (also constrained by max_heap_table_size, default 16Mb)
53. Normalize first, and denormalize where appropriate.
54. Databases are not spreadsheets, even though Access really really looks like one. Then again, Access isn't a real database
55. In 5.1 BOOL/BIT NOT NULL type is 1 bit, in previous versions it's 1 byte.
56. A NULL data type can take more room to store than NOT NULL
57. Choose appropriate character sets & collations — UTF16 will store each character in 2 bytes, whether it needs it or not, latin1 is faster than UTF8.
58. make similar queries consistent so cache is used
59. Have good SQL query standards
60. Don't use deprecated features
61. Use Triggers wisely
62. Run in SQL_MODE=STRICT to help identify warnings
63. Turning OR on multiple index fields (<5.0) into UNION may speed things up (with LIMIT), after 5.0 the index_merge should pick stuff up.
64. /tmp dir on battery-backed write cache
65. consider battery-backed RAM for innodb logfiles
66. use min_rows and max_rows to specify approximate data size so space can be pre-allocated and reference points can be calculated.
67. as your data grows, indexing may change (cardinality and selectivity change). Structuring may want to change. Make your schema as modular as your code. Make your code able to scale. Plan and embrace change, and get developers to do the same.
68. pare down cron scripts
69. create a test environment
70. try out a few schemas and storage engines in your test environment before picking one.
71. Use HASH indexing for indexing across columns with similar data prefixes
72. Use myisam_pack_keys for int data
73. Don't use COUNT * on Innodb tables for every search, do it a few times and/or summary tables, or if you need it for the total # of rows, use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS()
74. use –safe-updates for client
75. Redundant data is redundant
76. Use INSERT … ON DUPLICATE KEY update (INSERT IGNORE) to avoid having to SELECT
77. use groupwise maximum instead of subqueries
78. be able to change your schema without ruining functionality of your code
79. source control schema and config files
80. for LVM innodb backups, restore to a different instance of MySQL so Innodb can roll forward
81. use multi_query if appropriate to reduce round-trips
82. partition appropriately
83. partition your database when you have real data
84. segregate tables/databases that benefit from different configuration variables

INSERT INTO ... SELECT Performance with Innodb tables

Author:	peter
url:	http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/

Everyone using Innodb tables probably got use to the fact Innodb tables perform non locking reads, meaning unless you use some modifiers such as LOCK IN SHARE MODE or FOR UPDATE, SELECT statements will not lock any rows while running.

This is generally correct, however there a notable exception - INSERT INTO table1 SELECT * FROM table2. This statement will perform locking read (shared locks) for table2 table. It also applies to similar tables with where clause and joins. It is important for tables which is being read to be Innodb - even if writes are done in MyISAM table.

So why was this done, being pretty bad for MySQL Performance and concurrency ? The reason is - replication. In MySQL before 5.1 replication is statement based which means statements replied on the master should cause the same effect as on the slave. If Innodb would not locking rows in source table other transaction could modify the row and commit before transaction which is running INSERT .. SELECT statement. This would make this transaction to be applied on the slave before INSERT... SELECT statement and possibly result in different data than on master. Locking rows in the source table while reading them protects from this effect as other transaction modifies rows before INSERT ... SELECT had chance to access it it will also be modified in the same order on the slave. If transaction tries to modify the row after it was accessed and so locked by INSERT ... SELECT, transaction will have to wait until statement is completed to make sure it will be executed on the slave in proper order. Gets pretty complicated ? Well all you need to know it had to be done fore replication to work right in MySQL before 5.1.

In MySQL 5.1 this as well as few other problems should be solved by row based replication. I'm however yet to give it real stress tests to see how well it performs :)

One more thing to keep into account - INSERT ... SELECT actually performs read in locking mode and so partially bypasses versioning and retrieves latest committed row. So even if you're operation in REPEATABLE-READ mode, this operation will be performed in READ-COMMITTED mode, potentially giving different result compared to what pure SELECT would give. This by the way applies to SELECT .. LOCK IN SHARE MODE and SELECT ... FOR UPDATE as well.

One my ask what is if I'm not using replication and have my binary log disabled ? If replication is not used you can enable innodb_locks_unsafe_for_binlog option, which will relax locks which Innodb sets on statement execution, which generally gives better concurrency. However as the name says it makes locks unsafe fore replication and point in time recovery, so use innodb_locks_unsafe_for_binlog option with caution.

Note disabling binary logs is not enough to trigger relaxed locks. You have to set innodb_locks_unsafe_for_binlog=1 as well. This is done so enabling binary log does not cause unexpected changes in locking behavior and performance problems. You also can use this option with replication sometimes, if you really know what you're doing. I would not recommend it unless it is really needed as you might not know which other locks will be relaxed in future versions and how it would affect your replication.

So what are safe workarounds if you're using replication ?

The most general one is to use: PLAIN TEXT SQL:

1. SELECT * FROM tbl1 INFO OUTFILE '/tmp/tbl1.txt';
2. LOAD DATA INFILE '/tmp/tbl1.txt' INTO TABLE tbl2;

instead of: PLAIN TEXT SQL:

1. INSERT INTO tbl2 SELECT * FROM tbl1;

INSERT ... INTO OUTFILE does not have to set extra locks.

If you use this aproach make sure to delete file after it is loaded back (it has to be done outside of MySQL Server) as otherwise the script will fail second time.

If you need result to be even closer to one of INSERT ... SELECT you may execute this transaction in READ-COMMITTED isolation mode.

Other workarounds are less general purpose. For example if you're doing batch processing which is well indexed you might chop transactions and process rows by small bulks, which do not cause long enough locks to cause the problems.

To complete this article I should show how wait caused by this statement will look in SHOW INNODB STATUS:

 TRANSACTION 0 42304626, ACTIVE 14 sec, process no 29895, OS thread id 2894768 updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 1794760, query id 6994946 localhost root Updating update sample set j=0 where i=5 TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 33504 n bits 328 index `j` of table `test/sample` trx id 0 42304626 lock_mode X locks rec but not gap waiting Record lock, heap no 180 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 30; hex 306338386465646233353863643936633930363962373361353736383261; asc 0c88dedb358cd96c9069b73a57682a;...(truncated); 1: len 4; hex 00000005; asc ;;  TRANSACTION 0 42304624, ACTIVE 37 sec, process no 29895, OS thread id 4058032 fetching rows, thread declared inside InnoDB 3 mysql tables in use 1, locked 1 2539 lock struct(s), heap size 224576 MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select * from sample 

As you can see INSERT... SELECT has a lot of lock structs, which means it has locked a lot of rows. "fetching rows" of course means it is still going. In this case write is done to MyISAM table so we'll not see any write activity.

Other transaction which happes to be simple primary key update is waiting on sample table for this record to be unlocked.

Mysql Recover root password

Recover MySQL root Password

by VIVEK GITE · 148 COMMENTS

You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for password.

Step # 3: Connect to mysql server as the root user.

Step # 4: Setup new mysql root account password.

Step # 5: Exit and restart the MySQL server.

Here are commands you need to type for each step (login as the root user):

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

Facebook Fans Page extended permission - Graph API

To grant Facebook Extended Permission by the application to the Fans Page.

Steps:

1.  Login as administrator account which the Fans Page under. After that, go to the apps' "Apps Profile Page". when you are in that "Apps Profile Page", click at "Add to My Page". From the popup screen, you can select which page you would like to add the application to.

2.  Modify the link below:  

     https://graph.facebook.com/oauth/authorize?redirect_uri=http://www.facebook.com&client_id=4<Facebook APP ID>&scope=manage_pages

    * notes: you can only request per extended permission each time, else you will getting error page.

    manage_pages - to publish stream to your fans page wall

    insights - this is rather useful, if you are trying to get "access_token" of the page, hence to post a stream to the fans page

3. To get the access_token of the fans page, please run the link as below:

    https://graph.facebook.com/me/accounts?access_token=<access token>

    -  access_token : 

       this access_token can be the temporary access token you can get from graph.facebook.com, or it's the access_token you gain after you grant extended permission to the                 

       facebook application.

or The lastest way


https://developers.facebook.com/tools/explorer//?method=GET&path=

click on "Get Access Token"

To post stream to Facebook Fans Page:

Example PHP code:

$facebook = new Facebook($api_key, $secret);

$attachment = array (

'access_token' => $defaultPageAccessToken,

'message' => "some useful message",

'name' => $applicationName,

'caption' => $applicationDefaultCaption,

'link' => $applicationUrl,

'picture' => "http://www.google.com/google.gif",

'description' => $applicationDefaultDescription,

'actions' => null

);

try {

$result = $facebook->api('/'.$defaultPageId.'/feed/', 'post', $attachment);

} catch(Exception $e) {

echo '<pre>';

print_r($e);

echo '</pre>';

}

Best Regards,
jack

to check if a linux user able to ssh into Server even after the user created

check if the user have one "AllowUsers" line in /etc/ssh/sshd_config

after that restart the sshd service

`service sshd restart`

users are suppose able to login via ssh now.

MYSQL - tmp_table_size and max_heap_table_size

 Description: The default value of tmp_table_size (32M) is meaningless, as it is less than max_heap_table_size (16M), which then becomes the real maximum for an in-memory temporary table.  How to repeat: mysql> select @@global.tmp_table_size, @@global.max_heap_table_size \G *************************** 1. row ***************************      @@global.tmp_table_size: 33554432 @@global.max_heap_table_size: 16777216  Suggested fix: Decrease the default of tmp_table_size to 16M, or increase the default of max_heap_table_size to 32M.

Tunning System Variable - MYSQL

http://www.hashmysql.org/index.php?title=Tuning_System_Variables

Foreword

There are a number of variables available to tune within MySQL, all available for you to see via the SHOW VARIABLES; command. To aid in your diagnostics of how well these variables may be tuned, MySQL provides the SHOW STATUS; command.

Out of these variables, there are some that can help you on your way to the ultimate in "mauve" databases - those that detail the various memory cache's available to you. MySQL breaks up it's total memory structure in to smaller caches that service different functions, such as the Key Cache and Table Cache to hold frequently used index and table data in memory, for example.

Here we'll take a look at which variables to look for, how we can select them, how to interpret them, and ultimately, how to tune them. We will start with the thread cache and show the steps involved in tuning the server variables.

[edit]

The Thread Cache

Before users can even begin to start selecting data, they need to connect to the database. If your application makes a lot of seperate connections to the database over short periods of time (like most PHP based applications) this can cause a lot of overhead with allocating and deallocating the associated "stack" for each specific "Connection" or "Thread".

So wouldn't it make sense to hold a pool of already allocated threads in memory? Enter thread_cache_size - which sets the number of threads to hold open in memory to service new connections.

Using the SHOW STATUS command we can determine how many connections are being serviced by already cached threads, and how many are having to wait for a new thread stack to be allocated. The status variables in question are Threads_created andConnections. We can select very specific status variables by using the LIKE keyword, such as:

 SHOW STATUS LIKE '%thread%'; +------------------------+-------+ | Variable_name          | Value | +------------------------+-------+ | Delayed_insert_threads | 0     | | Slow_launch_threads    | 0     | | Threads_cached         | 30    | | Threads_connected      | 4     | | Threads_created        | 31    | | Threads_running        | 2     | +------------------------+-------+ 

To determine how the thread_cache_size variable should be set, we use what is known as a "Hit Ratio". Hit Ratios are typically stated as a percentage score, giving the percentage of "requests" that were serviced by in memory caches (also known as logical I/O) vs those that cause disk requests (physical I/O) or extra processing - such as in this case, thread allocation to memory (which causes extra CPU activity).

Threads_created details the number of threads that have been created since the MySQL server started, and Connections is the total number of client connections to the MySQL server since startup. To work out the thread cache hit ratio, we use this calculation:

 100 - ((Threads_created / Connections) * 100) 

Given the above value of 31 for Threads_created, and the value of 4567 that Connections showed, I am able to determine that my Thread Cache Hit Ratio is 99.32%.

The ideal situation is to get Threads_created as close as possible to thread_cache_size - no new connections having to wait for new thread allocation - staying as close to around a 99% hit ratio as you can. To see the value of thread_cache_size you can use:

 SHOW VARIABLES LIKE 'thread_cache_size'; +-------------------+-------+ | Variable_name     | Value | +-------------------+-------+ | thread_cache_size | 30    | +-------------------+-------+ 

Of course, these hit ratios can be skewed on a system that has recently been restarted, so should also be evaluated accordingly against Uptime.

[edit]

The MyISAM Key Cache

The MyISAM storage engine has one main area within the MySQL memory structure that helps to service requests to clients, the MyISAM Key Cache.

[edit]

InnoDB Specific Caches

To be written.

[edit]

The Query Cache

To be written.

[edit]

Sort Buffers

To be written.

Optimize MySQL: The Thread Cache

From: http://www.epigroove.com/posts/63/optimize_mysql_the_thread_cache

Optimize MySQL: The Thread Cache

With the recent release of the new Babble site and the resulting increase in mysql activity (MySQL was averaging around 300 queries per second), mysql and server loads rose to a level that was starting to get uncomfortable. So I decided to flip through the MySQL status variables to see if anything looked abnormal. That's when I discovered that my "thread cache hit rate" was abysmal.

The hit rate should be as close to 100% as possible. You can calculate your hit ratio by dividing the 'threads_created' status variable by the 'connections' status variable:

100 - ((Threads_created / Connections) * 100)

My hit rate was less than 1%. Almost every mysql connection was causing a new thread to be created, and a lot of threads were being created, thus creating a lot of unnecessary overhead.

The cause of the problem was that 'thread_cache_size' was set to 0. Thread_cache_size determines how many threads MySQL will hold open in memory to handle new connections. So in my case, MySQL wasn't holding any in cache so it had to create new threads all the time. Not cool.

To determine what you should set 'thread_cache_size' to, pay close attention to the 'threads_created' status variable. If it keeps going up it means your 'thread_cache_size' is set too low. Just keep bumping up 'thread_cache_size' until 'threads_created' no longer increments. My optimal thread_cache_size turned out to be 50.

As soon as I optimized the thread cache, MySQL's server load dropped over 50%!

This MySQL optimization technique worked wonders for me, so I'm sharing it in hopes that it will help you.

BTW- If you don't have it yet, go download the MySQL Administrator. It makes viewing and changing MySQL variables much easier.

Comments

READ this. Regarding INNOdb optimization

http://jeremy.zawodny.com/blog/archives/000173.html

MySQL, Linux, and Thread Caching

Some random bits scribbled by Jeremy Zawodny

Wow, it's been a busy week. I was totally swamped for several days dealing with the remember.yahoo.com MySQL servers and related stuff. And then I used a day or two to recover (sleep, shower, etc).

Anyway, I made some interesting discoveries along the way. The most surprising one had to do with thread caching on Linux when you have a busy MySQL server--busy in a particular way, mind you.

You see, we had a single master server which all the web servers could connect to (using PHP) whenever someone made a change. That includes creating a tile (there were several hundred thousand tiles created), approves a tile, marks one as "cool", and so on. All told, the master was quite busy.

Because there were between 20 and 45 front-end web servers during that time, and each could have had up to 70 apache processes that might have needed to connect, we faced a problem. That meant that the master needed to handle up to 3,150 connections in the worst case (that's 45 x 70). Most of the PHP code used mysql_pconnect() to hold persistent connections.

Rather than worry about how to do that, I made sure that the wait_timeout was set to a very low value: 15 seconds. That means MySQL would close any connection that was idle for more than 15 seconds. But I didn't realize the extent of the problem until I started getting reports from the web servers that the master was refusing connections. Why? Because I had set the maximum number of connections to a reasonable value in the master's my.cnf file:

set-variable = max_connections=180 set-variable = max_user_connections=140 

And at that time, the wait_timeout was set to 600 seconds (10 minutes). Clearly that was a problem. There were a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done.

What to do?

We could have stopped using mysql_pconnect(), but as you'll see, that wouldn't have solved the underlying problem.

I needed to adjust the settings. But I wasn't sure what values to use. And I really didn't want to keep stopping and starting the master. That would just suck. Then I remembered that we were running MySQL 4.0.4. I'd has a new feature that allows you to change most of the server settings on on the fly without restarting! Read abouthere, it in the on-line manual.

Excellent!

All I needed to do was execute a few variations on this command:

SET GLOBAL wait_timeout=60; 

(with different values in the place of "60") to try and strike a balance between letting new clients in and kicking out already connected users too quickly.

Ultimately, I settled on a timeout of 15 seconds.

But that had an interesting and unanticipated side-effect. It meant that the Linux server was having to create new threads (MySQL is a multi-threaded server) at a very high rate. That sucks up a measurable amount of CPU time.

How much CPU time? By the time I got around to looking at the output of SHOW STATUS and seeing this:

| Threads_cached           | 0          | | Threads_created          | 270194     | | Threads_connected        | 46         | | Threads_running          | 28         | 

Things were pretty bad. The machine had very little idle CPU time--probably 5-10% at the most. But it really wasn't doing that much work--maybe 40 queries per second. I was a bit puzzled. But that Threads_created number jumped out at me. It was high and increasing rapidly.

Luckily I remembered the thread_cache setting. So I decided to investigate (using the new syntax for examining server variables):

mysql> SELECT @@global.thread_cache_size; +---------------------+ | @@thread_cache_size | +---------------------+ |                   0 | +---------------------+ 1 row in set (0.00 sec) 

Uh oh. I never set the thread cache in my.cnf, so it has assumed the default. That's bad. It's like removing the pre-forking capabilities of Apache 1.3 and letting it get pounded on a busy web site. The "fork a new process for each new request" gets pretty expensive pretty quickly.

Ugh!

Luckily the thread cache is also tunable on the fly now. So all I had to do was this:

SET GLOBAL thread_cache_size=40; 

I took a guess and figured that by caching 40 threads, we'd be saving a lot of work. And boy was I right!

In the other window, where I was running vmstat 1 I noticed a dramatic change. The idle CPU on the machine immediately went from 5-10% to 35-40%

If only I had thought of that sooner!

So the moral of the story is this: 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. Your CPU will thank you.

I don't feel bad though. We were all going nuts to try and tune/optimize the code and servers while it was running and had very little sleep. Thread caching really wasn't the worst of our problems. But it became the worst after we had fixed all the bigger ones.

It was quite a learning experience.

[HowTo] Optimising MYSQL

FROM:: 

http://www.interworx.com/forums/showthread.php?t=377

First of all, how to find your variable, and the mysql usage ?

*VARIABLES

Quote:

from mysql : show variables;  or from command line :  mysqladmin variables

*PROCESS / STATUS

Quote:

from Mysql : show status;  or from command line  mysqladmin –i10 processlist extended-status

*SOME USEFUL COMMAND FOR YOU BOX USAGE

Quote:

>Top >ps –axfu  >vmstat 1

* OPTIMISING MYSQL

To obtain the stat of your mysql server since it has been loaded, run mysqladmin processlist extended-status as mentionned above.

1 - The two most important variables : Table_cache and Key_buffer_size

* If Opened_tables is big, then your table_cache variable is probably
too small.
table_cache 64
Open_tables 64
Opened_tables 544468

This is the first serious problem. "The table_cache is the number of open
tables for all threads. MySQL, being multi-threaded, may be running many
queries on the table at one time, and each of these will open a table."
Therefore, even though we only have a few tables, we will need many more
open_tables.

The Opened_tables value is high and shows the number of
cache misses. Getting the table_cache size correct is one of the two best
things you can do to improve performance.



* If Key_reads is big, then your key_buffer_size variable is probably
too small. The cache hit rate can be calculated with
Key_reads/Key_read_requests.
key_buffer_size 16M
Key_read_requests 2973620399
Key_reads 8490571
(cache hit rate = 0.0028)

"The key_buffer_size affects the size of the index buffers and the speed
of index handling, particularly reading." The MySQL manual (and other sources) say that
"Key_reads/Key_read_request ratio should normally be < 0.01." This is the
other most important thing to get correct. Here the value seems to be correct (< 0.01)

Also check key_write_requests and key_writes.
The key_writes/key_writes_request should normally be < 1 (near 0.5 seems to be fine)


Here is a very interesting web pointer : http://www.databasejournal.com/featu...0897_1402311_3



2 - Others important settings are : Wait_timeout, max_connexion, thread_cache

A little explanation : 
Generaly you have a lot of mysql process that are sleeping because wait_timeout are not set low. So I make sure that the wait_timeout is set to a very low value: 15 seconds (for me) . That means MySQL would close any connection that was idle for more than 15 seconds.

The problem is you also have to increment your max_connexion (mine is set to 300) to be sure there is not a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done.
The pbm is that the box has to create new threads (MySQL is a multi-threaded server) at a very high rate. That may sucks up a measurable amount of CPU time.

So the solution is to use the Thread_cache (from mysql doc) :
"How many threads we should keep in a cache for reuse. When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads_created you can see how efficient the current thread cache is for you."


* If Threads_created is big, you may want to increase the
thread_cache_size variable. The cache hit rate can be calculated with
Threads_created/Connections.
thread_cache_size 0
Threads_created 150022
Connections 150023

This is the second problem that should be fixed. A cache size of zero is the default for my-medium.cnf but the recommended size in my-large.cnf is 8. 

you may try this formula : table_cache = opened table / max_used_connection


3 - Finally, you may also have a look at : tmp_table_size and Handler_read_rnd / Handler_read_rnd_next 

* If Created_tmp_disk_tables is big, you may want to increase the
tmp_table_size variable to get the temporary tables memory-based instead
of disk based.

tmp_table_size 32M
Created_tmp_disk_tables 3227
Created_tmp_tables 159832
Created_tmp_files 4444

Created_tmp_disk_tables are the "number of implicit temporary tables on
disk created while executing statements" and Created_tmp_tables are
memory-based. Obviously it is bad if you have to go to disk instead of
memory. About 2% of temp tables go to disk, which doesn't seem too bad
but increasing the tmp_table_size probably couldn't hurt either.


* If Handler_read_rnd is big, then you probably have a lot of queries
that require MySQL to scan whole tables or you have joins that don't use
keys properly.
Handler_read_rnd 27712353
Handler_read_rnd_next 283536234

These values are high, that we could probably stand to improve
the indexes and queries.


I hope this will help some of you to more understand how it is possible to optimise MYSQL to fit your needs, hardaware box, or mysql current usage.

Maybe there is others tweaks to perform, but I know well only these ones. I did setup using these ones on differents mysql box, and generally it did help us to increase performance without have to change hardware (our boxes have 2GB ram)

two or three importants things like :

Used MySQL memory = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size) 

Notice the max_connexion and the multiplier.
connexion increase = memory usage increase too.

Notice key_buffer
for a given memory :
more you add mem to key buffer, less connexion is
less is key buffer, more connexion is

If you change one of these settings for a high value, you system may swap.
If you system swap, try lot decrease these values

Also, about table_cache :
Increasing the size of the table cache may really help you. 
But you must be careful not to make the value too large. All operating systems have a limit on the number "open file pointer" (sorry in french it is called pointer, maybe descriptors is the good translation) a single process may have.
If MySQL tries to open a lot of files, the OS may refuse it and MySQL will generate error message in the error log.