postgresql并发查询问题

[asdfasdf211]

postgresql并发查询问题

postgresql8.3 配置 max connection = 100
Apache/2.2.10
suse11

表记录 user 有 3万条


使用C写CGI程序查询表数据，
当不用分页功能时，也就是用下面的SQL：

select * from user where address like '%3%' limit 10 offset 0

/apache2/bin> ./ab -n 1000 -c 100 http://localhost:8080/cgi-bin/simpleCgi?address=3
Concurrency Level: 100
Time taken for tests: 4.172 seconds
Complete requests: 1000
Failed requests: 0
Write errors: 0
Total transferred: 1552068 bytes
HTML transferred: 1406000 bytes
Requests per second: 239.72 [#/sec] (mean)
Time per request: 417.162 [ms] (mean)
Time per request: 4.172 [ms] (mean, across all concurrent requests)
Transfer rate: 363.33 [Kbytes/sec] received

Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 2 7.2 0 26
Processing: 20 396 72.1 414 460
Waiting: 16 396 72.2 414 460
Total: 35 399 66.3 414 460

Percentage of the requests served within a certain time (ms)
50% 414
66% 421
75% 424
80% 427
90% 432
95% 438
98% 443
99% 449
100% 460 (longest request)


＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
当分页时就非常慢了，SQL：

select count(address) from user where address like '%3%'
select * from user where address like '%3%' limit 10 offset 0

Concurrency Level: 100
Time taken for tests: 98.207 seconds
Complete requests: 1000
Failed requests: 983
(Connect: 0, Receive: 0, Length: 983, Exceptions: 0)
Write errors: 0
Total transferred: 3022457 bytes
HTML transferred: 2862793 bytes
Requests per second: 10.18 [#/sec] (mean)
Time per request: 9820.718 [ms] (mean)
Time per request: 98.207 [ms] (mean, across all concurrent requests)
Transfer rate: 30.06 [Kbytes/sec] received

Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 2 5.6 0 21
Processing: 471 9452 1634.4 9790 11183
Waiting: 469 9450 1634.7 9788 11183
Total: 483 9454 1630.1 9791 11197

Percentage of the requests served within a certain time (ms)
50% 9791
66% 9982
75% 10112
80% 10203
90% 10429
95% 10687
98% 10902
99% 11006
100% 11197 (longest request)


－－－－－－－－－－－－－－－－
怎么会相差这么大？？

[asdfasdf211]

[Quote=引用 3 楼 trainee 的回复:]
postgresql的COUNT(*) 本来就很慢，要扫描整个表。
但3万条慢得这个地步，可能是这个表含有很多过时的行。
不知有没有做vacuum，高版本会自动vacuum
[/Quote]

8.3啊，最新版了，这3万条数据是一次导入进来测试的，没其他表，这个表数据也没删除，修改过。

[xbt746]

那只能建议你去postgre
找到sql的邮件列表，问全世界使用postgre的人了

[xbt746]

应该不会出现这种情况
是不是每次都这样？

[asdfasdf211]

每次都这样，用 jsp , hibernate (connection pool = 20) , tomcat 6 效果也一样。

[trainee]

postgresql的COUNT(*) 本来就很慢，要扫描整个表。
但3万条慢得这个地步，可能是这个表含有很多过时的行。
不知有没有做vacuum，高版本会自动vacuum

[amdwinter]

up

[老孙来也]

select count(address) from user where address like '%3%'

主要慢在这里了。

就这一句来看，几乎没有解决办法。

除非你优化数据库参数或者优化服务器。