Chinaunix

标题: 试了一下php连接pgsql,不知道为什么速度好慢 [打印本页]

作者: cdcchen 时间: 2007-03-21 13:10
标题: 试了一下php连接pgsql,不知道为什么速度好慢
本机192.168.1.108，pgsql地址192.168.0.2中间跨着routeros做的路由，用php插入简单的数据到一个表里，每次插入5000条，有时候还执行不完就停了，直接插入20000条，执行不完就停止了，用select cout(*)监测了一下，平均一秒钟插入50-100条啊，是不是有点慢啊

作者: cdcchen 时间: 2007-03-21 13:25
我又在mysql中建了相同的表，直接插入20000条，速度飞快，很快就完成了，为什么速度有这么大的差别啊

作者: likuku 时间: 2007-03-21 13:54
你开启 postgresql.conf 里的 autovacuum = on 。因为非 Win32 版本的pg,默认是关闭这个的。

另外，你确认你连接数据库是直接使用 IP 而不是使用 DNS.

我用 JDBC 测试过 Mysql 5 和 PostgreSQL,均插入 10W 条记录，也是每5000一批，Postgresql 很平稳的完成，相当快。

只有 mysql-jdbc 3.0.16 能在我的测试中正常完成且中文存储没问题。更高版本的JDBC 驱动都出现：MySQL 在程序开始运作后，内存占用直线上升，运行2分钟后直接死掉。

Java 代码一致.

我的部分记录:

PostgreSQL 8.1.4 Win32

## 使用控制COMMIT(一次提交)
D:\Program Files\test\db_test>java InsertTestPostgresql
<<---本页生成耗时[2875]毫秒(2.875秒)--->>

<<---共插入记录1000条-->>

D:\Program Files\test\db_test>java InsertTestPostgresql
<<---本页生成耗时[149175]毫秒(149.175秒)--->>

<<---共插入记录100000条-->>

Mysql 5.0.20 Win32

## jdbc 3.0.16 一次提交
D:\Program Files\test\db_test>java InsertTestMysql
<<---本页生成耗时[3846]毫秒(3.846秒)--->>

<<---共插入记录1000条-->>

D:\Program Files\test\db_test>java InsertTestMysql
<<---本页生成耗时[98582]毫秒(98.582秒)--->>

<<---共插入记录100000条-->>

测试相关代码,非常简单的：

Mysql 测试代码

import java.lang.*;
import java.sql.*;
public class InsertTestMysql{
public static void main(String [] args){
java.util.Date now_start = new java.util.Date();
long start_time=now_start.getTime();
int st = 100000;
String str,info;
String db="org.gjt.mm.mysql.Driver";
String host="jdbc:mysql://localhost/con_test";
String user="root";
String passwd="mysql";
Connection con=null;
try{
Class.forName(db).newInstance();
}
catch(Exception e){
System.out.println("加载驱动失败:"+db);
}
try{
con=DriverManager.getConnection(host,user,passwd);
con.setAutoCommit(false);
for (int i=1;i<=st;i++){
info = "这条记录是第=";
info = info.concat(java.lang.Integer.toString(i));
str = "insert into test (id,txt) values(?,?);";
PreparedStatement pstmt = con.prepareStatement(str);
pstmt.setInt(1,i);
pstmt.setString(2,info);
pstmt.executeUpdate();
//if(i%5000==0)con.commit();
}
//pstmt.close();
con.commit();
con.close();
}
catch(Exception e) {
System.out.println(e);
}
java.util.Date now_end = new java.util.Date();
long end_time=now_end.getTime();
long use_time=end_time-start_time;
System.out.println("<<---本页生成耗时["+use_time+"]毫秒("+((double)use_time)/1000+"秒)--->>");
System.out.println("\n<<---共插入记录"+st+"条-->>");
}
}

复制代码

postgresql 版测试代码

import java.lang.*;
import java.sql.*;
public class InsertTestPostgresql{
public static void main(String [] args){
java.util.Date now_start = new java.util.Date();
long start_time=now_start.getTime();
int st = 100000;
String str,info;
String db="org.postgresql.Driver";
String host="jdbc:postgresql://localhost/con_test";
String user="postgres";
String passwd="postgres";
Connection con=null;
try{
Class.forName(db).newInstance();
}
catch(Exception e){
System.out.println("加载驱动失败:"+db);
}
try{
con=DriverManager.getConnection(host,user,passwd);
con.setAutoCommit(false);
for (int i=1;i<=st;i++){
info = "这条记录是第=";
info = info.concat(java.lang.Integer.toString(i));
str = "insert into test (id,txt) values(?,?);";
PreparedStatement pstmt = con.prepareStatement(str);
pstmt.setInt(1,i);
pstmt.setString(2,info);
pstmt.executeUpdate();
//if(i%3000==0)con.commit();
}
//pstmt.close();
con.commit();
con.close();
}
catch(Exception e) {
System.out.println(e);
}
java.util.Date now_end = new java.util.Date();
long end_time=now_end.getTime();
long use_time=end_time-start_time;
System.out.println("<<---本页生成耗时["+use_time+"]毫秒("+((double)use_time)/1000+"秒)--->>");
System.out.println("\n<<---共插入记录"+st+"条-->>");
}
}

复制代码

作者: cdcchen 时间: 2007-03-21 14:02
我打开autovacuum = on了，也是直接使用的IP，双测试了一下MSSQL，速度跟PGSQL差不多
接着又试了一下MySQL，速度还是快啊，速度差太多了，是不是Java和PHP的原因？？

又或是我的PgSQL没有配置好？？楼上的能不能讲一下你的PgSQL怎么优化的？？

我的是FreeBSD6.1下编译的

[ 本帖最后由 cdcchen 于 2007-3-21 14:08 编辑 ]

作者: wolfop 时间: 2007-03-21 19:27
mysql你用的缺省的myisam表吧？那个玩意是表级别锁定，没有事务支持，当然快了。
不要只考虑速度，直接写文本文件岂不更快。
你用mysql完整的acid的innodb表试试。

作者: likuku 时间: 2007-03-22 15:42

原帖由 wolfop 于 2007-3-21 19:27 发表
mysql你用的缺省的myisam表吧？那个玩意是表级别锁定，没有事务支持，当然快了。
不要只考虑速度，直接写文本文件岂不更快。
你用mysql完整的acid的innodb表试试。

和上周7天都在测试 MySQL 5.2.3-Falcon-Alpha 的 Falcon事务存储引擎，和 soliddb-MySQL-5.0.27 的 soliddb存储引擎 (二者没有性能测试，只有可用测试。)

5.2.3 Falcon 表能用，任何字符集都成，php,jdbc 都没问题。

soliddb-mysql-5.0.27 表目前只支持 latin1 和 ucs2 存储。php 下，没有任何问题;jdbc 下 ucs2 没法用，根本不能连接数据库，总提示 SQL 语法错误。已经把错误发到 soliddb 官方。

Falcon 是 Mysql 自己开发的代替 innodb 的事务引擎。

Soliddb 是 Soliddb.com的，现在也开发了 mysql 的 soliddb 插件。

根据soliddb官方 2006-11-8 日测试报告:

8 个双核 AMD Opteron CPU, 每存储引擎 2GB 缓存

1. solidDB for MySQL Beta 5 (solidDB 存储引擎);
2. MySQL 5.0.27 使用 InnoDB 存储引擎

不同数据量时的事务效能 ,64个连接：
MySQL 5.0.27 InnoDB:
10 万行记录，每秒处理事务 12000 笔
30 万行记录，每秒处理事务 9000 笔
100 万行记录，每秒处理事务 5000 笔

MySQL solidDB:
10 万行记录，每秒处理事务 13000 笔
30 万行记录，每秒处理事务 13500 笔
100 万行记录，每秒处理事务 14200 笔

多核下的事务效能 ,64个连接 100万行记录:
MySQL 5.0.27 InnoDB:
4 核，每秒处理事务 5000 笔
8 核，每秒处理事务 4800 笔
16核，每秒处理事务 5000 笔

MySQL solidDB:
4 核，每秒处理事务 7800 笔
8 核，每秒处理事务 9800 笔
16核，每秒处理事务 14200 笔

Soliddb 引擎支持在线/不间断业务备份数据。

[ 本帖最后由 likuku 于 2007-3-22 15:43 编辑 ]

作者: likuku 时间: 2007-03-22 15:47

原帖由 cdcchen 于 2007-3-21 14:02 发表
我打开autovacuum = on了，也是直接使用的IP，双测试了一下MSSQL，速度跟PGSQL差不多
接着又试了一下MySQL，速度还是快啊，速度差太多了，是不是Java和PHP的原因？？

又或是我的PgSQL没有配置好？？楼上的能 ...

我没优化，都是默认值，在 Win32 用的。

PostgreSQL默认配置能应付绝大多数应用。

作者: perlpg 时间: 2007-03-23 00:14
其实我不懂java，但对数据库还是懂得一点点的。

con=DriverManager.getConnection(host,user,passwd);
con.setAutoCommit(false);

for (int i=1;i<=st;i++){

   info = "这条记录是第=";
   info = info.concat(java.lang.Integer.toString(i));
   str = "insert into test (id,txt) values(?,?);";
   PreparedStatement pstmt = con.prepareStatement(str);
   pstmt.setInt(1,i);
   pstmt.setString(2,info);
   pstmt.executeUpdate();
//if(i%3000==0)con.commit();
}
//pstmt.close();
con.commit();
con.close();

天啊！平时就这样写程序的吗？要是测试干脆写成：
con.ExecuteStatement(str);
不就完了嘛，对于批量插入，应该是使用下面的方法才合理吧？

con=DriverManager.getConnection(host,user,passwd);
con.setAutoCommit(false);

str = "insert into test (id,txt) values(?,?);";
PreparedStatement pstmt = con.prepareStatement(str);

for (int i=1;i<=st;i++){

   info = "这条记录是第=";
   info = info.concat(java.lang.Integer.toString(i));
   pstmt.setInt(1,i);
   pstmt.setString(2,info);
   pstmt.executeUpdate();
//if(i%3000==0)con.commit();
}
//pstmt.close();
con.commit();
con.close();

我没有java，但估计下面的方法应该比上面的快很多才对，特别是大型数据库，不信试试？

作者: cdcchen 时间: 2007-03-23 15:36
现在我的pgsql的CPU占用率非常大，用top查看，一有连接，postgres线程的CPU使用率立马到40-60%,这是什么原因呀，如果不考虑程序写的烂的话，应该怎么优化一下？？

作者: likuku 时间: 2007-03-23 17:11

原帖由 cdcchen 于 2007-3-23 15:36 发表
现在我的pgsql的CPU占用率非常大，用top查看，一有连接，postgres线程的CPU使用率立马到40-60%,这是什么原因呀，如果不考虑程序写的烂的话，应该怎么优化一下？？

建议去 www.pgsqldb.org 去问问。

那里专家多。

作者: likuku 时间: 2007-03-23 17:14

原帖由 perlpg 于 2007-3-23 00:14 发表
其实我不懂java，但对数据库还是懂得一点点的。

天啊！平时就这样写程序的吗？要是测试干脆写成：
con.ExecuteStatement(str);
不就完了嘛，对于批量插入，应该是使用下面的方法才合理吧？

我没有j ...

嗯，有道理，我试试。

我也是 java 初学者，当时没想过程序优化，只是正确实现功能。

con.ExecuteStatement(str); 这个对于批量插入，效率低，看过的子料都不推荐。

[ 本帖最后由 likuku 于 2007-3-23 17:19 编辑 ]

作者: perlpg 时间: 2007-03-24 00:44

for (int i=1;i<=st;i++){
......
str = "insert into test (id,txt) values(?,?);";
PreparedStatement pstmt = con.prepareStatement(str);
......
}

复制代码

按道理这样用语句句柄和
con.ExecuteStatement(str);
所做的工作对数据库来说没有区别, 都是准备一个句柄, 插入数据后丢弃该句柄, 然后下一个循环再准备一个..., 但你至少可以少写点代码吧? 我不知道java是如何处理这样的语句的(有智能缓存?), 但按我所知道编程语言的规则, 其工作和我描述的应该是一致的, 但第二种方法就不一样, 语句句炳只准备一次, 然后重复用了st次。
打个比喻，1000千个人去食堂吃饭，你的方法是每个人一个菜单，厨房为每个人独立做一次，要做完1000份饭要多长时间你算吧，而第二种用法的意思是让厨房做足够的菜，开饭的时候每人打一份，这样的效率当然就高了。
你虽然也使用了
str = "insert into test (id,txt) values(?,?);";
PreparedStatement pstmt = con.prepareStatement(str);
来做准备，但很不幸的是：用错地方了！在循环里面用，就是每个循环要准备一次，3000个循环下来就是3000次，在一个数据库操作中，准备是很费时间的，至少要比真正往数据库中插入一条记录使用的时间要多。

这也不算优化的事情，一个常识来的。

如果打开PG的储存冲刷，又使用默认的事务（就是每执行一条语句一个事务），则Insert和Update是很慢的，因为从数据安全性考虑，一个事务完成并真实保存到磁盘后数据才算是比较安全的。速度优化的方法有很多，例如使用合适大小的事务，忽略储存冲刷等。

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)