flyinweb's blog - 数据库技术

mysql-mmm Installation Guide

shanyiwan@live.com() — Tue, 31 Jan 2012 08:19:48 GMT

A basic installation contains at least 2 database servers and 1 monitoring server. In this guide, I used 5 servers with Debian Lenny (5.0)

function	ip	hostname	server id
monitoring host	192.168.0.10	mon	-
master 1	192.168.0.11	db1	1
master 2	192.168.0.12	db2	2
slave 1	192.168.0.13	db3	3
slave 2	192.168.0.14	db4	4

I used the following virtual IPs. They will be distributed across the hosts by MMM.

ip	role	description
192.168.0.100	writer	Your application should connect to this IP for write queries.
192.168.0.101	reader	Your application should connect to one of these four IPs for read queries
192.168.0.102	reader
192.168.0.103	reader
192.168.0.104	reader

Basic configuration of master 1

First we install MySQL on all hosts:

aptitude install mysql-server

Then we edit the configuration file /etc/mysql/my.cnf and add the following lines - be sure to use different server ids for all hosts:

server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log 
log_bin_index       = /var/log/mysql/mysql-bin.log.index 
relay_log           = /var/log/mysql/mysql-relay-bin 
relay_log_index     = /var/log/mysql/mysql-relay-bin.index 
expire_logs_days    = 10 
max_binlog_size     = 100M 
log_slave_updates   = 1

Then remove the following entry:

bind-address = 127.0.0.1

Do not bind of any specific IP, use 0.0.0.0 instead:

bind-address = 0.0.0.0

Afterwards we need to restart MySQL for our changes to take effect:

/etc/init.d/mysql restart

Create users

Now we can create the required users. We'll need 3 different users:

function	description	privileges
monitor user	used by the mmm monitor to check the health of the MySQL servers	REPLICATION CLIENT
agent user	used by the mmm agent to change read-only mode, replication master, etc.	SUPER, REPLICATION CLIENT, PROCESS
relication user	used for replication	REPLICATION SLAVE

GRANT REPLICATION CLIENT                 ON *.* TO 'mmm_monitor'@'192.168.0.%' IDENTIFIED BY 'monitor_password';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.0.%'   IDENTIFIED BY 'agent_password';
GRANT REPLICATION SLAVE                  ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED BY 'replication_password';

Note: We could be more restrictive here regarding the hosts from which the users are allowed to connect: mmm_monitor is used from 192.168.0.10. mmm_agent and replication are used from 192.168.0.11 - 192.168.0.14.

Synchronisation of data between both databases

I'll assume that db1 contains the correct data. If you have an empty database, you still have to syncronize the accounts we have just created.

First make sure that no one is altering the data while we create a backup.

(db1) mysql> FLUSH TABLES WITH READ LOCK;

Then get the current position in the binary-log. We will need this values when we setup the replication on db2, db3 and db4.

(db1) mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+ 
| mysql-bin.000002 |      374 |              |                  | 
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec)

DON'T CLOSE this mysql-shell. If you close it, the database lock will be removed. Open a second console and type:

db1$ mysqldump -u root -p --all-databases > /tmp/database-backup.sql

Now we can remove the database-lock. Go to the first shell:

(db1) mysql> UNLOCK TABLES;

Copy the database backup to db2, db3 and db4.

db1$ scp /tmp/database-backup.sql <user>@192.168.0.12:/tmp
db1$ scp /tmp/database-backup.sql <user>@192.168.0.13:/tmp
db1$ scp /tmp/database-backup.sql <user>@192.168.0.14:/tmp

Then import this into db2, db3 and db4:

db2$ mysql -u root -p < /tmp/database-backup.sql
db3$ mysql -u root -p < /tmp/database-backup.sql
db4$ mysql -u root -p < /tmp/database-backup.sql

Then flush the privileges on db2, db3 and db4. We have altered the user-table and mysql has to reread this table.

(db2) mysql> FLUSH PRIVILEGES;
(db3) mysql> FLUSH PRIVILEGES;
(db4) mysql> FLUSH PRIVILEGES;

On debian and ubuntu, copy the passwords in /etc/mysql/debian.cnf from db1 to db2, db3 and db4. This password is used for starting and stopping mysql.

Both databases now contain the same data. We now can setup replication to keep it that way.

Note: Import just only add records from dump file. You should drop all databases before import dump file.

Setup replication

Configure replication on db2, db3 and db4 with the following commands:

(db2) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', 
              master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;
(db3) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', 
              master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;
(db4) mysql> CHANGE MASTER TO master_host='192.168.0.11', master_port=3306, master_user='replication', 
              master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

Please insert the values return by “show master status” on db1 at the <file> and <position> tags.

Start the slave-process on all 3 hosts:

(db2) mysql> START SLAVE;
(db3) mysql> START SLAVE;
(db4) mysql> START SLAVE;

Now check if the replication is running correctly on all hosts:

(db2) mysql> SHOW SLAVE STATUS\G
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 192.168.0.11
                Master_User: replication
                Master_Port: 3306 
              Connect_Retry: 60 
…
(db3) mysql> SHOW SLAVE STATUS\G
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 192.168.0.11
                Master_User: replication
                Master_Port: 3306 
              Connect_Retry: 60 
…
(db4) mysql> SHOW SLAVE STATUS\G
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 192.168.0.11
                Master_User: replication
                Master_Port: 3306 
              Connect_Retry: 60 
…

Now we have to make db1 replicate from db2. First we have to determine the values for master_log_file and master_log_pos:

(db2) mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000001 |       98 |              |                  |
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec)

Now we configure replication on db1 with the following command:

(db1) mysql> CHANGE MASTER TO master_host = '192.168.0.12', master_port=3306, master_user='replication',
              master_password='replication_password', master_log_file='<file>', master_log_pos=<position>;

Now insert the values return by “show master status” on db2 at the <file> and <position> tags.

Start the slave-process:

(db1) mysql> START SLAVE;

Now check if the replication is running correctly on db1:

(db1) mysql> SHOW SLAVE STATUS\G
*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: 192.168.0.12
                Master_User: <replication>
                Master_Port: 3306 
              Connect_Retry: 60 
…

Replication between the nodes should now be complete. Try it by inserting some data into both db1 and db2 and check that the data will appear on all other nodes.

Install MMM

Create user

Optional: Create user that will be the owner of the MMM scripts and configuration files. This will provide an easier method to securely manage the monitor scripts.

useradd --comment "MMM Script owner" --shell /sbin/nologin mmmd

Monitoring host

First install dependencies:

aptitude install liblog-log4perl-perl libmailtools-perl liblog-dispatch-perl libclass-singleton-perl libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl

Then fetch the latest mysql-mmm-common*.deb and mysql-mmm-monitor*.deb and install it:

dpkg -i mysql-mmm-common_*.deb mysql-mmm-monitor*.deb

Database hosts

On UbuntuFirst install dependencies:

aptitude install liblog-log4perl-perl libmailtools-perl liblog-dispatch-perl iproute libnet-arp-perl libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl

Then fetch the latest mysql-mmm-common*.deb and mysql-mmm-agent*.deb and install it:

dpkg -i mysql-mmm-common_*.deb mysql-mmm-agent_*.deb

On RedHat

yum install -y mysql-mmm-agent

This will take care of all the dependencies, which may include:

Installed:

mysql-mmm-agent.noarch 0:2.2.1-1.el5

Dependency Installed:

libart_lgpl.x86_64 0:2.3.17-4                                                 
mysql-mmm.noarch 0:2.2.1-1.el5                                                
perl-Algorithm-Diff.noarch 0:1.1902-2.el5                                     
perl-DBD-mysql.x86_64 0:4.008-1.rf                                            
perl-DateManip.noarch 0:5.44-1.2.1                                            
perl-IPC-Shareable.noarch 0:0.60-3.el5                                        
perl-Log-Dispatch.noarch 0:2.20-1.el5                                         
perl-Log-Dispatch-FileRotate.noarch 0:1.16-1.el5                              
perl-Log-Log4perl.noarch 0:1.13-2.el5                                         
perl-MIME-Lite.noarch 0:3.01-5.el5                                            
perl-Mail-Sender.noarch 0:0.8.13-2.el5.1                                      
perl-Mail-Sendmail.noarch 0:0.79-9.el5.1                                      
perl-MailTools.noarch 0:1.77-1.el5                                            
perl-Net-ARP.x86_64 0:1.0.6-2.1.el5                                           
perl-Params-Validate.x86_64 0:0.88-3.el5                                      
perl-Proc-Daemon.noarch 0:0.03-1.el5                                          
perl-TimeDate.noarch 1:1.16-5.el5                                             
perl-XML-DOM.noarch 0:1.44-2.el5                                              
perl-XML-Parser.x86_64 0:2.34-6.1.2.2.1                                       
perl-XML-RegExp.noarch 0:0.03-2.el5                                           
rrdtool.x86_64 0:1.2.27-3.el5                                                 
rrdtool-perl.x86_64 0:1.2.27-3.el5

Configure MMM

All generic configuration-options are grouped in a separate file called /etc/mysql-mmm/mmm_common.conf. This file will be the same on all hosts in the system:

active_master_role          writer
<host default>
    cluster_interface       eth0
    pid_path                /var/run/mmmd_agent.pid
    bin_path                /usr/lib/mysql-mmm/
    replication_user        replication
    replication_password    replication_password
    agent_user              mmm_agent
    agent_password          agent_password
</host>
<host db1>
    ip                      192.168.0.11
    mode                    master
    peer                    db2
</host>
<host db2>
    ip                      192.168.0.12
    mode                    master
    peer                    db1
</host>
<host db3>
    ip                      192.168.0.13
    mode                    slave
</host>
<host db4>
    ip                      192.168.0.14
    mode                    slave
</host>
<role writer>
    hosts                   db1, db2
    ips                     192.168.0.100
    mode                    exclusive
</role>
<role reader>
    hosts                   db1, db2, db3, db4
    ips                     192.168.0.101, 192.168.0.102, 192.168.0.103, 192.168.0.104
    mode                    balanced
</role>

Don't forget to copy this file to all other hosts (including the monitoring host).

On the database hosts we need to edit /etc/mysql-mmm/mmm_agent.conf. Change “db1” accordingly on the other hosts:

include mmm_common.conf
this db1

On the monitor host we need to edit /etc/mysql-mmm/mmm_mon.conf:

include mmm_common.conf
<monitor>
    ip                      127.0.0.1
    pid_path                /var/run/mmmd_mon.pid
    bin_path                /usr/lib/mysql-mmm/
    status_path             /var/lib/misc/mmmd_mon.status
    ping_ips                192.168.0.1, 192.168.0.11, 192.168.0.12, 192.168.0.13, 192.168.0.14
</monitor>
<host default>
    monitor_user            mmm_monitor
    monitor_password        monitor_password
</host>
debug 0

ping_ips are some ips that are pinged to determine whether the network connection of the monitor is ok. I used my switch (192.168.0.1) and the four database server.

Start MMM

Start the agents

(On the database hosts)

Debian/Ubuntu

Edit /etc/default/mysql-mmm-agent to enable the agent:

ENABLED=1

Red Hat

RHEL/Fedora does not enable packages to start at boot time per default policy, so you might have to turn it on manually so the agents will start automatically when server is rebooted:

chkconfig mysql-mmm-agent on

Then start it:

/etc/init.d/mysql-mmm-agent start

Start the monitor

(On the monitoring host)Edit /etc/default/mysql-mmm-monitor to enable the monitor:

ENABLED=1

Then start it:

/etc/init.d/mysql-mmm-monitor start

Wait some seconds for mmmd_mon to start up. After a few seconds you can use mmm_control to check the status of the cluster:

mon$ mmm_control show
  db1(192.168.0.11) master/AWAITING_RECOVERY. Roles: 
  db2(192.168.0.12) master/AWAITING_RECOVERY. Roles: 
  db3(192.168.0.13) slave/AWAITING_RECOVERY. Roles: 
  db4(192.168.0.14) slave/AWAITING_RECOVERY. Roles:

Because its the first startup the monitor does not know our hosts, so it sets all hosts to state AWAITING_RECOVERY and logs a warning message:

mon$ tail /var/log/mysql-mmm/mmmd_mon.warn
…
2009/10/28 23:15:28  WARN Detected new host 'db1': Setting its initial state to 'AWAITING_RECOVERY'. Use 'mmm_control set_online db1' to switch it online.
2009/10/28 23:15:28  WARN Detected new host 'db2': Setting its initial state to 'AWAITING_RECOVERY'. Use 'mmm_control set_online db2' to switch it online.
2009/10/28 23:15:28  WARN Detected new host 'db3': Setting its initial state to 'AWAITING_RECOVERY'. Use 'mmm_control set_online db3' to switch it online.
2009/10/28 23:15:28  WARN Detected new host 'db4': Setting its initial state to 'AWAITING_RECOVERY'. Use 'mmm_control set_online db4' to switch it online.

Now we set or hosts online (db1 first, because the slaves replicate from this host):

mon$ mmm_control set_online db1
OK: State of 'db1' changed to ONLINE. Now you can wait some time and check its new roles!
mon$ mmm_control set_online db2
OK: State of 'db2' changed to ONLINE. Now you can wait some time and check its new roles!
mon$ mmm_control set_online db3
OK: State of 'db3' changed to ONLINE. Now you can wait some time and check its new roles!
mon$ mmm_control set_online db4
OK: State of 'db4' changed to ONLINE. Now you can wait some time and check its new roles!

mysql-mmm实现mysql高可用

shanyiwan@live.com() — Tue, 31 Jan 2012 08:04:20 GMT

一、MMM简介：

MMM即Master-Master Replication Manager for MySQL（mysql主主复制管理器）关于mysql主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件（在任何时候只有一个节点可以被写入），这个套件也能对居于标准的主从配置的任意数量的从服务器进行读负载均衡，所以你可以用它来在一组居于复制的服务器启动虚拟ip，除此之外，它还有实现数据备份、节点之间重新同步功能的脚本。

MySQL本身没有提供replication failover的解决方案，通过MMM方案能实现服务器的故障转移，从而实现mysql的高可用。

MMM项目来自 Google：http://code.google.com/p/mysql-master-master

官方网站为：http://mysql-mmm.org

Mmm主要功能由下面三个脚本提供

lmmm_mond负责所有的监控工作的监控守护进程，决定节点的移除等等

lmmm_agentd运行在mysql服务器上的代理守护进程，通过简单远程服务集提供给监控节点

lmmm_control通过命令行管理mmm_mond进程

二、mysql-mmm架构的搭建

1、先介绍下本文的环境：

系统环境：CentOS release 5.4（32bit）

server1 ip: 192.168.1.161 virtual read ip:192.168.1.111
server2 ip: 192.168.1.162 virtual read ip:192.168.1.112
server3 ip: 192.168.1.163 virtual write ip: 192.168.1.113

2、mysql-mmm架构配置简介：

u在server1、server2上安装mysql，并配置为master-master架构（就是互为主从）----------配置很简单，就不对着部分进行详细解释，有问题的话请查看：http://blog.chinaunix.net/u3/93755/showart.php?id=2213538

u在server1、server2，server3上安装mmm，并配置：mmm_common.conf、mmm_agent.conf以及mmm_mon.conf文件

3、Mysql-mmm实战

前提：server1和server2上已经配置好mysql主主同步

u安装mysql-mmm

CentOS软件仓库默认是不含这些软件的，必须要有epel这个包的支持。故我们必须先安装epel：

wget http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-4.noarch.rpm

rpm -Uvh epel-release-5-4.noarch.rpm

yum -y install mysql-mmm*

u配置mmm代理和监控账号的权限

在server1和server2上分别执行：

GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.1.%' IDENTIFIED BY 'monitor_password';

GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.1.%'IDENTIFIED BY 'agent_password';

flush privileges;

u配置mysql-mmm

所有的配置选项都集合在了一个叫/etc/mysql-mmm/mmm_common.conf的单独文件中，系统中所有主机的该文件内容都是一样的, 配置完后不要忘记了拷贝这个文件到所有的主机（包括监控主机）！，内容如下：

active_master_rolewriter

cluster_interfaceeth0

pid_path/var/run/mysql-mmm/mmm_agentd.pid

bin_path/usr/libexec/mysql-mmm/

#同步的帐号（这些要和前面设置的保持一致！）

replication_userreplication

replication_password123456#同步的密码

agent_usermmm_agent#mmm-agent用户名

agent_passwordagent_password#mmm-agent用户密码

</host>

ip192.168.1.161#db1的ip

modemaster

peerdb2

</host>

ip192.168.1.162#db2的ip

modemaster

peerdb1

</host>

hostsdb1, db2

ips192.168.1.113#设置写如的虚拟IP

modeexclusive

</role>

hostsdb1, db2

ips192.168.1.111, 192.168.1.112#设置读取的虚拟IP

modebalanced

</role>

在数据库主机上我们需要编辑/etc/mysql-mmm/mmm_agent.conf文件，根据其他主机的不同更改db1的值（db2就将db1更改成db2）：

include mmm_common.conf
this db1

在监控主机上我们需要编辑/etc/mysql-mmm/mmm_mon.conf文件：

include mmm_common.conf

ip127.0.0.1

pid_path/var/run/mysql-mmm/mmm_mond.pid

bin_path/usr/libexec/mysql-mmm

status_path/var/lib/mysql-mmm/mmm_mond.status

ping_ips192.168.1.161,192.168.1.162#监控服务器ip

auto_set_online60

# The kill_host_bin does not exist by default, though the monitor will

# throw a warning about it missing.See the section 5.10 "Kill Host

# Functionality" in the PDF documentation.

# kill_host_bin/usr/libexec/mysql-mmm/monitor/kill_host

</monitor>

monitor_usermmm_monitor#mmm_monitor用户名

monitor_passwordmonitor_password #mmm_monitor密码

</host>

debug 0

u启动MMM

启动代理：

（在数据库服务器上server1、2）编辑/etc/default/mysql-mmm-agent来开启：

ENABLED=1

然后启动它：

/etc/init.d/mysql-mmm-agent start

启动监控（在监控机上）：

/etc/init.d/mysql-mmm-monitor start

u利用mmm_control监控mysql服务器状态：

[root@server3 mysql-mmm]# mmm_control show

db1(192.168.1.161) master/ONLINE. Roles: reader(192.168.1.112), writer(192.168.1.113)

db2(192.168.1.162) master/ONLINE. Roles: reader(192.168.1.111)

u测试看两个mysql服务器能否实现故障自动切换

停掉作为写的db1上的mysql，查看写的服务器会不会自动转移到db2上去

停掉几秒钟后用mmm_control show查看：

[root@server3 mysql-mmm]# mmm_control show

db1(192.168.1.161) master/HARD_OFFLINE. Roles:

db2(192.168.1.162) master/ONLINE. Roles: reader(192.168.1.111), reader(192.168.1.112), writer(192.168.1.113)

我们可以看到已经把db2当作主写服务器

再来看看db1恢复后会是什么情况：

[root@server3 mysql-mmm]# mmm_control show

db1(192.168.1.161) master/ONLINE. Roles: reader(192.168.1.111)

db2(192.168.1.162) master/ONLINE. Roles: reader(192.168.1.112), writer(192.168.1.113)

我们可以看到当db1恢复后就充当slave的角色了！只有当db2挂了以后db1又会担当起主服务器的写入功能

ummm_control命令简介

[root@server3 mysql-mmm]# mmm_control help

Valid commands are:

help- show this message

#查看帮助信息

ping- ping monitor

#ping监控

show- show status

#查看状态信息

checks [<host>|all [<check>|all]] - show checks status

#显示检查状态，包括（ping、mysql、rep_threads、rep_backlog）

set_online <host>- set host <host> online

#设置某host为online状态

set_offline <host>- set host <host> offline

#设置某host为offline状态

mode- print current mode.

#打印当前的模式，是ACTIVE、MANUAL、PASSIVE？

#默认ACTIVE模式

set_active- switch into active mode.

#更改为active模式

set_manual- switch into manual mode.

#更改为manual模式

set_passive- switch into passive mode.

#更改为passive模式

move_role [--force] <role> <host> - move exclusive role <role> to host <host>

#更改host的模式，比如更改处于slave的mysql数据库角色为write

(Only use --force if you know what you are doing!)

set_ip <ip> <host>- set role with ip <ip> to host <host>

#为host设置ip，只有passive模式的时候才允许更改！

VS 2010 SP1 and SQL CE

shanyiwan@live.com() — Wed, 04 Jan 2012 03:24:14 GMT

Last month we released the Beta of VS 2010 Service Pack 1 (SP1). You can learn more about the VS 2010 SP1 Beta from Jason Zander’s two blog posts about it, and from Scott Hanselman’s blog post that covers some of the new capabilities enabled with it. You can download and install the VS 2010 SP1 Beta here.

Last week I blogged about the new Visual Studio support for IIS Express that we are adding with VS 2010 SP1. In today’s post I’m going to talk about the new VS 2010 SP1 tooling support for SQL CE, and walkthrough some of the cool scenarios it enables.

SQL CE – What is it and why should you care?

SQL CE is a free, embedded, database engine that enables easy database storage.

No Database Installation Required

SQL CE does not require you to run a setup or install a database server in order to use it. You can simply copy the SQL CE binaries into the \bin directory of your ASP.NET application, and then your web application can use it as a database engine. No setup or extra security permissions are required for it to run. You do not need to have an administrator account on the machine. Just copy your web application onto any server and it will work. This is true even of medium-trust applications running in a web hosting environment.

SQL CE runs in-memory within your ASP.NET application and will start-up when you first access a SQL CE database, and will automatically shutdown when your application is unloaded. SQL CE databases are stored as files that live within the \App_Data folder of your ASP.NET Applications.

Works with Existing Data APIs

SQL CE 4 works with existing .NET-based data APIs, and supports a SQL Server compatible query syntax. This means you can use existing data APIs like ADO.NET, as well as use higher-level ORMs like Entity Framework and NHibernate with SQL CE. This enables you to use the same data programming skills and data APIs you know today.

Supports Development, Testing and Production Scenarios

SQL CE can be used for development scenarios, testing scenarios, and light production usage scenarios. With the SQL CE 4 release we’ve done the engineering work to ensure that SQL CE won’t crash or deadlock when used in a multi-threaded server scenario (like ASP.NET). This is a big change from previous releases of SQL CE – which were designed for client-only scenarios and which explicitly blocked running in web-server environments. Starting with SQL CE 4 you can use it in a web-server as well.

There are no license restrictions with SQL CE. It is also totally free.

Easy Migration to SQL Server

SQL CE is an embedded database – which makes it ideal for development, testing, and light-usage scenarios. For high-volume sites and applications you’ll probably want to migrate your database to use SQL Server Express (which is free), SQL Server or SQL Azure. These servers enable much better scalability, more development features (including features like Stored Procedures – which aren’t supported with SQL CE), as well as more advanced data management capabilities.

We’ll ship migration tools that enable you to optionally take SQL CE databases and easily upgrade them to use SQL Server Express, SQL Server, or SQL Azure. You will not need to change your code when upgrading a SQL CE database to SQL Server or SQL Azure. Our goal is to enable you to be able to simply change the database connection string in your web.config file and have your application just work.

New Tooling Support for SQL CE in VS 2010 SP1

VS 2010 SP1 includes much improved tooling support for SQL CE, and adds support for using SQL CE within ASP.NET projects for the first time. With VS 2010 SP1 you can now:

Create new SQL CE Databases
Edit and Modify SQL CE Database Schema and Indexes
Populate SQL CE Databases within Data
Use the Entity Framework (EF) designer to create model layers against SQL CE databases
Use EF Code First to define model layers in code, then create a SQL CE database from them, and optionally edit the DB with VS
Deploy SQL CE databases to remote servers using Web Deploy and optionally convert them to full SQL Server databases

You can take advantage of all of the above features from within both ASP.NET Web Forms and ASP.NET MVC based projects.

Download

You can enable SQL CE tooling support within VS 2010 by first installing VS 2010 SP1 (beta).

Once SP1 is installed, you’ll also then need to install the SQL CE Tools for Visual Studio download. This is a separate download that enables the SQL CE tooling support for VS 2010 SP1.

Walkthrough of Two Scenarios

In this blog post I’m going to walkthrough how you can take advantage of SQL CE and VS 2010 SP1 using both an ASP.NET Web Forms and an ASP.NET MVC based application. Specifically, we’ll walkthrough:

How to create a SQL CE database using VS 2010 SP1, then use the EF4 visual designers in Visual Studio to construct a model layer from it, and then display and edit the data using an ASP.NET GridView control.

How to use an EF Code First approach to define a model layer using POCO classes and then have EF Code-First “auto-create” a SQL CE database for us based on our model classes. We’ll then look at how we can use the new VS 2010 SP1 support for SQL CE to inspect the database that was created, populate it with data, and later make schema changes to it. We’ll do all this within the context of an ASP.NET MVC based application.

You can follow the two walkthroughs below on your own machine by installing VS 2010 SP1 (beta) and then installing the SQL CE Tools for Visual Studio download (which is a separate download that enables SQL CE tooling support for VS 2010 SP1).

Walkthrough 1: Create a SQL CE Database, Create EF Model Classes, Edit the Data with a GridView

This first walkthrough will demonstrate how to create and define a SQL CE database within an ASP.NET Web Form application. We’ll then build an EF model layer for it and use that model layer to enable data editing scenarios with an <asp:GridView> control.

Step 1: Create a new ASP.NET Web Forms Project

We’ll begin by using the File->New Project menu command within Visual Studio to create a new ASP.NET Web Forms project. We’ll use the “ASP.NET Web Application” project template option so that it has a default UI skin implemented:

Step 2: Create a SQL CE Database

Right click on the “App_Data” folder within the created project and choose the “Add->New Item” menu command:

This will bring up the “Add Item” dialog box. Select the “SQL Server Compact 4.0 Local Database” item (new in VS 2010 SP1) and name the database file to create “Store.sdf”:

Note that SQL CE database files have a .sdf filename extension. Place them within the /App_Data folder of your ASP.NET application to enable easy deployment.

When we clicked the “Add” button above a Store.sdf file was added to our project:

Step 3: Adding a “Products” Table

Double-clicking the “Store.sdf” database file will open it up within the Server Explorer tab. Since it is a new database there are no tables within it:

Right click on the “Tables” icon and choose the “Create Table” menu command to create a new database table. We’ll name the new table “Products” and add 4 columns to it. We’ll mark the first column as a primary key (and make it an identify column so that its value will automatically increment with each new row):

When we click “ok” our new Products table will be created in the SQL CE database.

Step 4: Populate with Data

Once our Products table is created it will show up within the Server Explorer. We can right-click it and choose the “Show Table Data” menu command to edit its data:

Let’s add a few sample rows of data to it:

Step 5: Create an EF Model Layer

We have a SQL CE database with some data in it – let’s now create an EF Model Layer that will provide a way for us to easily query and update data within it.

Let’s right-click on our project and choose the “Add->New Item” menu command. This will bring up the “Add New Item” dialog – select the “ADO.NET Entity Data Model” item within it and name it “Store.edmx”

This will add a new Store.edmx item to our solution explorer and launch a wizard that allows us to quickly create an EF model:

Select the “Generate From Database” option above and click next. Choose to use the Store.sdf SQL CE database we just created and then click next again.

The wizard will then ask you what database objects you want to import into your model. Let’s choose to import the “Products” table we created earlier:

When we click the “Finish” button Visual Studio will open up the EF designer. It will have a Product entity already on it that maps to the “Products” table within our SQL CE database:

The VS 2010 SP1 EF designer works exactly the same with SQL CE as it does already with SQL Server and SQL Express. The Product entity above will be persisted as a class (called “Product”) that we can programmatically work against within our ASP.NET application.

Step 6: Compile the Project

Before using your model layer you’ll need to build your project. Do a Ctrl+Shift+B to compile the project, or use the Build->Build Solution menu command.

Step 7: Create a Page that Uses our EF Model Layer

Let’s now create a simple ASP.NET Web Form that contains a GridView control that we can use to display and edit the our Products data (via the EF Model Layer we just created).

Right-click on the project and choose the Add->New Item command. Select the “Web Form from Master Page” item template, and name the page you create “Products.aspx”. Base the master page on the “Site.Master” template that is in the root of the project.

Add an <h2>Products</h2> heading the new Page, and add an <asp:gridview> control within it:

Then click the “Design” tab to switch into design-view. Select the GridView control, and then click the top-right corner to display the GridView’s “Smart Tasks” UI:

Choose the “New data source…” drop down option above. This will bring up the below dialog which allows you to pick your Data Source type:

Select the “Entity” data source option – which will allow us to easily connect our GridView to the EF model layer we created earlier. This will bring up another dialog that allows us to pick our model layer:

Select the “StoreEntities” option in the dropdown – which is the EF model layer we created earlier. Then click next – which will allow us to pick which entity within it we want to bind to:

Select the “Products” entity in the above dialog – which indicates that we want to bind against the “Product” entity class we defined earlier. Then click the “Enable automatic updates” checkbox to ensure that we can both query and update Products. When you click “Finish” VS will wire-up an <asp:EntityDataSource> to your <asp:GridView> control:

The last two steps we’ll do will be to click the “Enable Editing” checkbox on the Grid (which will cause the Grid to display an “Edit” link on each row) and (optionally) use the Auto Format dialog to pick a UI template for the Grid.

Step 8: Run the Application

Let’s now run our application and browse to the /Products.aspx page that contains our GridView. When we do so we’ll see a Grid UI of the Products within our SQL CE database. Clicking the “Edit” link for any of the rows will allow us to edit their values:

When we click “Update” the GridView will post back the values, persist them through our EF Model Layer, and ultimately save them within our SQL CE database.

Learn More about using EF with ASP.NET Web Forms

Read this tutorial series on the http://asp.net site to learn more about how to use EF with ASP.NET Web Forms.

The tutorial series uses SQL Express as the database – but the nice thing is that all of the same steps/concepts can also now also be done with SQL CE.

Walkthrough 2: Using EF Code-First with SQL CE and ASP.NET MVC 3

We used a database-first approach with the sample above – where we first created the database, and then used the EF designer to create model classes from the database.

In addition to supporting a designer-based development workflow, EF also enables a more code-centric option which we call “code first development”. Code-First Development enables a pretty sweet development workflow. It enables you to:

Define your model objects by simply writing “plain old classes” with no base classes or visual designer required
Use a “convention over configuration” approach that enables database persistence without explicitly configuring anything
Optionally override the convention-based persistence and use a fluent code API to fully customize the persistence mapping
Optionally auto-create a database based on the model classes you define – allowing you to start from code first

I’ve done several blog posts about EF Code First in the past – I really think it is great. The good news is that it also works very well with SQL CE.

The combination of SQL CE, EF Code First, and the new VS tooling support for SQL CE, enables a pretty nice workflow. Below is a simple example of how you can use them to build a simple ASP.NET MVC 3 application.

Step 1: Create a new ASP.NET MVC 3 Project

We’ll begin by using the File->New Project menu command within Visual Studio to create a new ASP.NET MVC 3 project. We’ll use the “Internet Project” template so that it has a default UI skin implemented:

Step 2: Use NuGet to Install EFCodeFirst

Next we’ll use the NuGet package manager (automatically installed by ASP.NET MVC 3) to add the EFCodeFirst library to our project. We’ll use the Package Manager command shell to do this. Bring up the package manager console within Visual Studio by selecting the View->Other Windows->Package Manager Console menu command. Then type:

install-package EFCodeFirst

within the package manager console to download the EFCodeFirst library and have it be added to our project:

When we enter the above command, the EFCodeFirst library will be downloaded and added to our application:

Step 3: Build Some Model Classes

Using a “code first” based development workflow, we will create our model classes first (even before we have a database). We create these model classes by writing code.

For this sample, we will right click on the “Models” folder of our project and add the below three classes to our project:

The “Dinner” and “RSVP” model classes above are “plain old CLR objects” (aka POCO). They do not need to derive from any base classes or implement any interfaces, and the properties they expose are standard .NET data-types. No data persistence attributes or data code has been added to them.

The “NerdDinners” class derives from the DbContext class (which is supplied by EFCodeFirst) and handles the retrieval/persistence of our Dinner and RSVP instances from a database.

Step 4: Listing Dinners

We’ve written all of the code necessary to implement our model layer for this simple project.

Let’s now expose and implement the URL: /Dinners/Upcoming within our project. We’ll use it to list upcoming dinners that happen in the future.

We’ll do this by right-clicking on our “Controllers” folder and select the “Add->Controller” menu command. We’ll name the Controller we want to create “DinnersController”. We’ll then implement an “Upcoming” action method within it that lists upcoming dinners using our model layer above. We will use a LINQ query to retrieve the data and pass it to a View to render with the code below:

We’ll then right-click within our Upcoming method and choose the “Add-View” menu command to create an “Upcoming” view template that displays our dinners. We’ll use the “empty” template option within the “Add View” dialog and write the below view template using Razor:

Step 4: Configure our Project to use a SQL CE Database

We have finished writing all of our code – our last step will be to configure a database connection-string to use.

We will point our NerdDinners model class to a SQL CE database by adding the below <connectionString> to the web.config file at the top of our project:

EF Code First uses a default convention where context classes will look for a connection-string that matches the DbContext class name. Because we created a “NerdDinners” class earlier, we’ve also named our connectionstring “NerdDinners”. Above we are configuring our connection-string to use SQL CE as the database, and telling it that our SQL CE database file will live within the \App_Data directory of our ASP.NET project.

Step 5: Running our Application

Now that we’ve built our application, let’s run it!

We’ll browse to the /Dinners/Upcoming URL – doing so will display an empty list of upcoming dinners:

You might ask – but where did it query to get the dinners from? We didn’t explicitly create a database?!?

One of the cool features that EF Code-First supports is the ability to automatically create a database (based on the schema of our model classes) when the database we point it at doesn’t exist. Above we configured EF Code-First to point at a SQL CE database in the \App_Data\ directory of our project. When we ran our application, EF Code-First saw that the SQL CE database didn’t exist and automatically created it for us.

Step 6: Using VS 2010 SP1 to Explore our newly created SQL CE Database

Click the “Show all Files” icon within the Solution Explorer and you’ll see the “NerdDinners.sdf” SQL CE database file that was automatically created for us by EF code-first within the \App_Data\ folder:

We can optionally right-click on the file and “Include in Project" to add it to our solution:

We can also double-click the file (regardless of whether it is added to the project) and VS 2010 SP1 will open it as a database we can edit within the “Server Explorer” tab of the IDE.

Below is the view we get when we double-click our NerdDinners.sdf SQL CE file. We can drill in to see the schema of the Dinners and RSVPs tables in the tree explorer.

Notice how two tables - Dinners and RSVPs – were automatically created for us within our SQL CE database. This was done by EF Code First when we accessed the NerdDinners class by running our application above:

We can right-click on a Table and use the “Show Table Data” command to enter some upcoming dinners in our database:

We’ll use the built-in editor that VS 2010 SP1 supports to populate our table data below:

And now when we hit “refresh” on the /Dinners/Upcoming URL within our browser we’ll see some upcoming dinners show up:

Step 7: Changing our Model and Database Schema

Let’s now modify the schema of our model layer and database, and walkthrough one way that the new VS 2010 SP1 Tooling support for SQL CE can make this easier.

With EF Code-First you typically start making database changes by modifying the model classes. For example, let’s add an additional string property called “UrlLink” to our “Dinner” class. We’ll use this to point to a link for more information about the event:

Now when we re-run our project, and visit the /Dinners/Upcoming URL we’ll see an error thrown:

We are seeing this error because EF Code-First automatically created our database, and by default when it does this it adds a table that helps tracks whether the schema of our database is in sync with our model classes. EF Code-First helpfully throws an error when they become out of sync – making it easier to track down issues at development time that you might otherwise only find (via obscure errors) at runtime. Note that if you do not want this feature you can turn it off by changing the default conventions of your DbContext class (in this case our NerdDinners class) to not track the schema version.

Our model classes and database schema are out of sync in the above example – so how do we fix this? There are two approaches you can use today:

Delete the database and have EF Code First automatically re-create the database based on the new model class schema (losing the data within the existing DB)
Modify the schema of the existing database to make it in sync with the model classes (keeping/migrating the data within the existing DB)

There are a couple of ways you can do the second approach above. Below I’m going to show how you can take advantage of the new VS 2010 SP1 Tooling support for SQL CE to use a database schema tool to modify our database structure. We are also going to be supporting a “migrations” feature with EF in the future that will allow you to automate/script database schema migrations programmatically.

Step 8: Modify our SQL CE Database Schema using VS 2010 SP1

The new SQL CE Tooling support within VS 2010 SP1 makes it easy to modify the schema of our existing SQL CE database. To do this we’ll right-click on our “Dinners” table and choose the “Edit Table Schema” command:

This will bring up the below “Edit Table” dialog. We can rename, change or delete any of the existing columns in our table, or click at the bottom of the column listing and type to add a new column. Below I’ve added a new “UrlLink” column of type “nvarchar” (since our property is a string):

When we click ok our database will be updated to have the new column and our schema will now match our model classes.

Because we are manually modifying our database schema, there is one additional step we need to take to let EF Code-First know that the database schema is in sync with our model classes. As i mentioned earlier, when a database is automatically created by EF Code-First it adds a “EdmMetadata” table to the database to track schema versions (and hash our model classes against them to detect mismatches between our model classes and the database schema):

Since we are manually updating and maintaining our database schema, we don’t need this table – and can just delete it:

This will leave us with just the two tables that correspond to our model classes:

And now when we re-run our /Dinners/Upcoming URL it will display the dinners correctly:

One last touch we could do would be to update our view to check for the new UrlLink property and render a <a> link to it if an event has one:

And now when we refresh our /Dinners/Upcoming we will see hyperlinks for the events that have a UrlLink stored in the database:

Summary

SQL CE provides a free, embedded, database engine that you can use to easily enable database storage. With SQL CE 4 you can now take advantage of it within ASP.NET projects and applications (both Web Forms and MVC).

VS 2010 SP1 provides tooling support that enables you to easily create, edit and modify SQL CE databases – as well as use the standard EF designer against them. This allows you to re-use your existing skills and data knowledge while taking advantage of an embedded database option. This is useful both for small applications (where you don’t need the scalability of a full SQL Server), as well as for development and testing scenarios – where you want to be able to rapidly develop/test your application without having a full database instance.

SQL CE makes it easy to later migrate your data to a full SQL Server or SQL Azure instance if you want to – without having to change any code in your application. All we would need to change in the above two scenarios is the <connectionString> value within the web.config file in order to have our code run against a full SQL Server. This provides the flexibility to scale up your application starting from a small embedded database solution as needed.

Connection strings for SQL Server Compact Edition

shanyiwan@live.com() — Wed, 04 Jan 2012 02:55:51 GMT

.NET Compact Framework Data Provider for SQL Server Mobile

Type.NET Framework Class Library
UsageSystem.Data.SqlServerCe.SqlCeConnection
ManufacturerMicrosoft

Set example values »

More about this class library »

Standard

Data Source=MyData.sdf;Persist Security Info=False;

How to specify the location of the SDF file

Often times the .SDF database is not running in the current directory so it becomes necessary to programatically set the path to the SDF file. This is an example (.net C#) on how to do this when the SDF file is located in the same directory as the executing application.

Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\MyData.sdf;Persist Security Info=False;

Standard

Data Source=MyData.sdf;Persist Security Info=False;

Specifying the maximum database size

The maximum size of the database is by default 128 MB. Override this by using the following connection string.

Data Source=MyData.sdf;Max Database Size=256;Persist Security Info=False;

Specifying the maximum buffer size

The largest amount of memory that can be in use before the server starts flushing changes to disk is by default 640 kB. Override this by using the following connection string.

Data Source=MyData.sdf;Max Buffer Size=1024;Persist Security Info=False;

Encryption enabled

Use this connection string to enable encryption on the database.

Data Source=MyData.sdf;Encrypt Database=True;Password=myPassword;File Mode=shared read;Persist Security Info=False;

The Encrypt Database="True" pair is really not necessary as the presence of the Password-parameter itself turns on encryption for the connection.

Exclusive access

Use this one to disallow other processes from opening or modifying the database while you have it open.

Data Source=MyData.sdf;File Mode=Exclusive;Persist Security Info=False;

Read only access

Use this one to open a read-only copy of the database.

Data Source=MyData.sdf;File Mode=Read Only;Persist Security Info=False;

Exclusive but shared for reading

Use this one to allow other processes to read, but not modify, the database while you have it open.

Data Source=MyData.sdf;File Mode=Shared Read;Persist Security Info=False;

Specifying the maximum temp file size

The maximum size of the temporary database file is by default 128 MB. Override this by using the following connection string.

Data Source=MyData.sdf;Temp File Max Size=256;Persist Security Info=False;

Microsoft.SQLSERVER.CE.OLEDB.3.5

TypeOLE DB Provider
UsageProvider=Microsoft.SQLSERVER.CE.OLEDB.3.5
ManufacturerMicrosoft

Set example values »

More about this provider »

Standard

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=myPath\myData.sdf;

Microsoft.SQLSERVER.MOBILE.OLEDB.3.0

TypeOLE DB Provider
UsageProvider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0
ManufacturerMicrosoft

Set example values »

More about this provider »

Standard

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=myPath\myData.sdf;

How to specify the location of the SDF file

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\MyData.sdf;

Specifying the maximum database size

The maximum size of the database is by default 128 MB. Override this by using the following connection string.

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=myPath\myData.sdf;SSCE:Max Database Size=256;

Specifying the maximum buffer size

The largest amount of memory that can be in use before the server starts flushing changes to disk is by default 640 kB. Override this by using the following connection string.

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=myPath\myData.sdf;SSCE:Max Buffer Size=1024;

Encryption enabled

Use this connection string to enable encryption on the database.

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=myPath\myData.sdf;SSCE:Encrypt Database=True;

Specifying the maximum temp file size

The maximum size of the temporary database file is by default 128 MB. Override this by using the following connection string.

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=myPath\myData.sdf;SSCE:Temp File Max Size=256;

Specifying location of temp file

Set the directory for the temp file location using this connection string.

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=myPath\myData.sdf;SSCE:Temp File Directory="\myTempDir\";

Supplying the database password

Use this connection string to provide the database password when opening the connection.

Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=myPath\myData.sdf;SSCE:Database Password='myPassword';

.NET Framework Data Provider for OLE DB

Type.NET Framework Wrapper Class Library
UsageSystem.Data.OleDb.OleDbConnection
ManufacturerMicrosoft

Set example values »

More about this wrapper class library »

Bridging to Microsoft.SQLSERVER.CE.OLEDB.3.5

This is just one connection string sample for the wrapping OleDbConnection class that calls the underlying OLEDB provider. See respective OLE DB provider for more connection strings to use with this class.

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=myPath\myData.sdf;

Binlog has bad magic number

shanyiwan@live.com() — Thu, 10 Nov 2011 07:40:17 GMT

症状：

mysql数据库无法正常启动，日志如下：

111110 14:41:22 InnoDB: Starting shutdown...
111110 14:41:28 InnoDB: Shutdown completed; log sequence number 0 103559
111110 14:41:28 [Note] MySQL: Shutdown complete

111110 14:43:06 [Note] Plugin 'FEDERATED' is disabled.
111110 14:43:06 InnoDB: Started; log sequence number 0 103559
111110 14:43:06 [ERROR] Binlog has bad magic number; It's not a binary log file that can be used by this version of MySQL
111110 14:43:06 [ERROR] Can't init tc log
111110 14:43:06 [ERROR] Aborting

111110 14:43:06 InnoDB: Starting shutdown...
111110 14:43:11 InnoDB: Shutdown completed; log sequence number 0 103559
111110 14:43:11 [Note] MySQL: Shutdown complete

解决办法：

删除最后一个mysql-bin.x，打开mysql-bin.index 文件，删除对应的mysql-bin.x条目，重启。（未经测试）

IDS 10.0 supports multiple MSC VPs

shanyiwan@live.com() — Fri, 28 Oct 2011 01:23:58 GMT

Until Informix Dynamic Server 10.0 the number of MSC (or Miscellaneous) Virtual Processors was limited to one. With IDS 10.0 multiple MSC VPs can be configured using the VPCLASS onconfig parameter (e.g. VPCLASS msc,num=2), or added dynamically using onmode -p 1 msc.

What does that mean and why would I want to use it?
The MSC VP is a type of AIO VP, executing operating system calls on behalf of other threads. While the AIO VP mainly takes care of filesystem I/O calls the MSC VP executes some network and authentication calls like getpwnam(), gethostbyname(), gethostbyaddr() etc. (It also maintains some statistics like VP CPU usage.) The MSC VP gets used at client connection time for the operating system part of user validation, and in most cases one is sufficient.

Get to the point
In some cases, for example when a connection pool creates a large number of client connections at once, and/or when a PAM (Pluggable Authentication Module) implementation causes the operating system part of user authentication to take longer, the MSC VP can either be very busy serving the new connections or waiting for the OS to respond. When this happens new client connections can take longer to service and users will see a longer delay when connecting for the first time. Situations like this might benefit from having an extra MSC VP. Running onstats like onstat -g iov, onstat -g glo and onstat -g stk <tid> can help show what the MSC VP is doing but keep in mind there are other possible causes when new connections experience delays (such as operating system and network problems) so some troubleshooting (and tech support assistance) would generally be required, but at least dynamically adding a VP is an easy test to make if you suspect this might help.

注意：由于MSC VP会涉及到getpwnam(), gethostbyname(), gethostbyaddr() 调用，因此，对于informix的客户端的主机名解析可能会存在向外部DNS请求查询，而此可能会导致延时，因此，informix服务器上最好是做好各客户端主机名在本地hosts文件中的注册，以便减少因解析主机而带来的延时。同时要注意服务器端与客户端服务器端间的信任关系，否则可能会出现956的报错：

01:00:01 listener-thread: err = -956: oserr = 0: errstr = (informix@hostname): Client host or user (informix@hostname) is not trusted by the server.
11:22:13 listener-thread: err = -956: oserr = 0: errstr = (nobody@hostname): Client host or user (nobody@hostname) is not trusted by the server.
15:34:16 listener-thread: err = -956: oserr = 0: errstr = (nobody@hostname): Client host or user (nobody@hostname) is not trusted by the server.

# finderr 956
/home/informix/bin/finderr: line 79: test: 20020.9: integer expression expected
-956 Client client-name is not in /etc/hosts.equiv on the remote host.

This operation cannot be completed because the specified remote computer
does not recognize the name of the client computer (the local host,
whose name the hostname command returns). Client names are normally
recorded in the configuration file /etc/hosts.equiv. The rhosts file on
the remote computer might also need modification. For more information
on these files and the relation between them, try man rhosts on a UNIX
system. See the remote host administrator to ensure that the client
host name is specified in /etc/hosts.equiv file for the remote host.
This message appears in versions prior to Version 6.0.

-956 Client client-name or user is not trusted by the database server.

The desired database server does not consider the client computer or
your user ID trusted. Ask the system administrator to include the
client computer as a trusted host and/or your user id as a trusted user
in the /etc/hosts.equiv file on the server computer or in the .rhosts
file in your home directory on the server computer. See the UNIX manual
pages on HOSTS.EQUIV(5) for more information.

Alternatively, you can explicitly specify your user name and password
in the USER clause of a CONNECT statement or in the .netrc file in your
home directory on the client computer. See the IBM Informix Guide to SQL:
Syntax for information on the CONNECT statement, and the UNIX manual
pages regarding NETRC(5) for information on the .netrc file. This
message appears with Version 6.0 and later.

资源：

有关涉及到informix连接性能的信息

Slow connect time to Informix when using TCP/IP

Connections to IDS hang

Configuring Informix Virtual Processors

shanyiwan@live.com() — Fri, 28 Oct 2011 01:20:05 GMT

Virtual Processor (VP) is the technical term for an Informix process. Informix forks off a handful of these VPs at engine startup to take advantage of multiple CPUs or cores and increase parallelism. Each VP is multithreaded allowing Informix to efficiently use the CPU time each process is awarded by the OS, letting ready threads do work while other threads are busy waiting on something else to finish.

There are multiple types of VPs, each performing a specific job. Some VPs are configurable (i.e. the number of VPs and what CPU they should run on, etc.) through the VPCLASS ONCONFIG parameter and some are not. The 2 VPs you need to be concerned about right now are:

CPU - Runs all session threads and some system threads. Runs KAIO (Kernel Asynchronous I/O) if enabled. This process will consume most of the CPU cycles given to Informix by the OS and is configurable.
AIO - Performs all I/O except for physical or logical logging if KAIO is disabled. Performs I/O to cooked chunks if KAIO is enabled and is configurable.

Initial CPUVP Configuration

As the name implies, CPUVPs will take up the bulk of the CPU time allotted to Informix by the operating system. If you are running on a server that is dedicated to running Informix and nothing else (optimal, IMO) then you don't have to care too much about Informix hogging all of the CPU resources and can configure CPUVPs accordingly, leaving only some CPU cycles for the OS itself. If you are running additional applications (web server, middleware, etc.) on the same server as Informix you have to be careful to not over configure CPUVPs as this will impact the performance of the other applications.

Follow the following guidelines for ONCONFIG parameters based on the total number of CPU cores available to initially configure CPUVPs on a server dedicted to running the Informix engine. Process aging by the OS is disabled in all configurations to prevent the OS from reducing the priority of the long running Informix processes.

1 Core (example: Single CPU with a single core)

MULTIPROCESSOR 0
SINGLE_CPU_VP  1
VPCLASS        cpu,num=1,noage

2 Cores (example: Single CPU with 2 cores, 2 CPUs with a single core)

MULTIPROCESSOR 1
SINGLE_CPU_VP  1
VPCLASS        cpu,num=1,noage

4 Cores (example: Single CPU with 4 cores, 2 CPUs with 2 cores)

MULTIPROCESSOR 1
SINGLE_CPU_VP  0
VPCLASS        cpu,num=3,noage

8 Cores (example: 2 CPUs with 4 cores, 4 CPUs with 2 cores)

MULTIPROCESSOR 1
SINGLE_CPU_VP  0
VPCLASS        cpu,num=7,noage

8 Cores and Innovator-C that is limited to 4 CPUVPs

MULTIPROCESSOR 1
SINGLE_CPU_VP  0
VPCLASS        cpu,num=4,noage

See a pattern forming? Set MULTIPROCESSOR to 0 if you only have 1 core and set to 1 for everything else. Only configure 1 CPUVP if you have 2 or fewer cores and set SINGLE_CPU_VP to 1 to prevent additional CPUVPs from being added. Finally, configure CPUVPs = physical cores - 1 for everything else.

Monitoring and Tuning CPUVPs

A new column of output was added to onstat -g glo (the command to display a summary of all VPs) called Eff, for efficiency. This column will tell us the percentage of time a VP's threads were actually running on a CPU vs the time they where scheduled to run by Informix. A high percentage indicates that a physical CPU was able to run a thread when Informix asked it too and a low percentage indicates that a thread had to wait for CPU cycles to become available. Warning: There is a bug with onstat -g glo that is fixed in 11.50.xC6, if you run onstat -z the efficiency is incorrect.

informix> onstat -g glo
IBM Informix Dynamic Server Version 11.50.UC7IE   -- On-Line (Prim) -- Up 5 days 10:18:47 -- 1510960 Kbytes
MT global info:
sessions threads  vps      lngspins
42       182      21       2368
          sched calls     thread switches yield 0   yield n   yield forever
total:    483306514       338088588       149128716 10772763  41926442
per sec:  5085            4404            960       119       447
Virtual processor summary:
 class       vps       usercpu   syscpu    total
 cpu         3         30455.42  1617.50   32072.92
 aio         10        80.52     137.88    218.40
 lio         1         24.49     70.51     95.00
 pio         1         0.12      0.46      0.58
 adm         1         0.12      0.05      0.17
 soc         3         543.29    841.82    1385.11
 msc         1         94.16     62.45     156.61
 fifo        1         0.06      0.10      0.16
 total       21        31198.18  2730.77   33928.95
Individual virtual processors:
 vp    pid       class       usercpu   syscpu    total     Thread    Eff
 1     2204      cpu         12208.16  776.47    12984.63  13890.89  93%
 2     2230      adm         0.12      0.05      0.17      0.00       0%
 3     2231      cpu         11919.15  558.55    12477.70  12477.70 100%
 4     2232      cpu         6328.11   282.48    6610.59   6610.59  100%
 5     2233      lio         24.49     70.51     95.00     1800.72    5%
 6     2236      pio         0.12      0.46      0.58      33.14      1%
 7     2240      aio         57.64     94.05     151.69    4804.89    3%
 8     2244      msc         94.16     62.45     156.61    198.29    78%
 9     2248      fifo        0.06      0.10      0.16      0.00       0%
 10    2252      aio         9.56      21.31     30.87     2620.68    1%
 11    2253      aio         3.86      5.81      9.67      1469.70    0%
 12    2254      aio         1.96      3.33      5.29      1170.85    0%
 13    2256      aio         1.57      2.76      4.33      1092.83    0%
 14    2257      aio         1.41      2.55      3.96      1047.00    0%
 15    2258      aio         1.28      2.25      3.53      1019.12    0%
 16    2260      aio         1.23      2.07      3.30      980.02     0%
 17    2262      aio         1.08      2.05      3.13      934.60     0%
 18    2263      aio         0.93      1.70      2.63      896.20     0%
 19    2264      soc         179.83    272.86    452.69    NA         NA
 20    2265      soc         197.81    310.80    508.61    NA         NA
 21    2267      soc         165.65    258.16    423.81    NA         NA
                 tot         31198.18  2730.77   33928.95

Take a look at the cpu output of the Individual virtual processors section. If you have one or more CPUVPs with an efficiency at or near 100% then each time this CPUVP asked a CPU to do something the CPU was available. This is an indication that there are enough free CPU cycles that can handle additional CPUVPs.

Just because you can add more CPUVPs doesn't mean it needs to be done. If your CPUVPs are handling the current load then there is no need to add more. Monitor the ready queue via onstat -g rea during peak times to determine if more CPUVPs will help. Threads that appear in this output are waiting for a CPUVP to become available so it can run.

informix> onstat -r 1 -g rea # -r 1 will will repeat the onstat -g rea command every second
IBM Informix Dynamic Server Version 11.50.UC7IE   -- On-Line (Prim) -- Up 5 days 10:31:29 -- 1510960 Kbytes
Ready threads:
 tid     tcb              rstcb            prty status                vp-class       name
 29246867e41290d0         e12f9f30         1    ready                   4cpu         sqlexec
IBM Informix Dynamic Server Version 11.50.UC7IE   -- On-Line (Prim) -- Up 5 days 10:31:30 -- 1510960 Kbytes
Ready threads:
 tid     tcb              rstcb            prty status                vp-class       name
IBM Informix Dynamic Server Version 11.50.UC7IE   -- On-Line (Prim) -- Up 5 days 10:31:31 -- 1510960 Kbytes
Ready threads:
 tid     tcb              rstcb            prty status                vp-class       name

In the first onstat -g rea output there is 1 sqlexec thread waiting for a free CPUVP. The occasional thread in the ready queue isn't cause for much alarm, but if there were constantly threads in the queue I would consider increasing CPUVPs if I have free CPU cycles.

You must also use OS utilities (top and sar, etc.) to determine if there are available CPU cycles. Very low % CPU Idle and high Load Averages are a good indication that additional CPU resources are not available.

If your CPUVP efficiency is low (oh I don't know, less than 70%? Just a guess, this is a relatively new metric) then this can mean you have too many CPUVPs configured or your actual CPUs are overloaded and you might want to reduce CPUVPs or add additional CPUs to the system.

Initial AIOVP Configuration

If KAIO is disabled (another topic for another day) all I/O except for physical and logical logging will be performed by an AIO VP. I like to use 1 AIOVP per physical disk containing database chunks + 2 AIOVPs to handle writing to the message log, sqexplain.out, sort files in PSORT_DBTEMP, etc.

To configure 5 AIOVPs for an engine using 3 disks for chunks uncomment the VPCLASS aio ONCONFIG parameter (or modify it if is already uncommented).

VPCLASS aio,num=5,noage

Monitoring and Tuning AIOVPs

The good news is there really isn't too much penalty for having too many AIOVPs. If an AIOVP isn't needed it will just sit there idle, no biggie. But wait, there's more! If the AUTO_AIOVPS ONCONFIG parameter is 1 the engine will dynamically add AIOVPs on the fly for you if they are needed.

For a more hands on approach to tuning AIOVPs, use onstat -g iov to monitor the number of I/Os per Wakeup for each AIOVP. We are looking for the most active AIOVP to have an I/O per Wakeup of 1.0 which means each time this AIOVP was woken up to do some I/O there was only 1 I/O request needing to be processed. If there are no requests backing up in the queue then our AIOVPs are keeping up with the demand. The other AIOVPs will likely have more than 1.0 I/O per Wakeup, this is OK.

informix> onstat -g iov
IBM Informix Dynamic Server Version 11.50.UC7IE   -- On-Line (Prim) -- Up 5 days 11:11:11 -- 1510960 Kbytes
AIO I/O vps:
class/vp/id s  io/s totalops  dskread dskwrite  dskcopy  wakeups  io/wup  errors tempops
 fifo  9  0 i   0.0        0        0        0        0        0   0.0       0        0
  msc  8  0 i   0.2   343147        0        0        0   343081   1.0       0   343170
  aio  7  0 i  62.0  4761188  3052788   102515        0  4680405   1.0       0    23338
  aio 10  1 i   7.9   604355   487107    38564        0   513321   1.2       0     1036
  aio 11  2 i   3.0   227818   166585    30099        0   180650   1.3       0      222
  aio 12  3 i   1.7   130962    98601    26027        0    88725   1.5       0        7
  aio 13  4 i   1.4   107353    81510    24218        0    69842   1.5       0        5
  aio 14  5 i   1.2    95549    72486    22105        0    61630   1.6       0        5
  aio 15  6 i   1.1    87313    65094    21449        0    57238   1.5       0        3
  aio 16  7 i   1.1    82456    61836    19979        0    54071   1.5       0        3
  aio 17  8 i   1.0    77195    57215    19367        0    49859   1.5       0        4
  aio 18  9 i   0.9    70738    51898    18305        0    45489   1.6       0        5
  pio  6  0 i   0.1     4326        0     4326        0     4326   1.0       0    37156
  lio  5  0 i  31.3  2405192        0  2405192        0  2396927   1.0       0 19652116

If adding AIOVPs does not help the most active AIOVP reach 1.0 I/O per Wakeup this is a good indication that your disks are saturated and increasing AIOVPs won't help improve performance.

informix 自动备份逻辑日志时出错

shanyiwan@live.com() — Fri, 14 Oct 2011 06:11:17 GMT

环境配置及症状：

AS4U8+IDS9.21.UC2

$ONCONFIG

ALARMPROGRAM /home/informix/etc/log_full.sh # Alarm program path

bar_act.log

2011-10-14 13:48:40 8063 8061 /home/informix/bin/onbar_d -b -l
2011-10-14 13:48:40 8063 8061 Begin backup logical log 311991.
2011-10-14 13:48:40 8063 8061 Successfully connected to Storage Manager.
2011-10-14 13:48:40 8063 8061 ASSERT: file bar_xbsa.ecbarSendData() line 1331 - contact product support
2011-10-14 13:48:40 8063 8061 See also: /home/informix/tmp/core

online.log

13:48:39 Logical Log 311993 Complete.
13:48:40 Logical Log 311991 - Backup Started
13:48:40 Logical Log 311991 - Backup Aborted
Aborted by client.
13:48:40 Process exited with return code 139: /bin/sh /bin/sh -c /home/informix/etc/log_full.sh 2 23 "Logical Log 311993 Complete." "Logical Log 311993 Complete."

系统逻辑日志满时，自动触发/home/informix/etc/log_full.sh 进行逻辑日志备份，bar_act.log及online.log日志信息如上（手工进行逻辑日志备份正常）

原因：

与参数SHMVIRTSIZE （# initial virtual shared memory segment size）有关

解决办法：

将SHMVIRTSIZE值设置小一点，我的实例下，将原来参数调整为32000，如果调整后仍然有问题，试着重启一下ISM（ism_shutdown->ism_startup）

SHMVIRTSIZE configuration parameter

Use the SHMVIRTSIZE configuration parameter to specify the initial size of a virtual shared-memory segment.

onconfig.std value

32656

if not present

If SHMADD is present: SHMADD
If SHMADD is not present: 8192

units

Kilobytes

range of values

32-bit platforms: Positive integer with a maximum value of 2 gigabytes

64-bit platforms: Positive integer with a maximum value of 4 terabytes

The maximum value might be less on some platforms due to operating-system limitations. For the actual maximum value for your UNIX platform, see the machine notes.

takes effect

When the database server is shut down and restarted

utilities

onstat -g seg (see onstat -g seg command: Print shared memory segment statistics)

refer to

Virtual portion of shared memory, in the shared-memory chapter of the IBM Informix Administrator's Guide
Chapter on configuration effects on memory utilization, in your IBM Informix Performance Guide

To determine the appropriate value for the SHMVIRTSIZE configuration parameter, use the following algorithm to determine the size of the virtual portion of shared memory:

shmvirtsize = fixed overhead + ((stack size + heap) * number of users)

Component	Value to use
Fixed overhead	This includes the size of the AIO vectors + sort memory + dbspace backup buffers + dictionary size + size of stored-procedure cache + histogram pool + other pools, and other overhead. To obtain an estimate of the fixed overhead, start the database server and see how many additional memory segments are allocated, if any. The number of users that you have on the system when you start the server, impacts the allocation of memory segments. When you start the server: If the number of users is typical for your environment, then add the size of the memory segments to the current value for the SHMVIRTSIZE configuration parameter and restart the server. If the number of users is far less than what is typical for your environment, you must calculate the appropriate overhead value to use for the memory segments. You can determine how many memory segments each user will consume by dividing the number of additional memory segments allocated when you started the server by the number of users that you had on the server at that time. Multiply the value for the memory segments for each user by the number of users that you will typically have on the system. Add this calculated value for the memory segments to the current value for SHMVIRTSIZE configuration parameter and restart the server.
Stack size	On 32-bit systems, use 32 kilobytes for the stack size. Typically on 64-bit systems, you use 64 kilobytes for the stack size. However, some 64-bit systems use a different value. For more information see the STACKSIZE configuration parameter.
Heap	Use 30 kilobytes per user.
Number of users	Use the maximum number of concurrent user sessions that you anticipate on the server.

If possible, create a virtual portion of shared memory of a size that is more than you require for your daily processing.

Use the onstat -g seg command to determine peak usage and lower the value of the SHMVIRTSIZE configuration parameter accordingly.

Definition(SAP)

The

SHMVIRTSIZEparameter in the Informix ONCONFIGfile specifies how much shared memory in KB to use for the "virtual portion" at system start. Informix stores various objects here, including the dictionary cache.

The most important factor affecting the size of virtual portion is the session pools. If a large number of users are very active, Informix adds memory to this area. This dynamic allocation of shared memory can affect performance considerably, so you should allocate a large initial segment to avoid dynamic increases.

Use
For up-to-date recommendations, see SAP Note 38307.
Adjust the parameters

SHMVIRTSIZEand SHMADDto the actual storage requirements of the system for the following reasons:main screen of the database monitor to check for Virtual Portion in the Shared Memory display, which shows the value of SHMVIRTSIZE. Or you can execute the command onstat -g seg(as the user <sid>adm/informixor with transaction ST04), as in the following example:

Segment Summary:
(resident segments are not locked)
id	key	addr	size	ovhd	class	blkused	blkfree
778	1381386243	c0651000	679936	616	M	80	3
776	1381386241	c072d000	46792704	1584	R	5708	4
777	1381386242	c33cd000	8192000	732	V	1000	0
2331	1381386244	d5fea000	8388608	732	V	1020	4
2844	1381386245	d67ea000	8388608	732	V	536	488
Total	-	-	72441856	-	-	8344	499

SHMVIRTSIZEto a value which can be determined as follows in a production system:

The dynamic allocation of new shared memory segments is a time-consuming operation, which should be avoided in online operation for reasons of performance.
Various operating systems have a limit on the available shared memory segments.
For different operating systems, there are significant performance losses if more than two shared memory segments are used.

You can use the

Set the parameter

Take the sum of the size of the individual virtual shared memory segments (that is, the rows with the value

This example of how to set

Vin the column class) and set the parameter SHMVIRTSIZEto a value that is larger than the determined value. We recommend you to add 10% to the determined value. SHMVIRTSIZEis taken from the onstat –g segexample above:

SHMVIRTSIZE

= 27,000 KB

= ((8192000 + 8388608 + 8388608) / 1024) x 1.1

Oracle Database List of Books(11g Release 2)

shanyiwan@live.com() — Sun, 09 Oct 2011 14:48:00 GMT

Title	HTML	Search	Index	PDF
2 Day + .NET Developer's Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
2 Day + Application Express Developer's Guide	Contents	/ Search	/ Index	/ PDF
2 Day + Data Replication and Integration Guide	Contents	/ Search	/ Index	/ PDF
2 Day + Data Warehousing Guide	Contents	/ Search	/ Index	/ PDF
2 Day + Java Developer's Guide	Contents	/ Search	/ Index	/ PDF
2 Day + Performance Tuning Guide	Contents	/ Search	/ Index	/ PDF
2 Day + PHP Developer's Guide	Contents	/ Search	/ Index	/ PDF
2 Day + Real Application Clusters Guide	Contents	/ Search	/ Index	/ PDF
2 Day + Security Guide	Contents	/ Search	/ Index	/ PDF
2 Day DBA	Contents	/ Search	/ Index	/ PDF
2 Day Developer's Guide	Contents	/ Search	/ Index	/ PDF
Administrator's Guide	Contents	/ Search	/ Index	/ PDF
Administrator's Reference for Linux and UNIX-Based Operating Systems	Contents	/ Search	/ Index	/ PDF
Advanced Application Developer's Guide	Contents	/ Search	/ Index	/ PDF
Advanced Replication	Contents	/ Search	/ Index	/ PDF
Advanced Replication Management API Reference	Contents	/ Search	/ Index	/ PDF
Advanced Security Administrator's Guide	Contents	/ Search	/ Index	/ PDF
Application Express Administration Guide	Contents	/ Search	/ Index	/ PDF
Application Express Advanced Tutorials	Contents	/ Search	/	/ PDF
Application Express API Reference	Contents	/ Search	/	/ PDF
Application Express Application Builder User's Guide	Contents	/ Search	/ Index	/ PDF
Application Express Application Migration Guide	Contents	/ Search	/ Index	/ PDF
Application Express Installation Guide	Contents	/ Search	/ Index	/ PDF
Application Express Release Notes	Contents	/ Search	/	/ PDF
Application Express SQL Workshop and Utilities Guide	Contents	/ Search	/ Index	/ PDF
Automatic Storage Management Administrator's Guide	Contents	/ Search	/ Index	/ PDF
Backup and Recovery Reference	Contents	/ Search	/ Index	/ PDF
Backup and Recovery User's Guide	Contents	/ Search	/ Index	/ PDF
Clusterware Administration and Deployment Guide	Contents	/ Search	/ Index	/ PDF
COM Automation Feature Developer's Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
Communications Data Model Implementation and Operations Guide	Contents	/ Search	/ Index	/ PDF
Communications Data Model Installation Guide	Contents	/ Search	/ Index	/ PDF
Communications Data Model Reference	Contents	/ Search	/ Index	/ PDF
Communications Data Model Release Notes	Contents	/ Search	/	/ PDF
Concepts	Contents	/ Search	/ Index	/ PDF
Data Cartridge Developer's Guide	Contents	/ Search	/ Index	/ PDF
Data Guard Broker	Contents	/ Search	/ Index	/ PDF
Data Guard Concepts and Administration	Contents	/ Search	/ Index	/ PDF
Data Mining Administrator's Guide	Contents	/ Search	/ Index	/ PDF
Data Mining API Reference (Virtual Book)	Contents	/ Search	/	/
Data Mining Application Developer's Guide	Contents	/ Search	/ Index	/ PDF
Data Mining Concepts	Contents	/ Search	/ Index	/ PDF
Data Mining Java API Reference (Javadoc)	Contents	/ Search	/	/
Data Provider for .NET Developer's Guide	Contents	/ Search	/ Index	/ PDF
Data Provider for .NET Oracle TimesTen In-Memory Database Support User's Guide for Microsoft Windows	Contents	/ Search	/	/ PDF
Data Warehousing Guide	Contents	/ Search	/ Index	/ PDF
Database Client Installation Guide for HP-UX	Contents	/ Search	/ Index	/ PDF
Database Client Installation Guide for IBM AIX on POWER Systems (64-Bit)	Contents	/ Search	/ Index	/ PDF
Database Client Installation Guide for Linux	Contents	/ Search	/ Index	/ PDF
Database Client Installation Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
Database Client Installation Guide for Oracle Solaris	Contents	/ Search	/ Index	/ PDF
Database Client Quick Installation Guide for HP-UX Itanium	Contents	/ Search	/	/ PDF
Database Client Quick Installation Guide for HP-UX PA-RISC (64-Bit)	Contents	/ Search	/	/ PDF
Database Client Quick Installation Guide for IBM AIX on POWER Systems (64-Bit)	Contents	/ Search	/	/ PDF
Database Client Quick Installation Guide for IBM: Linux on System z	Contents	/ Search	/	/ PDF
Database Client Quick Installation Guide for Linux x86	Contents	/ Search	/	/ PDF
Database Client Quick Installation Guide for Linux x86-64	Contents	/ Search	/	/ PDF
Database Client Quick Installation Guide for Microsoft Windows (32-Bit)	Contents	/ Search	/	/ PDF
Database Client Quick Installation Guide for Microsoft Windows x64 (64-Bit)	Contents	/ Search	/	/ PDF
Database Client Quick Installation Guide for Oracle Solaris on SPARC (64-Bit)	Contents	/ Search	/	/ PDF
Database Client Quick Installation Guide for Oracle Solaris on x86-64 (64-Bit)	Contents	/ Search	/	/ PDF
Database Examples Installation Guide	Contents	/ Search	/	/ PDF
Database Gateway for APPC Installation and Configuration Guide for AIX 5L Based Systems (64-Bit), HP-UX Itanium, Solaris Operating System (SPARC 64-Bit), Linux x86, and Linux x86-64	Contents	/ Search	/ Index	/ PDF
Database Gateway for APPC Installation and Configuration Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
Database Gateway for IMS, VSAM, and Adabas Installation and Configuration Guide for AIX 5L Based Systems (64-Bit), HP-UX Itanium, Solaris Operating System (SPARC 64-Bit), Linux x86, and Linux x86-64	Contents	/ Search	/ Index	/ PDF
Database Gateway for IMS, VSAM, and Adabas Installation and Configuration Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
Database Gateway for WebSphere MQ Installation and User's Guide	Contents	/ Search	/ Index	/ PDF
Database Gateway Installation and Configuration Guide for AIX 5L Based Systems (64-Bit), HP-UX Itanium, Solaris Operating System (SPARC 64-Bit), Linux x86, and Linux x86-64	Contents	/ Search	/ Index	/ PDF
Database Gateway Installation and Configuration Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
Database Installation Guide for HP-UX	Contents	/ Search	/ Index	/ PDF
Database Installation Guide for IBM AIX on POWER Systems (64-Bit)	Contents	/ Search	/ Index	/ PDF
Database Installation Guide for Linux	Contents	/ Search	/ Index	/ PDF
Database Installation Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
Database Installation Guide for Oracle Solaris	Contents	/ Search	/ Index	/ PDF
Database Quick Installation Guide for HP-UX Itanium	Contents	/ Search	/	/ PDF
Database Quick Installation Guide for HP-UX PA-RISC (64-Bit)	Contents	/ Search	/	/ PDF
Database Quick Installation Guide for IBM AIX on POWER Systems (64-Bit)	Contents	/ Search	/	/ PDF
Database Quick Installation Guide for IBM: Linux on System z	Contents	/ Search	/	/ PDF
Database Quick Installation Guide for Linux x86	Contents	/ Search	/	/ PDF
Database Quick Installation Guide for Linux x86-64	Contents	/ Search	/	/ PDF
Database Quick Installation Guide for Microsoft Windows (32-Bit)	Contents	/ Search	/	/ PDF
Database Quick Installation Guide for Microsoft Windows x64 (64-Bit)	Contents	/ Search	/	/ PDF
Database Quick Installation Guide for Oracle Solaris on SPARC (64-Bit)	Contents	/ Search	/	/ PDF
Database Quick Installation Guide for Oracle Solaris on x86-64 (64-Bit)	Contents	/ Search	/	/ PDF
Database Vault Administrator's Guide	Contents	/ Search	/ Index	/ PDF
Enterprise Manager System Monitoring Plug-in for Oracle TimesTen In-Memory Database Installation Guide	Contents	/ Search	/	/ PDF
Enterprise Manager System Monitoring Plug-in for Oracle TimesTen In-Memory Database Release Notes	Contents	/ Search	/	/ PDF
Enterprise User Security Administrator's Guide	Contents	/ Search	/ Index	/ PDF
Error Messages	Contents	/ Search	/ Index	/
Extensions for .NET Developer's Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
Gateway for Adabas User's Guide	Contents	/ Search	/ Index	/ PDF
Gateway for APPC User's Guide	Contents	/ Search	/ Index	/ PDF
Gateway for DRDA User's Guide	Contents	/ Search	/ Index	/ PDF
Gateway for IMS User's Guide	Contents	/ Search	/ Index	/ PDF
Gateway for Informix User's Guide	Contents	/ Search	/ Index	/ PDF
Gateway for ODBC User's Guide	Contents	/ Search	/ Index	/ PDF
Gateway for SQL Server User's Guide	Contents	/ Search	/ Index	/ PDF
Gateway for Sybase User's Guide	Contents	/ Search	/ Index	/ PDF
Gateway for Teradata User's Guide	Contents	/ Search	/ Index	/ PDF
Gateway for VSAM User's Guide	Contents	/ Search	/ Index	/ PDF
Globalization Development Kit Java API Reference (Javadoc)	Contents	/ Search	/	/
Globalization Support Guide	Contents	/ Search	/ Index	/ PDF
Grid Infrastructure for HP-UX	Contents	/ Search	/ Index	/ PDF
Grid Infrastructure Installation Guide for IBM AIX on POWER Systems (64-Bit)	Contents	/ Search	/ Index	/ PDF
Grid Infrastructure Installation Guide for Linux	Contents	/ Search	/ Index	/ PDF
Grid Infrastructure Installation Guide for Microsoft Windows x64 (64-Bit)	Contents	/ Search	/ Index	/ PDF
Grid Infrastructure Installation Guide for Solaris Operating System	Contents	/ Search	/ Index	/ PDF
Heterogeneous Connectivity User's Guide	Contents	/ Search	/ Index	/ PDF
High Availability Best Practices	Contents	/ Search	/ Index	/ PDF
High Availability Overview	Contents	/ Search	/ Index	/ PDF
In-Memory Database Cache Introduction	Contents	/ Search	/ Index	/ PDF
In-Memory Database Cache User's Guide	Contents	/ Search	/ Index	/ PDF
interMedia -> renamed to Oracle Multimedia	Contents	/ Search	/	/
Java Developer's Guide	Contents	/ Search	/ Index	/ PDF
JDBC Developer's Guide	Contents	/ Search	/ Index	/ PDF
JDBC Java API Reference (Javadoc)	Contents	/ Search	/	/
JPublisher User's Guide	Contents	/ Search	/ Index	/ PDF
Label Security Administrator's Guide	Contents	/ Search	/ Index	/ PDF
Licensing Information	Contents	/	/ Index	/ PDF
Master Glossary	Contents	/	/	/
Master Index	Contents	/	/	/
Multimedia DICOM Developer's Guide	Contents	/ Search	/ Index	/ PDF
Multimedia DICOM Java API Reference (Javadoc)	Contents	/ Search	/	/
Multimedia Java API Reference (Javadoc)	Contents	/ Search	/	/
Multimedia Mid-Tier Java API Reference (Javadoc)	Contents	/ Search	/	/
Multimedia Reference	Contents	/ Search	/ Index	/ PDF
Multimedia Servlets and JSP Java API Reference (Javadoc)	Contents	/ Search	/	/
Multimedia User's Guide	Contents	/ Search	/ Index	/ PDF
Net Services Administrator's Guide	Contents	/ Search	/ Index	/ PDF
Net Services Reference	Contents	/ Search	/ Index	/ PDF
New Features Guide	Contents	/ Search	/	/ PDF
Object-Relational Developer's Guide	Contents	/ Search	/ Index	/ PDF
OLAP Customizing Analytic Workspace Manager	Contents	/ Search	/ Index	/ PDF
OLAP DML Reference	Contents	/ Search	/ Index	/ PDF
OLAP Expression Syntax Reference	Contents	/ Search	/	/ PDF
OLAP Java API Developer's Guide	Contents	/ Search	/ Index	/ PDF
OLAP Java API Reference (Javadoc)	Contents	/ Search	/	/
OLAP User's Guide	Contents	/ Search	/ Index	/ PDF
Oracle C++ Call Interface Programmer's Guide	Contents	/ Search	/ Index	/ PDF
Oracle Call Interface Programmer's Guide	Contents	/ Search	/ Index	/ PDF
Oracle Connect for IMS, VSAM, and Adabas Gateways Installation and Configuration Guide for IBM z/OS	Contents	/ Search	/ Index	/ PDF
Oracle Objects for OLE Developer's Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
Performance Tuning Guide	Contents	/ Search	/ Index	/ PDF
PL/SQL Language Reference	Contents	/ Search	/ Index	/ PDF
PL/SQL Packages and Types Reference	Contents	/ Search	/ Index	/ PDF
Platform Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
Pro*C/C++ Programmer's Guide	Contents	/ Search	/ Index	/ PDF
Pro*COBOL Programmer's Guide	Contents	/ Search	/ Index	/ PDF
Pro*FORTRAN Supplement to the Oracle Precompilers Guide	Contents	/ Search	/	/ PDF
Pro*PL/1 Supplement to the Oracle Precompilers Guide	Contents	/ Search	/ Index	/ PDF
Programmer's Guide to the Oracle Precompilers	Contents	/ Search	/ Index	/ PDF
Provider for OLE DB Developer's Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
Providers for ASP.NET Developer's Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
Quality of Service Management User's Guide	Contents	/ Search	/ Index	/ PDF
RAC FAN Events Java API Reference (Javadoc)	Contents	/ Search	/	/
Readme	Contents	/ Search	/	/ PDF
Real Application Clusters Administration and Deployment Guide	Contents	/ Search	/ Index	/ PDF
Real Application Clusters Installation Guide for Linux and UNIX	Contents	/ Search	/ Index	/ PDF
Real Application Clusters Installation Guide for Microsoft Windows x64 (64-Bit)	Contents	/ Search	/ Index	/ PDF
Real Application Testing User's Guide	Contents	/ Search	/ Index	/ PDF
Reference	Contents	/ Search	/ Index	/ PDF
Release Notes for HP-UX	Contents	/ Search	/	/ PDF
Release Notes for IBM AIX on POWER Systems (64-Bit)	Contents	/ Search	/	/ PDF
Release Notes for Linux	Contents	/ Search	/	/ PDF
Release Notes for Microsoft Windows	Contents	/ Search	/	/ PDF
Release Notes for Oracle Solaris	Contents	/ Search	/	/ PDF
Rules Manager and Expression Filter Developer's Guide	Contents	/ Search	/ Index	/ PDF
Sample Schemas	Contents	/ Search	/ Index	/ PDF
SecureFiles and Large Objects Developer's Guide	Contents	/ Search	/ Index	/ PDF
Security Guide	Contents	/ Search	/ Index	/ PDF
Semantic Technologies Developer's Guide	Contents	/ Search	/ Index	/ PDF
Services for Microsoft Transaction Server Developer's Guide for Microsoft Windows	Contents	/ Search	/ Index	/ PDF
Spatial Developer's Guide	Contents	/ Search	/ Index	/ PDF
Spatial GeoRaster Developer's Guide	Contents	/ Search	/ Index	/ PDF
Spatial Java API Reference (Javadoc)	Contents	/ Search	/	/
Spatial Topology and Network Data Models Developer's Guide	Contents	/ Search	/ Index	/ PDF
SQL Developer User's Guide	Contents	/ Search	/ Index	/ PDF
SQL Language Quick Reference	Contents	/ Search	/ Index	/ PDF
SQL Language Reference	Contents	/ Search	/ Index	/ PDF
SQL*Module for Ada Programmer's Guide	Contents	/ Search	/ Index	/ PDF
SQL*Plus Quick Reference	Contents	/ Search	/	/ PDF
SQL*Plus Release Notes	Contents	/ Search	/	/ PDF
SQL*Plus User's Guide and Reference	Contents	/ Search	/ Index	/ PDF
SQLJ Developer's Guide	Contents	/ Search	/ Index	/ PDF
Streams Advanced Queuing Java API Reference (Javadoc)	Contents	/ Search	/	/
Streams Advanced Queuing User's Guide	Contents	/ Search	/ Index	/ PDF
Streams Concepts and Administration	Contents	/ Search	/ Index	/ PDF
Streams Extended Examples	Contents	/ Search	/ Index	/
Streams Replication Administrator's Guide	Contents	/ Search	/ Index	/ PDF
Text Application Developer's Guide	Contents	/ Search	/ Index	/ PDF
Text Reference	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database C Developer's Guide	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database Error Messages and SNMP Traps	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database Installation Guide	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database Java Developer's Guide	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database JDBC Extensions Java API Reference (Javadoc)	Contents	/ Search	/	/
TimesTen In-Memory Database JMS/XLA Java API Reference (Javadoc)	Contents	/ Search	/	/
TimesTen In-Memory Database Operations Guide	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database PL/SQL Developer's Guide	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database PL/SQL Packages Reference	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database Reference	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database Release Notes	Contents	/ Search	/	/ PDF
TimesTen In-Memory Database SQL Reference	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database System Tables and Limits Reference	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database TimesTen to TimesTen Replication Guide	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database Troubleshooting Procedures Guide	Contents	/ Search	/ Index	/ PDF
TimesTen In-Memory Database TTClasses Guide	Contents	/ Search	/ Index	/ PDF
Universal Connection Pool for JDBC Developer's Guide	Contents	/ Search	/ Index	/ PDF
Universal Connection Pool for JDBC Java API Reference (Javadoc)	Contents	/ Search	/	/
Universal Installer and OPatch User's Guide for Windows and UNIX	Contents	/ Search	/ Index	/ PDF
Upgrade Guide	Contents	/ Search	/ Index	/ PDF
Using the OLAP APIs (Virtual Book)	Contents	/ Search	/	/
Utilities	Contents	/ Search	/ Index	/ PDF
VLDB and Partitioning Guide	Contents	/ Search	/ Index	/ PDF
Warehouse Builder API and Scripting Reference	Contents	/ Search	/	/ PDF
Warehouse Builder Concepts	Contents	/ Search	/ Index	/ PDF
Warehouse Builder Data Modeling, ETL, and Data Quality Guide	Contents	/ Search	/ Index	/ PDF
Warehouse Builder Installation and Administration Guide	Contents	/ Search	/ Index	/ PDF
Warehouse Builder OMB*Plus Command Reference	Contents	/ Search	/	/
Warehouse Builder Release Notes	Contents	/ Search	/	/ PDF
Warehouse Builder Sources and Targets Guide	Contents	/ Search	/ Index	/ PDF
Workspace Manager Developer's Guide	Contents	/ Search	/ Index	/ PDF
XML C API Reference	Contents	/ Search	/ Index	/ PDF
XML C++ API Reference	Contents	/ Search	/ Index	/ PDF
XML DB Developer's Guide	Contents	/ Search	/ Index	/ PDF
XML Developer's Kit Programmer's Guide	Contents	/ Search	/ Index	/ PDF
XML Java API Reference (Javadoc)	Contents	/ Search	/	/
XStream Guide	Contents	/ Search	/ Index	/ PDF
XStream Java API Reference (Javadoc)	Contents	/ Search	/	/

http://www.oracle.com/pls/db112/docindex?preference=All

SQLite Optimization FAQ

shanyiwan@live.com() — Wed, 28 Sep 2011 03:07:11 GMT

SQLite Optimization FAQ

Jim Lyon (jplyon@attglobal.net)
10-Sep-2003
Compiled from sqlite docs, source, and yahoo postings

This document is current for SQLite 2.8.6

1. Introduction

1.1. About this FAQ

This FAQ is an incomplete summary of my experiences with speed-optimizing an application that uses the SQLite library. It will be of most use to people who are already familiar with using SQLite. Feel free to send any questions and comments.

It is released under the terms of the SQLite license :)

** The author disclaims copyright to this material.
** In place of a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.

1.2. Table of Contents

Introduction
PRAGMA settings
Transactions
Indexes
Optimizing queries
Moving code into SQL
User functions
Callback functions
Reducing database file size
Reducing database load time
Reducing SQL query parse time
Hacking the source
Using the btree interface
Multiple threads
Operating system issues

Appendices

Timing considerations

1.3. Overview

SQLite is capable of being extremely fast. If you are seeing speeds slower than other DB systems such as MySQL or PostGres, then you are not utilizing SQLite to its full potential. Optimizing for speed appears to be the second priority of D. Richard Hipp, the author and maintainer of SQLite. The first is data integrity and verifiability.

The first thing you should know is that most of the time spent by SQLite (and most other DB systems) is on disk access, which is slow compared to memory operations. So the key to optimizing SQLite is to minimize the amount of disk access required. This requires some understanding of when SQLite is accessing information on disk, and why. Because operating systems generally cache open disk files in memory, a carefully tuned use of SQLite can approach the speed of a pure in-memory database.

If you are new to optimization, you must know that the only reliable way to optimize for speed is to measure where time is being spent. Unless you are already familiar with doing this for SQLite, you will often guess wrong. Use quantitative tests on representative examples whenever possible. Unfortunately, reproducibly measuring performance on an application that does disk access isn't trivial. See the appendix Timing considerations.

1.4. Quick start

There are a few things you can do that are one-liners or just require recompiling the source. These often make a big difference, however.

(in rough order of effectiveness)

Use an in-memory database
Use BEGIN TRANSACTION and END TRANSACTION
Use indexes
Use PRAGMA cache_size
Use PRAGMA synchronous=OFF
Compact the database
Replace the memory allocation library
Use PRAGMA count_changes=OFF

2. `PRAGMA` settings

2.1 `PRAGMA cache_size`

An SQLite database is split into a btree of "pages" which are 1K in size by default. SQLite loads these pages into memory when working with the database, with some additional structures to keep track of them. If a query only involves cached pages, it can execute much faster since no disk access is required.

The cache_size value is the maximum number of btree pages that the SQLite back end will keep in memory at one time. The default (through version 2.8.6) is 2000 (MAX_PAGES in "sqliteInt.h"). This setting can be different from the default_cache_size value which is stored in the database file, and read in by SQLite on loading the database.

The standard size of a database page is 1KB (on disk), so a modern system can generally keep a large number of pages in memory at once. The page cache isn't allocated ahead of time, so there is no overhead if you set a value larger than the number of pages read. In addition, the operating system will often make better decisions about paging than you can.

Since this setting is dynamic, it can be raised to a large value to optimize a specific set of queries, and then dropped back to a lower value afterwards.

2.2 `PRAGMA synchronous`

The Boolean synchronous value controls whether or not the library will wait for disk writes to be fully written to disk before continuing. This setting can be different from the default_synchronous value loaded from the database. In typical use the library may spend a lot of time just waiting on the file system. Setting "PRAGMA synchronous=OFF" can make a major speed difference.

2.3 `PRAGMA count_changes`

When the count_changes setting is ON, the callback function is invoked once for each DELETE, INSERT, or UPDATE operation. The argument is the number of rows that were changed. If you don't use this feature, there is a small speed increase from turning this off.

This pragma may be removed from future versions of SQLite. Consider using the sqlite_changes() API function instead.

2.4 `PRAGMA temp_store`

(This PRAGMA is not fully implemented, as of 2.8.6.)

The temp_store values specifies the type of database back-end to use for temporary files. The choices are DEFAULT (0), FILE (1), and MEMORY (2). The use of a memory database for temporary tables can produce signifigant savings. DEFAULT specifies the compiled-in default, which is FILE unless the source has been modified.

This pragma will have no effect if the sqlite library has support for in-memory databases turned off using symbol SQLITE_OMIT_INMEMORYDB.

There is a corresponding setting which is stored in and loaded from the database. It is set with PRAGMA default_temp_store.

3. Transactions

3.1. Using `BEGIN TRANSACTION` and `END TRANSACTION`

Unless already in a transaction, each SQL statement has a new transaction started for it. This is very expensive, since it requires reopening, writing to, and closing the journal file for each statement. This can be avoided by wrapping sequences of SQL statements with BEGIN TRANSACTION; and END TRANSACTION; statements. This speedup is also obtained for statements which don't alter the database.

The keyword COMMIT is a synonym for END TRANSACTION.

It is legal to begin and end a transaction in different calls to sqlite_exec():

sqlite_exec(sqlitedb, "BEGIN;",...);
sqlite_exec(sqlitedb, query1,...);
...
sqlite_exec(sqlitedb, queryN,...);
sqlite_exec(sqlitedb, "END;",...);

Note that SQLite obtains a write lock on the database file when a transaction is open, so when accessing the same database on multiple threads you have to be careful not to starve some of the threads.3.2. Handling failed transactions

Although SQLite parses named transactions, the names are ignored. SQLite does not support nested transactions. If you split the SQL statements in a transaction over several sqlite_exec() calls, you must be prepared to handle failure in each of these calls. If a failure occurs SQLite will return a non-zero value. After that, SQLite will revert to the default behavior of giving each SQL statement its own transaction until a new transaction is started.

3.3. Turning off journaling

SQLite uses a separate "journal" file to provide the ability to roll back a transaction in case of a failure. This records all changes made to the database during a transaction. This adds the overhead of additional disk access, which is expensive compared to the speed of SQLite in-memory operations.

The jounal file is no longer opened for operations that don't alter the database. The journal file is not created at all for read-only database files. It is also not used for operations on TEMPORARY tables, even if the statements change the contents or schema of a temporary table.

For a writable database, it is possible to turn off journaling at the C source level. This should only be done as a last resort, but there are some applications such as games where speed can be more important than data integrity. In many cases you can achieve the same effect by using a TEMPORARY table.

4. Indexes

Indexes maintain a sorting order on a column or set of columns in a table. This allows selecting a range of values without having to scan the entire table on disk. Indexes can make a huge difference in speed when a query does not need to scan the entire table.

Indexes are implemented by creating a separate index table which maps a key created from the column[s] to a row index in the indexed table. So there is an additional size overhead, which is usually worth the cost.

4.1. Creating indexes

SQLite automatically creates an index for every UNIQUE column, including PRIMARY KEY columns, in a CREATE TABLE statement.

CREATE TABLE mytable (
  a TEXT PRIMARY KEY,  -- index created
  b INTEGER,
  c TEXT UNIQUE        -- index created
);

You may also explicitly create an index on an existing table using the CREATE INDEX statement. This allows dropping the index later.

4.2. Indexes on multiple columns

4.3. Verifying that an index is being used correctly

4.4. Ordering queries to correctly use an index

4.5. Searching for prefixes

4.6. Searching for suffixes

4.7. The rowid of a column is an integer index

4.8. Storing dates so they sort correctly

Dates can be stored as integers to get them to store correctly, but if they are strings, you will need a format similar to "YYYY-MM-DD HH:MM:SS.XXX Day", or some subsequence.

4.9. Alternative to index -- use computed hash value as a key for fast lookup

4.9. When not to use an index

When you are entering data into a new table which has indexes on it, this can be much slower than entering all the data first, and then creating the index.

If your queries must do a full table scan anyway, such as when doing LIKE matching, an index will not be of benefit.

5. Optimizing queries

5.1. The `EXPLAIN` command

The EXPLAIN command displays the VDBE opcodes output by the parser, and can be used to determine how the query will actually be executed. The opcodes are documented in the online help, and in the "vdbe.c" source.

In the command-line sqlite utility, the .explain command changes the output format to make the EXPLAIN output easier to view.

5.2. Temporary tables

Use to manipulate code generated

(see Message 5 in digest 337 from sam_saffron)

Subqueries are handled internally using temporary tables. By splitting a subquery out of the main query, a complex query can be broken up to change the code output by the parser.

Save intermediate results in temporary tables

5.3. Order subqueries so smaller results are returned first

If a query filters by multiple criteria, move the test to the front (left) which has the fewest results.
A test which uses an index generally shouldn't be moved behind (to the right of) one which does.

5.4. Use `LIMIT` and `OFFSET`

Sometimes you only need to check to see if there is at least one record that matches some criterion. SQLite doesn't implement the EXISTS keyword, but you can obtain the same result with LIMIT 1.

5.5. Expressions

5.5.1 Replace `GLOB` and `LIKE` when possible

The GLOB and LIKE operators are expensive in SQLite because they can't make use of an index. One reason is that these are implemented by user functions which can be overridden, so the parser has no way of knowing how they might behave in that case. This forces a full scan of the table for the column being matched against, even if that column has an index.

A GLOB or LIKE expression that begins with a literal string and ends with the wildcard * (or % for LIKE) can be replaced by a pair of inequalities. This is possible because the wildcard % occurs at the end of the string, so the resulting string will always sort after the prefix, and before the string of the same length that follows the prefix. These inequality operators are able to make use of an index if x below is a column.

Example: The expression (x GLOB 'abc*') can be replaced by (x >= 'abc' AND x < 'abd').

Note: The LIKE operator is case sensitive. This means that the above won't work for LIKE unless the strings involved are all either upper- or lower-case.

If a string of arbitrary characters of fixed length is to be matched, this can be replaced by the SQL length() function.

Example: The expression (x GLOB '???') can be replaced by (length(x) == 3).

If a * or % wildcard occurs in the middle of a string, or a complex pattern appears at the end, we can still use the above techniques to create a filter test which is done before the more expensive pattern test.

Example: The expression (x GLOB 'abc*jkl*') can be replaced by (x > 'abc' AND x < 'abd' AND x GLOB 'abc*jkl*').
Example: The expression (x LIKE 'abc_') can be replaced by (x > 'abc' AND x < 'abd' AND length(x) == 4).

5.5.2 Don't use `length()` on very long strings

The length() function scans the entire string, and forces all of the data for that column entry to be loaded. For strings which exceed the page size (1K by default), the rest of the data will be split over multiple overflow pages, which must be loaded one at a time, in sequence. If you are testing for a short length, there are methods that only load the first page of data.

Test against the empty string directly.

Example: Replace (WHERE length(str)=0) with (WHERE str='').

Use LIKE and GLOB to test the string against a pattern instead of testing its length, for short lengths.

Example:(WHERE length(str)==3) can be replaced by (WHERE str GLOB '???') or (WHERE str LIKE '___').

For longer strings which will be read many times, it may be worthwhile to add a new table column which records the length. This length can be updated using a TRIGGER if desired. If it isn't reasonable to store this in the database, it can be kept in a temporary table which can be merged with the original table using a JOIN. Remember, the length() function can't make use of an index, so an expression like (length(x) < 45) will be forced to scan the entire table. If you are going to have to scan the entire table and compute this for every row at least once, you may as well just record the information and reuse it.

5.5.3 Disable inefficent indexes

An index can keep a query from loading unnecessary rows of a table. There is a cost to using an index; its table has to be loaded from the database, and extra code has to be executed by the VDBE to use it. This cost is usually outweighed by the benefits of not having to do a full table scan. However, there are a number of cases in which an index can slow a query down:

If the table is small, loading the extra index table may take more time.
If the answers in a limited query are very close to the start.
If most or all of the table would need to be loaded anyway. (e.g. when an expression contains a LIKE operator)
If an index provides little benefit, loading the index can force normal table pages out of the cache, slowing things further.
If there is a choice of indexes, the query optimizer may make a bad choice.

An existing index can be bypassed by rewriting the query so that the index isn't used. This can be useful for reasons listed above. The query optimizer (in "where.c") looks for expressions of the form "column-namebinopexpr" or "exprbinopcolumn-name", where binop is one of <, <=, ==, =, >=, >. Changing the query to not fit this pattern will prevent an index on the column from being used.

Example:"WHERE x=5" can be changed to "WHERE x+0=5" or "WHERE +x=5" to bypass the index.

5.5.4 Use `IN` instead of `OR`

An expression like "x='A' OR x='B'" will not make use of an index on column x. Instead use "x IN ('A', 'B')".

6. Moving code into SQL

There is overhead in crossing the "barrier" between the SQLite library and the application using it. Memory has to be allocated and results copied, often several times. If you can move C code that handles results "inside" SQLite by using callback functions, user functions, and triggers, there can be substantial speed savings.

The first example I encountered was when I needed to rename all of the data in one column of a table in a complicated way. I originally coded this by fetching the data, renaming it, and putting it back into the database. There are several problems with this, including transaction overhead. By rewriting the renaming function as a complex SQL expression, I was able to use a single UPDATE function, and obtained a factor of 10 increase. The fact that the SQL expression was interpreted didn't compare to the savings from the reduced overhead of only having one query to execute, and no data that had to be returned. I could also have implemented this with a user fuction to "compile" the SQL expression, but at that point I didn't need any more speed.

6.1. Converting code into SQL expressions

6.2. Moving code data into SQLite database

6.3. Wrapping code with user functions

6.4. Converting multiple queries into a single query with user functions.

This avoids having to explicitly use a transaction wrapper.

This only requires parsing the query once.

6.5. Use TRIGGERs to replace code

7. User functions

7.1. Example user function

7.2. Aggregate functions

8. Callback functions

8.1. Example callback function

9.2. Replacing uses of sqlite_get_table() with callbacks 9.2.1. This fn has an overhead of reallocating the result table for and all row data for every row. For large results sets this allocation can consume signifigant resources, and result in millions of calls to malloc(). This large number of malloc() calls can lead to massive fragmentation of the memory pool. 9.2.2. Instead use a callback to handle each row in turn. 9.2.3. This behaves differently if the query would have failed at some point. 9.2.4. Possible optimizations 9.2.4.1. note that the allocated size will often have a known fixed minimum in terms of the number of rows and columns of the result set. If we alloc to this initial size, or a nice initial seed size, this will prevent many realloc calls. This only requires changing a single line "res.nAlloc = 20;" in sqlite_get_table(). 9.2.4.2. we can allocate memory for an entire row's values, and place all the rows' strings in that memory block. This reduces the the number of malloc() calls proportionally to the number of rows, but eliminates the possible explicit manipulation of the returned strings separately (which is naughty).

9. Reducing database file size

9.1. Compact the database

When information is deleted in the database, and a btree page becomes empty, it isn't removed from the database file, but is instead marked as 'free' for future use. When a new page is needed, SQLite will use one of these free pages before increasing the database size. This results in database fragmentation, where the file size increases beyond the size required to store its data, and the data itself becomes disordered in the file.

Another side effect of a dynamic database is table fragmentation. The pages containing the data of an individual table can become spread over the database file, requiring longer for it to load. This can appreciably slow database speed because of file system behavior. Compacting fixes both of these problems.

The easiest way to remove empty pages is to use the SQLite command VACUUM. This can be done from within SQLite library calls or the sqlite utility.

(C code)
sqlite_exec(db, "VACUUM;", 0, 0);
(sqlite utility)
sqlite> VACUUM;
(shell prompt)
$ echo 'VACUUM;' | sqlite filename
or
$ sqlite filename 'VACUUM;'

Another way to remove the empty pages is to dump the database and recreate it. This can only be done from "outside" the database.

(shell prompt)
$ echo '.dump' | sqlite file1.db > file1.sql
$ cat dump-file | sqlite file2.db
or
$ sqlite file1.db '.dump' > dump-file
$ sqlite file2.db < file1.sql
or
$ sqlite file1.db '.dump' | sqlite file2.db
(DOS / Win prompt)
> echo .dump | sqlite file1.db > file1.sql
> sqlite file2.db < file1.sql
or
> sqlite file1.db .dump > file1.sql
> sqlite file2.db < file1.sql

Afterwards, copy the new file over the old one (or delete the old one before creating the new one, and use the same name).

(shell prompt)
$ cp file2.dbfile1.db
(DOS / Win prompt)
> move file2.dbfile1.db

How many free pages there are in a database can be determined at runtime using the function sqlite_count_free_pages() in message #5 of digest #385. A current version is at freepages.c.

There is a TCL script for determining free pages announced in message #1 of digest #385 by DRH. It is space_used.tcl.

9.2. Computing file sizes

9.3. Recompiling SQLite for different page sizes

9.4. Determining data sizes

The size of the data in the database can be determined using SQL queries:

SELECT length(colname) FROM tablename

SELECT sum( length(colname) ) FROM tablename;

SELECT avg(length(colname)), min(length(colname)), max(length(colname)) FROM tablename;

SELECT sum( min(100, length(colname) * 0.125) ) FROM tablename;

SELECT name FROM SQLITE_MASTER WHERE type = 'table'
UNION SELECT name FROM SQLITE_TEMP_MASTER WHERE type = 'table';

9.5. Compress large data

9.5.1. Compression tools

9.5.2. Encoding binary data

The SQLite binary encoding API

The SQLite library includes functions sqlite_encode_binary() and sqlite_decode_binary() in "encode.c". They can be used to safely encode binary data in a string form suitable for storing in SQLite, and safe for use in SQL queries. These functions are an "addition" to the SQLite library. They are not compiled into the library by default, and are not present in the precompiled binaries distributed on the download page.

The encoding used is very efficient and sharply bounded, and only results in about 2% overhead. This is achieved by first scanning the data to find an infrequently used character which is suitable to use to rotate the data. The resulting data then has special characters escaped. There is detailed documentation of how they work in the source.

(add example here)

Binhex encoding

Binary data can be encoded by converting each character to a sequence of 2 hexadecimal digits in ASCII form. This will result in strings twice as long as the orginal data, but this may not matter if the strings are small, since there is overhead in the database. The resulting strings will contain no apostrophes or 0s, so will be safe to use in SQL statements as well.

/* Convert binary data to binhex encoded data.
** The out[] buffer must be twice the number of bytes to encode.
** "len" is the size of the data in the in[] buffer to encode.
** Return the number of bytes encoded, or -1 on error.
*/
int bin2hex(char *out, const char *in, int len)
{
  int ct = len;
  if (!in || !out || len < 0) return -1;
  /* hexadecimal lookup table */
  static char hex[] = "0123456789ABCDEF";
  while (ct-- > 0)
  {
    *out   = hex[*in >> 4];
    *out++ = hex[*in++ & 0x0F];
  }
  return len;
}
/* Convert binhex encoded data to binary data.
** "len" is the size of the data in the in[] buffer to decode, and must 
** be even. The out[] buffer must be at least half of "len" in size.
** The buffers in[] and out[] can be the same to allow in-place decoding.
** Return the number of bytes encoded, or -1 on error.
*/
int hex2bin(char *out, const char *in, int len)
{
  int ct = len;
  if (!in || !out || len < 0 || len&1) return -1;
  while ((ct-=2) > 0)
  {
    char ch = ((*in >= 'A')? (*in++ - 'A' + 10): (*in++ - '0')) << 4;
    *out++ += ch + ((*in >= 'A')? (*in++ - 'A' + 10): (*in++ - '0'));
  }
  return len;
}

XOR encoding

If you know that your data doesn't contain a specific character, you can exclusive-or each character of the data with the missing character to remove all 0 characters. This encoding can be done in-place, because the encoded data will have the same length. Also, the encoding is symmetric, so the same function is used for decoding.

/* Example of encoding and decoding binary data. */
/* XOR encode a buffer which is missing char "ch".
** If "ch" isn't in the data, no 0s will occur in the encoded data.
** This function is symmetric, and can be used for both encoding and decoding.
** Return the buffer pointer.
*/
char *xorbuf(char *data, int len, char ch)
{
  int ct = len;
  char *d = data;
  if (!data || len < 0) return NULL;
  while (ct-- > 0)
    *d++ ^= ch;
  return data;
}

Encoding 7-bit data

7-bit data can be encoded by toggling the high bit. This can be done with xorbuf() above using encoding character '0x80'. This technique results in data safe to use in SQL statements, since encoding can't produce an apostrophe character.

To obtain encoded data which is still readable, you can selectively remap just the 0 and apostrophe characters. The following function does this encoding and decoding.

/* Encode 7-bit ASCII data into the buffer out[], which must be at least
** "len" bytes in length.  The encoded data will have the same size as 
** the decoded data.  You must append a 0 byte to terminate the string.
** The buffers in[] and out[] may be the same. Call this function again 
** to decode. Return the length encoded, or -1 on error.
*/
int ascii7enc(char *out, const char *in, int len)
{
  int ct = len;
  if (!in || !out || len < 0) return -1;
  while (ct-- > 0)
  {
    char ch = (*in == 0 || *in == '\'' || *in == 0x80 || *in == '\''^0x80)?
     (*in++ ^ 0x80): *in++;
    *out++ = ch;
  }
  return len;
}

Escape-char encoding

MIME base64 encoding

MIME base64 encoding is used to encode binary data for transmission in email messages. It encodes the data into printable ASCII characters, requiring a fixed overhead of roughly 33%. This method isn't recommended unless you have functions to do this easily available. There are techniques above that are either faster or produce smaller encodings. There is a public domain implementation online at fourmilab or freshmeat, which includes full C source, docs, and the RFC 1341 standard, and builds on both Unix and Windows.

9.5.5. Operating in-place on data returned by sqlite_get_table()

9.6. Store large data in external files

This is practical for CD-ROMs, private systems,...

10. Reducing database load time

This covers both loading the database at startup, and loading information from the database during execution.

There are only a few ways to speed up loading:
1) Load less data
2) Make sure data is close together on disk
3) Make sure data doesn't need to be reloaded

Several techniques already covered will speed up loading:

Compact the database
Compress large data
Store large data externally

10.1 Store information externally

If you have a large amount of data in a particular column, you may want to store its information externally in individual files, and store the filename in the column. This is particularly useful with binary data, but it is also useful with large text data.

10.2 Move large columns into another table

If you have a table with a column containing a large amount of (e.g. binary) data, you can split that column into another table which references the original table as a FOREIGN KEY. This will prevent the data from being loaded unless that column is actually needed (used in a query). This also helps keep the rest of the table together in the database file.

10.3 Store compressed data

Compressing even short text data can reduce it by a factor of 2 or more. XML and related markup languages compress well. Usually it takes less time to load compressed data and uncompress it than it does to load the uncompressed data. If the data is in a column that is only occassionally accessed, there are even greater savings.

The helper functions [Fn]ZipString() and [Fn]UnzipString() make this easy to implement, and only require linking in the zlib compression library.

10.4 Add or remove indexes

An index can keep a query from loading unnecessary rows of a table. However, if the table is small or most of it would need to be loaded anyway, the index can slow things down, because now it needs to be loaded as well as the table data, and the index data can force normal table pages out of the cache, slowing things further.

11. Reducing SQL query parse time

11.1. Use the new API fn for reusing a VDBE program

11.2. Combine sequences of like queries using callback functions

12. Hacking the source

12.1. Replace the memory allocation library

The memory allocation is notoriously bad on some systems (e.g. Windows). Replacing the functions malloc(), realloc(), and free() on these systems can have a dramatic effect on performance.

One widely used open source replacement is Hans Boehm's Memory Pool System at freshmeat and Ravenbrook Limited.
A light-weight, public domain implementation is Doug Lea's dlmalloc.
Both have bindings for malloc()/realloc()/free(), so they can be used as drop-in replacements for the standard memory allocation library.

12.2. Replace `sqlite_get_table()`

sqlite_get_table() is entirely contained in "table.c" which is self-contained. It can be modified or replaced entirely.

sqlite_get_table_cb() originally allocates its array (that it returns string ptrs in) to size 20. When it needs more it reallocates by powers of 2 to avoid O(N^2) behavior. Afterwards it reallocates down to the size required by the number of elements. For large numbers of calls with small result sets, it is more efficient to use an application-tuned value for the initial size. This is set in member res.nAlloc in function sqlite_get_table() itself. This one-line change doesn't break anything else.

You can remove the if( res.nAlloc>res.nData ){...} case used to realloc down in size afterwards if you know you will always be freeing the table soon, which is the usual case. This is usually unnecessary because the table will be freed afterwards anyway. This array only holds the pointers to the result data, not the data itself, so if it twice as large as required, this usually doesn't present enough of an overhead to cause problems.

sqlite_get_table_cb() allocates the column strings for each row separately, so if there are 10 columns returned in a result, you get 10 malloc() calls for what are essentially inline strdup() calls (when the strings are not NULL). For large result sets (eg 100,000 rows), this would lead to 10^6 malloc() calls for the data.
A simple change is to allocate memory for the entire result row at once, with all the strings for a row copied contiguously into the memory block. This reduces the number of malloc() and free() calls proportionally to the number of rows, and has no overhead when there is only one column per row. The only thing this breaks is the ability to directly remove strings from the results table, which is not guaranteed anyhow. This only requires changes to sqlite_get_table_cb() and sqlite_free_table().

Note: if you are getting result sets of this size, using sqlite_get_table() only makes sense if you (1) need all the data available at once and (2) only want them if the entire query succeeds. If you don't require this, just using a callback is much more efficient because it bypasses all the copying entirely.

12.3. Compile-time options

12.4. Replace the file library

(Linking in custom file library under Windows for working with memory-mapped files.

13. Using the btree interface

13.1. Simple example

See message #8 in digest #387 for a short example (by Mike Ekim).

14. Multiple threads

14.1. Transactions -- locking the database

14.2. Reading from multiple threads

14.3. Using temporary tables to accumulate modifications

14.4. Implementing your own thread locking functions

15. Operating system issues

15.1. Thread safety

15.2. Paging

15.3. File locking

15.4. Memory handling

15.5. Mutex handling

15.6. Rollbacks

A. Timing considerations

Accurately timing disk-intensive programs such as sqlite is rather hard.
There is variability in results, and both library and operating system paging can hide worst-case times.

A.1. Timing tools

A.1.1 The `speedcompare` script

DRH has uploaded a tcl script to the Hwaci website that compares the speed of two versions of SQLite named "sqlite-base" and "sqlite-test". The script is at speedcompare.tcl. This is the script he uses to judge the speed impact of changes made to the library.

A.1.2 Shell commands

A.1.3 C functions

A.1.4 C profiler

A.2. Accurate timing

A.2.1. Controlling file system variability

A.2.2. Looping tests

Don't just use the same query in loop.
(see message #6 in digest #302)

A.2.3. Btree cache size

The btree cache size set by PRAGMA cache_size has a large affect on speed. You should generally set it to the value you intend to use in your program.

If you want to test the speed of memory-intensive functions, you can set the cache size to a large value to minimize the effect of paging on the timings. After setting the cache size, force the entire table being used to load into memory with a SELECT statement.

A.2.4 Settings stored in the database

Some default PRAGMA settings are stored in the database itself. These need to be controlled for reproducible tests. They can be overridden at runtime with the corresponding non-default PRAGMA. The PRAGMAs are default_cache_size and default_synchronous.

flyinweb's blog - 数据库技术

mysql-mmm Installation Guide

mysql-mmm实现mysql高可用

VS 2010 SP1 and SQL CE

SQL CE – What is it and why should you care?

New Tooling Support for SQL CE in VS 2010 SP1

Download

Walkthrough of Two Scenarios

Walkthrough 1: Create a SQL CE Database, Create EF Model Classes, Edit the Data with a GridView

Walkthrough 2: Using EF Code-First with SQL CE and ASP.NET MVC 3

Summary

Connection strings for SQL Server Compact Edition

.NET Compact Framework Data Provider for SQL Server Mobile

Standard

How to specify the location of the SDF file

Standard

Specifying the maximum database size

Specifying the maximum buffer size

Encryption enabled

Exclusive access

Read only access

Exclusive but shared for reading

Specifying the maximum temp file size

Microsoft.SQLSERVER.CE.OLEDB.3.5

Standard

Microsoft.SQLSERVER.MOBILE.OLEDB.3.0

Standard

How to specify the location of the SDF file

Specifying the maximum database size

Specifying the maximum buffer size

Encryption enabled

Specifying the maximum temp file size

Specifying location of temp file

Supplying the database password

.NET Framework Data Provider for OLE DB

Bridging to Microsoft.SQLSERVER.CE.OLEDB.3.5

Binlog has bad magic number

IDS 10.0 supports multiple MSC VPs

Configuring Informix Virtual Processors

informix 自动备份逻辑日志时出错

Oracle Database List of Books(11g Release 2)

SQLite Optimization FAQ

SQLite Optimization FAQ

1. Introduction

1.1. About this FAQ

1.2. Table of Contents

1.3. Overview

1.4. Quick start

2. PRAGMA settings

2.1 PRAGMA cache_size

2.2 PRAGMA synchronous

2.3 PRAGMA count_changes

2.4 PRAGMA temp_store

3. Transactions

3.1. Using BEGIN TRANSACTION and END TRANSACTION

3.3. Turning off journaling

4. Indexes

4.1. Creating indexes

4.2. Indexes on multiple columns

4.3. Verifying that an index is being used correctly

4.4. Ordering queries to correctly use an index

4.5. Searching for prefixes

4.6. Searching for suffixes

4.7. The rowid of a column is an integer index

4.8. Storing dates so they sort correctly

4.9. Alternative to index -- use computed hash value as a key for fast lookup

4.9. When not to use an index

5. Optimizing queries

5.1. The EXPLAIN command

5.2. Temporary tables

Use to manipulate code generated

Save intermediate results in temporary tables

5.3. Order subqueries so smaller results are returned first

5.4. Use LIMIT and OFFSET

5.5. Expressions

5.5.1 Replace GLOB and LIKE when possible

5.5.2 Don't use length() on very long strings

5.5.3 Disable inefficent indexes

5.5.4 Use IN instead of OR

6. Moving code into SQL

2. `PRAGMA` settings

2.1 `PRAGMA cache_size`

2.2 `PRAGMA synchronous`

2.3 `PRAGMA count_changes`

2.4 `PRAGMA temp_store`

3.1. Using `BEGIN TRANSACTION` and `END TRANSACTION`

5.1. The `EXPLAIN` command

5.4. Use `LIMIT` and `OFFSET`

5.5.1 Replace `GLOB` and `LIKE` when possible

5.5.2 Don't use `length()` on very long strings

5.5.4 Use `IN` instead of `OR`

12.2. Replace `sqlite_get_table()`