2个问题1 mysq5.1 的大表是否支持分区 的加减 （在线），
我的某个表高达 50万， 如果换分区，是否所有记录需要重新 在插入一遍？？？
2 这个max_connections是否过小， 如果要调整 该是多少最好 （内存4g，在线java应用系统， 目前物理内存用完 ，swap用一部分，已经用了2年多 ，系统正常）
当前my.cnf配置如下 ，
table_cache = 1024
key_buffer = 256M
record_buffer = 16M
wait_timeout=800
connect_timeout=25
interactive_timeout=800
max_connections=50
sort_buffer = 128M
innodb_buffer_pool_size=3000MB
innodb_additional_mem_pool_size=20M
innodb_log_file_size = 50M
innodb_log_buffer_size=8M
innodb_thread_concurrency=50
innodb_flush_method=O_DSYNC
innodb_status_file = 1
innodb_flush_log_at_trx_commit = 0
innodb_autoextend_increment = 25M
skip-external-locking
innodb_table_locks=0max_allowed_packet=32M
tmp_table_size=96M
thread_concurrency=10os: centod5.3 （我记得有个公式可以综合计算得到max_connections的）
我的某个表高达 50万， 如果换分区，是否所有记录需要重新 在插入一遍？？？
2 这个max_connections是否过小， 如果要调整 该是多少最好 （内存4g，在线java应用系统， 目前物理内存用完 ，swap用一部分，已经用了2年多 ，系统正常）
当前my.cnf配置如下 ，
table_cache = 1024
key_buffer = 256M
record_buffer = 16M
wait_timeout=800
connect_timeout=25
interactive_timeout=800
max_connections=50
sort_buffer = 128M
innodb_buffer_pool_size=3000MB
innodb_additional_mem_pool_size=20M
innodb_log_file_size = 50M
innodb_log_buffer_size=8M
innodb_thread_concurrency=50
innodb_flush_method=O_DSYNC
innodb_status_file = 1
innodb_flush_log_at_trx_commit = 0
innodb_autoextend_increment = 25M
skip-external-locking
innodb_table_locks=0max_allowed_packet=32M
tmp_table_size=96M
thread_concurrency=10os: centod5.3 （我记得有个公式可以综合计算得到max_connections的）
query_cache_type = 0
query_cache_size = 0
16+128=144*50
《8g参考来自 http://www.chinaunix.net/jh/17/489282.html
http://horizonhyg.javaeye.com/blog/288273
查看调整swap分区大小
关键字: linux
查看swap分区大小：
命令代码 1. cat /proc/swaps cat /proc/swaps
显示代码 1. Filename Type Size Used Priority
2. /dev/mapper/VolGroup00-LogVol01 partition 524280 0 -1
3. /tmp/swapfree file 262136 0 -2 Filename Type Size Used Priority
/dev/mapper/VolGroup00-LogVol01 partition 524280 0 -1
/tmp/swapfree file 262136 0 -2调整swap大小：
命令代码 1. dd if=/dev/zero of=swapfree bs=32k count=8192 (256MB)
2. mkswap swapfree
3. swapon /tmp/swapfree
4. dd if=/dev/zero of=swapfree bs=32k count=8192 (256MB)
mkswap swapfree
swapon /tmp/swapfree
停止：
命令代码 1. swapoff /tmp/swapfree swapoff /tmp/swapfree
启动时加载:
在/etc/fstab文件中，加入下行：
命令代码 1. /tmp/swapfree swap swap defaults 0 0 /tmp/swapfree swap swap defaults 0 0检查swap
命令代码 1. #swapon -s #swapon -s
Warning: On GNU/Linux x86, you must be careful not to set memory usage too high. glibc will allow the process heap to grow over thread stacks, which will crash your server. It is a risk if the value of the following expression is close to or exceeds 2GB:innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MBEach thread will use a stack (often 2MB, but only 256KB in MySQL AB binaries) and in the worst case also uses sort_buffer_size + read_buffer_size additional memory.
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1),
UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;In each case, the proposed table would have at least one unique key that does not include all columns used in the partitioning expression.Each of the following statements is valid, and represents one way in which the corresponding invalid table creation statement could be made to work:CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;This example shows the error produced in such cases:mysql> CREATE TABLE t3 (
-> col1 INT NOT NULL,
-> col2 DATE NOT NULL,
-> col3 INT NOT NULL,
-> col4 INT NOT NULL,
-> UNIQUE KEY (col1, col2),
-> UNIQUE KEY (col3)
-> )
-> PARTITION BY HASH(col1 + col3)
-> PARTITIONS 4;
ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning functionThe CREATE TABLE statement fails because both col1 and col3 are included in the proposed partitioning key, but neither of these columns is part of both of unique keys on the table. This shows one possible fix for the invalid table definition:mysql> CREATE TABLE t3 (
-> col1 INT NOT NULL,
-> col2 DATE NOT NULL,
-> col3 INT NOT NULL,
-> col4 INT NOT NULL,
-> UNIQUE KEY (col1, col2, col3),
-> UNIQUE KEY (col3)
-> )
-> PARTITION BY HASH(col3)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)In this case, the proposed partitioning key col3 is part of both unique keys, and the table creation statement succeeds.The following table cannot be partitioned at all, because there is no way to include in a partitioning key any columns that belong to both unique keys:CREATE TABLE t4 (
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNQIUE KEY (col1, col3),
UNQIUE KEY (col2, col4)
);Since every primary key is by definition a unique key, this restriction also includes the table's primary key, if it has one. For example, the next two statements are invalid:CREATE TABLE t5 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;CREATE TABLE t6 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col3),
UNIQUE KEY(col2)
)
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;In both cases, the primary key does not include all columns referenced in the partitioning expression. However, both of the next two statements are valid:CREATE TABLE t7 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;CREATE TABLE t8 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2, col4),
UNIQUE KEY(col2, col1)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;If a table has no unique keys—this includes having no primary key—then this restriction does not apply, and you may use any column or columns in the partitioning expression as long as the column type is compatible with the partitioning type.For the same reason, you cannot later add a unique key to a partitioned table unless the key includes all columns used by the table's partitioning expression. Consider given the partitioned table defined as shown here:mysql> CREATE TABLE t_no_pk (c1 INT, c2 INT)
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (10),
-> PARTITION p1 VALUES LESS THAN (20),
-> PARTITION p2 VALUES LESS THAN (30),
-> PARTITION p3 VALUES LESS THAN (40)
-> );
Query OK, 0 rows affected (0.12 sec)It is possible to add a primary key to t_no_pk using either of these ALTER TABLE statements:# possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0# use another possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0However, the next statement fails, because c1 is part of the partitioning key, but is not part of the proposed primary key:# fails with error 1503
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning functionSince t_no_pk has only c1 in its partitioning expression, attempting to adding a unique key on c2 alone fails. However, you can add a unique key that uses both c1 and c2.These rules also apply to existing nonpartitioned tables that you wish to partition using ALTER TABLE ... PARTITION BY. Consider a table np_pk defined as shown here:mysql> CREATE TABLE np_pk (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(50),
-> added DATE,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.08 sec)The following ALTER TABLE statements fails with an error, because the added column is not part of any unique key in the table:mysql> ALTER TABLE np_pk
-> PARTITION BY HASH( TO_DAYS(added) )
-> PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning functionHowever, this statement using the id column for the partitioning column is valid, as shown here:mysql> ALTER TABLE np_pk
-> PARTITION BY HASH(id)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0In the case of np_pk, the only column that may be used as part of a partitioning expression is id; if you wish to partition this table using any other column or columns in the partitioning expression, you must first modify the table, either by adding the desired column or columns to the primary key, or by dropping the primary key altogether.We are working to remove this limitation in a future MySQL release series.
通过分区（Partition）提升MySQL性能
http://database.51cto.com 2009-05-11 14:46 佚名 haohtml 我要评论(0) * 摘要：数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对其相当熟悉。本文将为大家介绍如何通过分区（Partition）提升MySQL性能。
* 标签：MySQL 分区 提升性能
*
限时报名参加“甲骨文全球大会·2010·北京”及“JavaOne和甲骨文开发者大会2010”什么是数据库分区？数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。分区主要有两种形式：//这里一定要注意行和列的概念（row是行，column是列）1. 水平分区（Horizontal Partitioning）这种形式分区是对表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。（朋奕注：这里具体使用的分区方式我们后面再说，可以先说一点，一定要通过某个属性列来分割，譬如这里使用的列就是年份）2. 垂直分区（Vertical Partitioning） 这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列 被划分到特定的分区，每个分区都包含了其中的列所对应的行。举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。在数据库供应商开始在他们的数据库引擎中建立分区（主要是水平分区）时，DBA和建模者必须设计好表的物理分区结构，不要保存冗余的数据（不同表中同时都包含父表中的数据）或相互联结成一个逻辑父对象（通常是视图）。这种做法会使水平分区的大部分功能失效，有时候也会对垂直分区产生影响。在MySQL 5.1中进行分区MySQL5.1中最激动人心的新特性应该就是对水平分区的支持了。这对MySQL的使用者来说确实是个好消息，而且她已经支持分区大部分模式：Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980’s）的数据，90年代（1990’s）的数据以及任何在2000年（包括2000年）后的数据。Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。Composite（复合模式） - 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。分区带来的好处太多太多了，有多少？俺也不知道，自己猜去吧，要是觉得没有多少就别用，反正俺也不求你用。不过在这里俺强调两点好处：性能的提升（Increased performance） - 在扫描操作中，如果MySQL的优化器知道哪个分区中才包含特定查询中需要的数据，它就能直接去扫描那些分区的数据，而不用浪费很多时间扫描不需要的地方了。需要举个例子？好啊，百万行的表划分为10个分区，每个分区就包含十万行数据，那么查询分区需要的时间仅仅是全表扫描的十分之一了，很明显的对比。同时对十万行的表建立索引的速度也会比百万行的快得多得多。如果你能把这些分区建立在不同的磁盘上，这时候的I/O读写速度就“不堪设想”（没用错词，真的太快了，理论上100倍的速度提升啊，这是多么快的响应速度啊，所以有点不堪设想了）了。