标签归档：mysql

xfs，数据库mysql优化

发表评论

准备尝试一下

xfs和Ext4最近学习文件系统，先要搞明白如何把分区格式化成相应的格式。

如果是xfs，需要安装 XFSprogs

如果是Ext4，需要安装e4fsprogs

对于ubuntu，比较好办，默认就已经搞定。但是Centos，就需要安装，xfs官方的源里都是没有的。

root@cloud:~# mkfs.xfs -f /dev/sdb1
meta-data=/dev/sdb1 isize=256 agcount=4, agsize=122009214 blks
= sectsz=512 attr=2, projid32bit=0
data = bsize=4096 blocks=488036855, imaxpct=5
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0
log =internal log bsize=4096 blocks=238299, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0

# mkfs.ext /dev/sdb1
mkfs.ext2 mkfs.ext3 mkfs.ext4 mkfs.ext4dev
root@cloud:~# mkfs.ext4 /dev/sdb1
mke2fs 1.41.14 (22-Dec-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
122011648 inodes, 488036855 blocks
24401842 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
14894 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968,
102400000, 214990848

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 35 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.

http://hi.baidu.com/chenshake/blog/item/b2b0eb246407412e8644f93c.html

mysql优化工具，profiler，服务器维护时会经常用到

发表评论

Query Profiler可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。该工具只有在MYSQL 5.0.37以及以上版本中才有实现。
默认的情况下，MYSQL的该功能没有打开，需要自己手动启动。可以通过如下方法查看当前mysql服务器是否开启了该功能。

mysql> show variables like ‘%profiling%’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| profiling | OFF |
| profiling_history_size | 15 |
+————————+——-+
继续阅读 →

g++: yes: No such file or directory mysql编译出错

发表评论

configure 没问题

make时出错，最后有如下错误信息

g++: yes: No such file or directory

mysql官方说，configure时可以去掉–with-mysqld-libs，或者指定相关的路径什么的，我去掉后还是出错

后来去掉–with-named-curses-libs选项后，正常了

mysql的datadir中的文件用途说明，网上收集，仅供参考

发表评论

mysql的datadir文件分析说明：
1、ibdata1
这个文件超级大， 查了一下， 大概的作用如下
是储存的格式
INNODB类型数据状态下，
ibdata用来储存文件的数据
而库名的文件夹里面的那些表文件只是结构而已

由于mysql4.1默认试innodb，所以这个文件默认就存在了http://man.chinaunix.net/database/mysql/inonodb_zh/2.htm  这个链接试innodb的中文参考， innodb的东西可以在my.ini中设置

2、ib_logfileX
The ib_logfileX files are log files used by the innodb storage engine
mainly to rollback any uncommitted transactions so that the database
is in a consistent state after it recovers from a crash
大概的意思是ib_logfilex文件是innodb存储引擎使用的日志文件，用来回滚一些未提交的sql，

3、localhost-relay-bin.005759和localhost-relay-bin.index
默认情况，中继日志使用host_name-relay-bin.nnnnnn形 式的文件名，其中host_name是从服务器主机名，nnnnnn是 序列号。用连续序列号来创建连续中继日志文件，从000001开始。从服务器跟踪索引文件中目前正 使用的中继日志。 默认中继日志索引文件名为host_name-relay-bin.index。 默认情况，在从服务器的数据目录中创建这些文件。可以用–relay-log和–relay-log-index服 务器选项覆盖 默认文件名。参见6.8节，“复制启动选项”。

中继日志与二进制日志的格式相同，并且可以用mysqlbinlog读取。SQL线 程执行完中继日志中的所有事件并且不再需要之后，立即自动删除它。没有直接的删除中继日志的机制，因为SQL线程可以负责完 成。然而，FLUSH LOGS可以循环中继日志，当SQL线程删除日志时会有影响。

4、master.info和relay-log.info
从属复制服务器在数据目录中另外创建两个小文件。这些状态文件默认名为主master.info和relay-log.info。 它们包含SHOW SLAVE STATUS语句的输出所显示的信息(关于该语句的描述参见13.6.2节，“用 于控制从服务器的SQL语句”)。状态文件保存在硬盘上，从服务器关闭时不会丢失。下次从服务器启动时，读取这些文件 以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。

举例如下：
[root@localhost db]# cat master.info
14
localhost-bin.000007
132857066
192.168.0.1
dbuser
pw
3306
60
0

[root@localhost db]# cat relay-log.info
/home/db/localhost-relay-bin.005944
239
localhost-bin.000007
132857066
7066

一个老外的my.cnf的配置，很有价值参考，呵呵

发表评论

HOWTO: configure MySQL’s my.cnf file
UPDATE: I recently used this MySQL tuner script, I basically went with what it told me, but I’m using a higher query_cache_size than it recommends, basically because I don’t see anything online saying it will hurt things.  So I’m now using the following values on my server:

[mysqld]
user=mysql
bind-address=127.0.0.1
datadir=/var/lib/mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysql/mysql.sock
port=3306
tmpdir=/tmp
language=/usr/share/mysql/english
skip-external-locking
query_cache_limit=64M
query_cache_size=32M
query_cache_type=1
max_connections=15
max_user_connections=300
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_stack=128K
thread_cache_size=128
myisam-recover=BACKUP
key_buffer=64M
join_buffer=1M
max_allowed_packet=32M
table_cache=512M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
max_connect_errors=10
thread_concurrency=4
myisam_sort_buffer_size=32M
skip-locking
skip-bdb
expire_logs_days=10
max_binlog_size=100M
server-id=1
[mysql.server]
user=mysql
basedir=/usr
[safe_mysqld]
bind-address=127.0.0.1
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192
SAFE_MYSQLD_OPTIONS=”–defaults-file=/etc/my.cnf –log-slow-queries=/var/log/slow-queries.log”
[mysql]
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeout
max_heap_table_size = 64 M
tmp_table_size = 64 M
!includedir /etc/mysql/conf.d/I’ve gone back and forth over the years configuring MySQL for optimal performance, and while I know I’m not there, I now have a new baseline to build from. From a post called Standard MYSQL my.cnf configuration, you can see all the base information, but also things like:

 key_buffer=256M # 64M for 1GB, 128M for 2GB, 256 for 4GBWhich defines the value (256M) but then spells out ideal base values for you to start with if you have more RAM on your system. This is very helpful, I’m tried to go a step further by combining it with Debian’s default my.cnf that comes on 5.0 (lenny) for MySQL 5. As I’m always open for suggestions for improvements, please comment if you have a different view on these choices, thanks. Here it is:

[client]
socket=/var/run/mysqld/mysqld.sock
port=3306

[mysqld_safe]
socket=/var/run/mysqld/mysqld.sock
nice=0

[mysqld]
user=mysql
bind-address=127.0.0.1
datadir=/var/lib/mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysql/mysql.sock
port=3306
tmpdir=/tmp
language=/usr/share/mysql/english
skip-external-locking
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=3000
max_user_connections=600
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_stack=128K
thread_cache_size=128
myisam-recover=BACKUP
#key_buffer – 64M for 1GB, 128M for 2GB, 256 for 4GB
key_buffer=64M
#join_buffer_size – 1M for 1GB, 2M for 2GB, 4M for 4GB
join_buffer=1M
max_allowed_packet=32M
table_cache=1024
#sort_buffer_size – 1M for 1GB, 2M for 2GB, 4M for 4GB
sort_buffer_size=1M
#read_buffer_size – 1M for 1GB, 2M for 2GB, 4M for 4GB
read_buffer_size=1M
#read_rnd_buffer_size – 768K for 1GB, 1536K for 2GB, 3072K for 4GB
read_rnd_buffer_size=768K
max_connect_errors=10
thread_concurrency=4
#myisam_sort_buffer_size – 32M for 1GB, 64M for 2GB, 128 for 4GB
myisam_sort_buffer_size=32M
skip-locking
skip-bdb
expire_logs_days=10
max_binlog_size=100M
server-id=1

[mysql.server]
user=mysql
basedir=/usr

[safe_mysqld]
bind-address=127.0.0.1
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192
SAFE_MYSQLD_OPTIONS=”–defaults-file=/etc/my.cnf –log-slow-queries=/var/log/slow-queries.log”

#[mysqldump]
#quick
#quote-names
#max_allowed_packet=16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
#key_buffer – 64M for 1GB, 128M for 2GB, 256M for 4GB
key_buffer=64M
#sort_buffer – 64M for 1GB, 128M for 2GB, 256M for 4GB
sort_buffer=64M
#read_buffer – 16M for 1GB, 32M for 2GB, 64M for 4GB
read_buffer=16M
#write_buffer – 16M for 1GB, 32M for 2GB, 64M for 4GB
write_buffer=16M

[myisamchk]
#key_buffer – 64M for 1GB, 128M for 2GB, 256M for 4GB
key_buffer=64M
#sort_buffer – 64M for 1GB, 128M for 2GB, 256M for 4GB
sort_buffer=64M
#read_buffer – 16M for 1GB, 32M for 2GB, 64M for 4GB
read_buffer=16M
#write_buffer – 16M for 1GB, 32M for 2GB, 64M for 4GB
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

!includedir /etc/mysql/conf.d/

mysql5.1.6后的计划任务功能

发表评论
http://dev.mysql.com/tech-resources/articles/event-feature.html

给个示例：

SQL code 
如果你是5.1后的版本的话，可以用event功能，示例如下：

检查event功能： 
mysql> show variables like '%sche%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)

启动event功能：
mysql> set global event_scheduler =1;
Query OK, 0 rows affected (0.00 sec)

确定起用event功能后，执行下面的建立event：(每24小时执行一次，任务的操作主体是update那部分）
delimiter //
create EVENT my_stat_event
    ON SCHEDULE
    EVERY 24 HOUR
    STARTS concat(date_format(now(),'%Y-%m-%d'),' 23:00:00')
    on completion preserve ENABLE
    do
    begin
     update order_total a inner join (select company_id, sum(goods_number) as total_count from order_goods group by company_id) b on a.company_id=b.company_id
       set a.total=b.total_count;
    end;
    //

执行上面的建立作业后，查看作业的信息：
mysql> select * from information_schema.events where event_name='my_stat_event';