MySQL TEXT fields have a limit of 65,000 bytes. If you insert anything larger than that in to a normal TEXT field mySQL will silently truncate your data without telling you (meaning software checks are probably a good idea). MEDIUMTEXT will store 16 million characters and LONGTEXT can handle over 4 trillion, but this information does not appear to be readily available in the online mySQL manual (although it is hinted at in this table). Something to bare in mind when designing database applications.

MyISAM tables in MySQL have a maximum size of a row of 65,535 bytes, so all the data in a row must fit within that limit. However, the TEXT types are stored outside the table itself and only contribute 9 to 12 bytes towards that limit. (For more information about this refer to the MySQL Manual – Data Storage Requirements chapter).

ref::http://www.electrictoolbox.com/maximum-length-mysql-text-field-types/
TEXT data types are also able to store much more data than VARCHAR and CHAR text types so TEXT types are what you need to use when storing web page or similar content in a database.

The maximum amount of data that can be stored in each data type is as follows:

TINYTEXT 256 bytes
TEXT 65,535 bytes ~64kb
MEDIUMTEXT 16,777,215 bytes ~16MB
BIGTEXT 4,294,967,295 bytes ~4GB
In most circumstances the TEXT type is probably sufficient, but if you are coding a content management system it’s probably best to use the MEDIUMTEXT type for longer pages to ensure there are no issues with data size limits.

MySQL CONCAT function is used to concatenate two strings to form a single string. Try out following example:
mysql> SELECT CONCAT(‘FIRST ‘, ‘SECOND’);
+—————————-+
| CONCAT(‘FIRST ‘, ‘SECOND’) |
+—————————-+
| FIRST SECOND |
+—————————-+
1 row in set (0.00 sec)

ie.
mysql -u eoewallpapers -p123456123 wallpapers -e ‘set names ‘utf8′; select CONCAT(base_url,”400″,title) from photos where state > 10 ‘ > photos_400.txt

有很多插件可以做这个事情，例如use_db ,magic_multi_connections等等，但是也有更简单得方法，如下，2步就好了

1. 设置database的config
extradb_production:
adapter: mysql
host: localhost
username: root
password: blabla
database: extradb_prod
2. 在相关得 Model 利用 estabilish_connection 去做连接即可
class lala < ActiveRecord::Base
establish_connection :extradb_production
end

Rails启动后报错，ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/temp/mysql.sock’ (2)

1、先查看 /etc/rc.d/init.d/mysqld status 看看m y s q l 是否已经启动.
另外看看是不是权限问题.

2、确定你的mysql.sock是不是在那个位置，
mysql -u 你的mysql用户名 -p -S /var/lib/mysql/mysql.sock

3、试试：service mysqld start

4、如果是权限问题，则先改变权限 #chown -R mysql:mysql /var/lib/mysql

[root@localhost ~]# /etc/init.d/mysqld start
启动 MySQL： [ 确定 ]
[root@localhost ~]# mysql -uroot -p

前段时间写了一篇《实例优化mysql配置文件my.cnf》，这几天看到效果非常明显，别的不说，单mysql cache_hits就很不错（之前竟然没注意到这个参数）

# Increase query_cache_size from 0 to 128M
query_cache_size=128M
# Increase query cache limit from 1048576 to 2M
query_cache_limit=2M

看到效果很明显，如下是munin的监控图：

mysql数据库也像ORACLE数据库一样，可以动态的修改参数，可以修改会话级变量只对当前会话产生影响；也可以修改全局变量，对所有新连接的会话都产生影响。

修改会话级变量
用show variables 命令查看当前参数的值,like ‘pattern’用于模式匹配，查找指定的参数

mysql> show variables like ‘%sort_buffer_size%’;
+—————————+————+
| Variable_name | Value |
+—————————+————+
| sort_buffer_size | 6291448 |
+—————————+————+
1 rows in set (0.00 sec)

用set SESSION命令设置会话级变量的新值

mysql> set SESSION sort_buffer_size=7000000;
Query OK, 0 rows affected (0.00 sec)

–修改会话级变量对当前会话来说立刻生效
mysql> show variables like ‘%sort_buffer_size%’;
+—————————+————+
| Variable_name | Value |
+—————————+————+
| sort_buffer_size | 7000000 |
+—————————+————+
1 rows in set (0.00 sec)

mysql> exit
Bye
退出重新连接后，此参数恢复原值
[root@devdbc_stb root]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40 to server version: 5.0.37-log

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

mysql> show variables like ‘%sort_buffer_size%’;
+—————————+————+
| Variable_name | Value |
+—————————+————+
| sort_buffer_size | 6291448 |
+—————————+————+
1 rows in set (0.00 sec)

修改全局变量
[root@devdbc_stb root]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40 to server version: 5.0.37-log

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

mysql> show variables like ‘%sort_buffer_size%’;
+—————————+————+
| Variable_name | Value |
+—————————+————+
| sort_buffer_size | 6291448 |
+—————————+————+
1 rows in set (0.00 sec)

用set GLOBAL 命令设置全局变量

mysql> set GLOBAL sort_buffer_size = 7000000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like ‘%sort_buffer_size%’;
+—————————+————+
| Variable_name | Value |
+—————————+————+
| sort_buffer_size | 6291448 |
+—————————+————+
1 rows in set (0.00 sec)
当前此参数的值并不发生变化，先退出，然后重新连进去
mysql> exit
Bye
[root@devdbc_stb root]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41 to server version: 5.0.37-log

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

mysql> show variables like ‘%sort_buffer_size%’;
+—————————+————+
| Variable_name | Value |
+—————————+————+
| sort_buffer_size | 7000000 |
+—————————+————+
1 rows in set (0.00 sec)
新的参数值生效

一直以来，只要我看到explain出来的结果有“Using filesort ”，我就要想方设法的优化和改善，因为我以前以为这个的意思就是使用文件系统来排序（那该多慢呀！），其实不是这样的。
在mysqlperformanceblog上看到一篇文章《What does Using filesort mean in MySQL?》其中作者说自己每次面世的时候都会问“what does Using filesort mean in EXPLAIN,” 结果没有一个人回答的是对的，结果让其比较郁闷，就写了这个文章说明：

The usual answer is something like “rows are being placed into a temporary table which is too big to fit in memory, so it gets sorted on disk.” Unfortunately, this is not the same thing. First of all, this is Using temporary. Secondly, temporary tables may go to disk if they are too big, but EXPLAIN doesn’t show that. (If I interview you, I might ask you what “too big” means, or I might ask you the other reason temporary tables go to disk!)

通常的解释是：选择出来的记录太多了，需要排序时内存中放不下了，所以会存储在磁盘中，并通过操作磁盘的文件的方式来排序。但是呢，这是不对的。

The truth is, filesort is badly named. Anytime a sort can’t be performed from an index, it’s a filesort. It has nothing to do with files. Filesort should be called “sort.” It is quicksort at heart.

正解是，filesort这个名字有问题，容易给人误解，在mysql中，任何不能通过index进行的sort都称之为filesort，这里的filesort和文件没有任何关系，应该称之为“sort”而不是“filesort”，它的内部实现就是快速排序。

参考资料：
What does Using filesort mean in MySQL?

http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/

How MySQL executes ORDER BY

http://s.petrunia.net/blog/?p=24

发现VARIABLES里有个max_length_for_sort_data，其值是1024，感觉很小，如下

mysql> SHOW VARIABLES like “%sort%”;
+—————————+————+
| Variable_name | Value |
+—————————+————+
| max_length_for_sort_data | 1024 |
| max_sort_length | 1024 |
| myisam_max_sort_file_size | 2146435072 |
| myisam_sort_buffer_size | 67108864 |
| sort_buffer_size | 8388608 |
+—————————+————+
5 rows in set (0.00 sec)

不晓得是做啥用的，查了下资料：

http://forge.mysql.com/wiki/MySQL_Internals_Algorithms

Using the modified filesort algorithm, the tuples are longer than the pairs used in the original method, and fewer of them fit in the sort buffer (the size of which is given by sort_buffer_size). As a result, it is possible for the extra I/O to make the modified approach slower, not faster. To avoid a slowdown, the optimization is used only if the total size of the extra columns in the sort tuple does not exceed the value of the max_length_for_sort_data system variable. (A symptom of setting the value of this variable too high is that you should see high disk activity and low CPU activity.)

mysql的filesort有两个方法,MySQL 4.1之前是使用方法A, 之后版本会使用改进的算法B, 但使用方法B的前提是列长度的值小于max_length_for_sort_data, 但我们系统中的列的长度的值会大于1024. 因此也就是说在sort的时候, 是在使用方法A, 而方法A的性能比较差

这些天给我们的一个产品的服务器端加上了memcached，发现性能相当好，在newrelic上监控到每个请求差不多能稳定在100ms以下，开上10个ruby实例，每分钟处理几千个请求不是问题，但是耶发现munin的监控，MYSQL在过期缓冲的时候还是又慢查询，因为我有个表应该又100W的数据了。

晓得MYSQL默认的一些配置不合适，只是一直没来得及调整，今天查了下资料，讲相关的配置调整了下，调整后的如下，如果你是又这方面的经验，发现不合理的还请指出，谢谢。
机器配置：
CentOS release 5.3，CPU（Intel(R) Xeon(R) CPU L5420 @ 2.50GHz） RAM 3G，disk 200G

my.cnf配置

# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the “–help” option.

# The following options will be passed to all MySQL clients
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
skip-locking

# By default, the wait_timeout value is 28800. Unless you want MySQL to keep waiting for 28800 seconds (480 minutes or 8 hours),
# please decrease its value according to your application needs.
wait_timeout=60
# Increase connect_timeout from 5 to 10
connect_timeout=10
# Decrease interactive_timeout from 28800 to 100
interactive_timeout=120
# Increase join_buffer_size from 131072 to 1M
join_buffer_size=1M
# Increase query_cache_size from 0 to 128M
query_cache_size=32M
# Increase query cache limit from 1048576 to 2M
query_cache_limit=2M
# Increase max allowed packet size from 1M to 16M
max_allowed_packet=2M
# Increase table cache cache from 256 to 1024
table_cache=1024
# Increase sort buffer size from 1M
sort_buffer_size=2M
# Increase read buffer size from 1M
read_buffer_size=2M
# Increase read_rnd_buffer_size to 4M
read_rnd_buffer_size=4M

#Other settings
sort_buffer_size=8M
key_buffer = 256M
key_buffer_size=64M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 2

# Don’t listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the “enable-named-pipe” option) will render mysqld useless!
#
#skip-networking

# Disable Federated by default
skip-federated

old_passwords=1

#for slow queries
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 3

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid