Difference between revisions of "Talk:PowerDNS OpenDBX Backend/Comparison"
(hello) |
|||
| (3 intermediate revisions by 2 users not shown) | |||
| Line 16: | Line 16: | ||
4) Create more pdns backends; at least 10 for a benchmark of this nature I would think. | 4) Create more pdns backends; at least 10 for a benchmark of this nature I would think. | ||
| − | + | ---- | |
| − | + | It would also be useful to benchmark a more recent vintage of Postgresql, at least in the 8.1 series, if not 8.2. | |
| − | + | ||
| − | + | == Results of testing again == | |
| + | After reading a lot of documentation about tuning the database server, I've repeated the tests with PostgreSQL 8.1 using Debian Etch. | ||
| − | + | With default setting, PostgreSQL 8.1 doesn't perform better than 7.4. I identified three settings which improved performance from 271qps to 316qps in this resource limited environment (only 256MB RAM): | |
| − | + | * '''max_connections''' = 15 | |
| − | + | * '''shared_memory''' = 15000 (kernel shmmax is 128MB) | |
| + | * '''effective_cache_size''' = 10000 | ||
| + | |||
| + | Setting '''distributor-threads''' in PowerDNS to anything higher than 3 is counterproductive because the performance doesn't get better but the system doesn't respond within the time limit and queryperf lists some lost queries. | ||
| + | |||
| + | I haven't managed it to force PostgreSQL to hold all data in memory and don't read it from disk again to speed up the second and third run. This is the main reason why it still performs so badly while MySQL provides much better figures. | ||
| + | |||
| + | --[[User:Nose|Nose]] 15:08, 30 April 2007 (CEST) | ||
Latest revision as of 16:56, 17 May 2007
Which settings are necessary to get a better PostgreSQL performance?
1) On a 256MB machine, you might want to:
echo 96000000 > /proc/sys/kernel/shmmax echo 96000000 > /proc/sys/kernel/shmall Set shared_buffers = 6000 in postgresql.conf and restart.
The default cache settings are very conservative.
2) Make sure you've run an ANALYZE in your database after loading the test data. The PostgreSQL query planner assumes tables are empty until analyze tells it different. If you've deleted and reloaded the data, run a VACUUM FULL ANALYZE in the database before running the benchmark.
3) Try setting enable_seqscan=false in postgresql.conf. On small data sets, PostgreSQL will frequently avoid using indexes. In a benchmark like this, where all the data will end up cached, that could slow it down a fair bit, even though for a general purpose database with more concurrency it might make sense. You don't want to do this in a real database unless you will also have the luxury of always having your data cached; instead tweak the cost estimates to get it to use indexes where appropriate. setting random_page_cost = 2.0 and giving a good estimate for effective_cache_size is usually sufficient.
4) Create more pdns backends; at least 10 for a benchmark of this nature I would think.
It would also be useful to benchmark a more recent vintage of Postgresql, at least in the 8.1 series, if not 8.2.
Results of testing again
After reading a lot of documentation about tuning the database server, I've repeated the tests with PostgreSQL 8.1 using Debian Etch.
With default setting, PostgreSQL 8.1 doesn't perform better than 7.4. I identified three settings which improved performance from 271qps to 316qps in this resource limited environment (only 256MB RAM):
- max_connections = 15
- shared_memory = 15000 (kernel shmmax is 128MB)
- effective_cache_size = 10000
Setting distributor-threads in PowerDNS to anything higher than 3 is counterproductive because the performance doesn't get better but the system doesn't respond within the time limit and queryperf lists some lost queries.
I haven't managed it to force PostgreSQL to hold all data in memory and don't read it from disk again to speed up the second and third run. This is the main reason why it still performs so badly while MySQL provides much better figures.
--Nose 15:08, 30 April 2007 (CEST)
