Home Performance configuration seems to be ineffective PostgreSQL
Reply: 1

Performance configuration seems to be ineffective PostgreSQL

ShellRox
1#
ShellRox Published in 2018-02-13 19:46:26Z

I'm not very experienced in databases and i want to increase performance of PostgreSQL query by using configuration. Most of the query takes around 3.5 seconds to be completely searched in logs. Then i checked the configuration file and settings are set to relatively low values, therefore i calculated the best possible configuration and entered it in psql:

postgres=# ALTER SYSTEM SET
postgres-#  max_connections = '200';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  shared_buffers = '4GB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  effective_cache_size = '12GB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  work_mem = '20971kB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  maintenance_work_mem = '1GB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  min_wal_size = '1GB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  max_wal_size = '2GB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  checkpoint_completion_target = '0.7';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  wal_buffers = '16MB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  default_statistics_target = '100';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  random_page_cost = '4';
ALTER SYSTEM

This didn't work at first, and i thought restarting database was necessary, so i used:

SELECT pg_reload_conf();

But there was no progress still. Before configuration work_mem was 4 MB whereas maintenance_work_mem was 64 MB and even after increasing them at these numbers, time taken for the database queries were very similar.


Table:

Table was created automatically by Django (my web framework), therefore like this:

CREATE TABLE tablename (
    "id" serial NOT NULL PRIMARY KEY,
    "some_char" varchar(30) NOT NULL,
);

Index creation:

CREATE INDEX trgm_idx ON table USING gist (column gist_trgm_ops);

Execution plans (explain and analyze):

 Bitmap Heap Scan on main_question  (cost=1721.05..20990.16 rows=1786 width=436) (actual time=2634.604..2634.634 rows=9 loops=1)
   Recheck Cond: (attr1 % 'querytext'::text)
   Filter: (char_length(attr2) > 300)
   Rows Removed by Filter: 5
   Heap Blocks: exact=13
   Buffers: shared hit=419319
   ->  Bitmap Index Scan on trgm_idx  (cost=0.00..1720.60 rows=5358 width=0) (actual time=2634.578..2634.578 rows=14 loops=1)
         Index Cond: (attr1 % 'querytext'::text)
         Buffers: shared hit=419306
 Planning time: 0.131 ms
 Execution time: 2634.739 ms
(11 rows)

Query logs:

before configuration:

(0.000) SELECT typarray FROM pg_type WHERE typname = 'citext'; args=None
(3.568) SELECT "main_model"."id", "main_model"."attribute1", "main_model"."attribute2" FROM "main_model" WHERE ((CHAR_LENGTH(attribute2) > 350) AND "main_model"."attribute1" % 'querytext'); args=(u'querytext',)

after configuration:

(0.000) SELECT typarray FROM pg_type WHERE typname = 'citext'; args=None
(3.555) SELECT "main_model"."id", "main_model"."attribute1", "main_model"."attribute2" FROM "main_model" WHERE ((CHAR_LENGTH(attribute2) > 350) AND "main_model"."attribute1" % 'querytext'); args=(u'querytext',)

as you can see, query times are very similar for some reason.


Shouldn't these settings increase the maximum memory usage thus the performance? Am i doing something incorrectly?

Andomar
2#
Andomar Reply to 2018-02-13 20:01:41Z

Setting the max connections to 200 is a sure sign you are new to Postgres performance tuning. Check out the wiki on performance or some of the introductions on YouTube. I can recommend Christophe Pettus: PostgreSQL When It's Not Your Job.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.303835 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO