|
Помогите с настройками пожалуйста. 64 Гб рамы и тормозит.
#39227933
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Пытаюсь заставить работать постгре без тормозов.
Индексы на всех полях, по которым делается поиск.
Сценарий: таблица с >2 млн. записей, в которую вставляется примерно 300-500 тыс. строк в сутки, в 60 потоков.
Бинарных данных нет, есть json и служебная инфа, есть ещё несколько небольших таблиц (10-100 записей) в которых эти же потоки постоянно обновляют статус операций и счётчики (select/update).
Что я хочу понять:
1. Почему тормозит. Чухом нюю, что можно быстрее, но может я не прав?
2. Может быть можно как-то оптимизировать запрос? Ускорят ли процесс вьюхи?
Версия 9.5.2, в системе 64 Гб ОЗУ, linux 64бит ядро 4.5.1.
Вот такой запрос может занимать до минуты (это статистика, дёргаю раз в пять минут):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT
( SELECT count(*) from bot.acquires WHERE ( status = 'stored' OR status = 'on_fs' ) AND curl_exitstatus = 0 ) AS images_ok,
( SELECT count(*) from bot.acquires WHERE (status = 'badurl' OR status = 'lost' OR ( pic_file_size IS NOT NULL AND pic_file_size < 20))) AS images_404,
( SELECT count(*) FROM bot.acquires WHERE status IS NULL OR status = 'new' ) AS images_waiting,
( SELECT count(*) FROM bot.acquires WHERE updated_at > (now() AT TIME ZONE 'UTC') - interval '1 hour') AS lasthour,
( SELECT count(*) FROM bot.acquires WHERE updated_at > (now() AT TIME ZONE 'UTC') - interval '1 hour' AND status = 'badurl') AS bad_lasthour,
( SELECT count(*) FROM bot.acquires WHERE updated_at > (now() AT TIME ZONE 'UTC') - interval '24 hour') AS lastday,
( SELECT count(*) FROM bot.acquires WHERE updated_at > (now() AT TIME ZONE 'UTC') - interval '24 hour' AND status = 'badurl') AS bad_lastday,
( SELECT sum((pagestats->>'dup_counter')::integer) FROM bot.packages ) AS duplicates
LIMIT 1
Вот настройки постгре:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
data_directory = '/work/postgres/data-9.5'
external_pid_file = '/var/lib/postgres/data/postmaster.pid' # write an extra PID file
listen_addresses = 'localhost' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 200 # (change requires restart)
superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directories = '/run/postgresql' # comma-separated list of directories
tcp_keepalives_idle = 30 # TCP_KEEPIDLE, in seconds;
shared_buffers = 1GB # min 128kB
huge_pages = try # on, off, or try
temp_buffers = 16MB # min 800kB
max_prepared_transactions = 5 # zero disables the feature
work_mem = 8MB # min 64kB
maintenance_work_mem = 64MB # min 1MB
autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
dynamic_shared_memory_type = posix # the default is the first option
vacuum_cost_delay = 75 # 0-100 milliseconds
effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
max_worker_processes = 40
wal_level = minimal # minimal, archive, hot_standby, or logical
full_page_writes = on # recover from partial page writes
wal_compression = on # enable compression of full-page writes
wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
commit_delay = 100 # range 0-100000, in microseconds
commit_siblings = 5 # range 1-1000
checkpoint_timeout = 15min # range 30s-1h
max_wal_size = 1GB
min_wal_size = 80MB
checkpoint_completion_target = 0.75 # checkpoint target duration, 0.0 - 1.0
checkpoint_warning = 1min # 0 disables
log_timezone = 'W-SU'
track_counts = on
stats_temp_directory = '/run/postgresql/stats'
autovacuum = on # Enable autovacuum subprocess? 'on'
log_autovacuum_min_duration = 1000
autovacuum_max_workers = 10
autovacuum_naptime = 10min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
datestyle = 'iso, dmy'
timezone = 'W-SU'
lc_messages = 'ru_RU.UTF-8' # locale for system error message
lc_monetary = 'ru_RU.UTF-8' # locale for monetary formatting
lc_numeric = 'ru_RU.UTF-8' # locale for number formatting
lc_time = 'ru_RU.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.russian'
И вот схема базы - сделал дамп, почистил руками.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
CREATE SCHEMA bot;
SET search_path = bot, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE bindata (
id integer NOT NULL,
created_at timestamp without time zone DEFAULT now() NOT NULL,
image_id bigint,
image_bin bytea,
status character varying(32) DEFAULT 'new'::character varying NOT NULL
);
CREATE SEQUENCE bindata_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE acquires (
id integer NOT NULL,
created_at timestamp without time zone DEFAULT now() NOT NULL,
updated_at timestamp without time zone DEFAULT now() NOT NULL,
url character varying,
icode character varying(127),
iid bigint,
owner_id bigint,
status character varying(32) DEFAULT 'new'::character varying NOT NULL,
image_data jsonb DEFAULT '{}'::jsonb NOT NULL,
curl_stderr text,
curl_exitstatus smallint,
page_id integer NOT NULL,
pic_file_name character varying,
pic_path character varying,
pic_content_type character varying,
pic_file_size integer,
pic_updated_at timestamp without time zone,
pic_fingerprint character varying,
pic_processing boolean DEFAULT false NOT NULL,
bot_id integer
);
CREATE SEQUENCE acquires_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE service_names (
id integer NOT NULL,
created_at timestamp without time zone DEFAULT now() NOT NULL,
orig_id bigint NOT NULL,
name character varying
);
CREATE SEQUENCE service_names_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE packages (
id integer NOT NULL,
created_at timestamp without time zone DEFAULT now() NOT NULL,
updated_at timestamp without time zone DEFAULT now() NOT NULL,
tag character varying NOT NULL,
cookie text,
nextid character varying,
status character varying(32) DEFAULT 'new'::character varying NOT NULL,
counter bigint DEFAULT 0 NOT NULL,
page_data jsonb,
bot_id integer,
curlog text,
curlretval smallint,
pagetats jsonb
);
CREATE SEQUENCE packages_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE robots (
id integer NOT NULL,
created_at timestamp without time zone DEFAULT now() NOT NULL,
updated_at timestamp without time zone DEFAULT now() NOT NULL,
bot_id character varying(256) NOT NULL,
descr text,
status character varying(32) DEFAULT 'new'::character varying NOT NULL,
hostname character varying(127) NOT NULL,
username character varying(64) NOT NULL,
port integer NOT NULL,
job public.robot_job_type,
syslog text,
proxyport integer,
process_id integer
);
CREATE SEQUENCE robots_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER TABLE ONLY bindata ALTER COLUMN id SET DEFAULT nextval('bindata_id_seq'::regclass);
ALTER TABLE ONLY acquires ALTER COLUMN id SET DEFAULT nextval('acquires_id_seq'::regclass);
ALTER TABLE ONLY service_names ALTER COLUMN id SET DEFAULT nextval('service_names_id_seq'::regclass);
ALTER TABLE ONLY packages ALTER COLUMN id SET DEFAULT nextval('packages_id_seq'::regclass);
ALTER TABLE ONLY robots ALTER COLUMN id SET DEFAULT nextval('robots_id_seq'::regclass);
ALTER TABLE ONLY bindata ADD CONSTRAINT bindata_pkey PRIMARY KEY (id);
ALTER TABLE ONLY acquires ADD CONSTRAINT acquires_pkey PRIMARY KEY (id);
ALTER TABLE ONLY service_names ADD CONSTRAINT service_names_pkey PRIMARY KEY (id);
ALTER TABLE ONLY packages ADD CONSTRAINT packages_pkey PRIMARY KEY (id);
ALTER TABLE ONLY robots ADD CONSTRAINT robots_pkey PRIMARY KEY (id);
ALTER TABLE ONLY robots ADD CONSTRAINT robots_proxyport_key UNIQUE (proxyport);
CREATE INDEX image_bot_id_idx ON acquires USING btree (bot_id);
CREATE INDEX image_created_idx ON acquires USING btree (created_at);
CREATE INDEX image_updated_idx ON acquires USING btree (updated_at);
CREATE INDEX bot_curl_idx ON acquires USING btree (curl_exitstatus);
CREATE INDEX bot_imagebin_status_idx ON bindata USING btree (status);
CREATE INDEX bot_imagefk_idx ON bindata USING btree (image_id);
CREATE UNIQUE INDEX bot_imageid_idx ON acquires USING btree (icode, iid);
CREATE INDEX bot_imageurl_idx ON acquires USING btree (url);
CREATE UNIQUE INDEX bot_instagram_id_idx ON service_names USING btree (orig_id);
CREATE INDEX bot_page_status_idx ON packages USING btree (status);
CREATE INDEX bot_people_name_idx ON service_names USING btree (name);
CREATE INDEX bot_picfile_idx ON acquires USING btree (pic_file_name, pic_path);
CREATE INDEX bot_pichash_idx ON acquires USING btree (pic_fingerprint);
CREATE UNIQUE INDEX bot_robot_id_idx ON robots USING btree (bot_id);
CREATE INDEX bot_robot_job_idx ON robots USING btree (job);
CREATE INDEX bot_robot_status_idx ON robots USING btree (status);
CREATE INDEX page_bot_id_idx ON packages USING btree (bot_id);
CREATE INDEX packages_curl_status_idx ON packages USING btree (curlretval);
CREATE INDEX packages_lastpage_idx ON packages USING gin (packagestats);
CREATE INDEX process_id_idx ON robots USING btree (process_id);
ALTER TABLE ONLY bindata ADD CONSTRAINT bindata_image_id_fkey FOREIGN KEY (image_id) REFERENCES acquires(id) ON DELETE CASCADE;
ALTER TABLE ONLY acquires ADD CONSTRAINT acquires_bot_id_fkey FOREIGN KEY (bot_id) REFERENCES robots(id) ON DELETE RESTRICT;
ALTER TABLE ONLY acquires ADD CONSTRAINT acquires_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES service_names(orig_id) ON DELETE CASCADE;
ALTER TABLE ONLY acquires ADD CONSTRAINT acquires_page_id_fkey FOREIGN KEY (page_id) REFERENCES packages(id) ON DELETE RESTRICT;
ALTER TABLE ONLY packages ADD CONSTRAINT packages_bot_id_fkey FOREIGN KEY (bot_id) REFERENCES robots(id) ON DELETE RESTRICT;
|
|
|
