Разница между страницами «Работа» и «Партиционирование»

Материал из Home Wiki
(Различия между страницами)
Перейти к навигации Перейти к поиску
 
м
 
Строка 1: Строка 1:
[[:Категория:Работа|Все страницы относящиеся к категории Работа]]
=== Функции ===
<syntaxhighlight lang="plsql">
CREATE OR REPLACE FUNCTION move_data_month(dd timestamp without time zone)


= Java =
  RETURNS text AS
{| border="0"
| [[JHipster]]
| [[JDK]]
| [[Java9]]
|-
| [[Spring]] [[Spring boot]]
| [[Hibernate]]
| [[Mockito]]
|-
| [[Gradle]]
| [[Maven]]
|
|-
| [[Patterns]]
|
|
|-
| [[Angular]]
| [[JavaScript]]
| [[TypeScript]]
|-
| [[LiquiBase]]
|
|
|-
| [[Eclipse]]
|
|
|-
| [[Kafka]]
|
|
|-
| [[Jenkins]]
|
|
|-
| [[PlantUML]]
|
|
|-
| colspan="3" | [[Java-собеседование]]
|}


= Linux =
$BODY$


[[Docker]]
DECLARE


[[Ubuntu]]
     dd ALIAS FOR $1;


[[mdadm]]
     dname text;


Команды Linux:
     dname1 text;


sudo dmidecode --type memory | less
     rn text;


= FreeBSD =
BEGIN
[[Gmirror]]


= Postgresql =
    select into dname1 extract(month from ($1-interval '1 month'));


[[Postgresql]] [[PgBouncer]]
    if char_length(dname1) = 1 then


[[Партиционирование]]
        select into dname1 '0' || extract(month from ($1-interval '1 month'));


= VirtualBox =
    end if;
[[VirtualBox]]


= Git =
    DROP TABLE IF EXISTS  payments_log_move;
[[Git]]


[[Git-key]]
    CREATE TABLE payments_log_move ( ) INHERITS (payments_log);


= AI =
    ALTER TABLE payments_log_move NO INHERIT payments_log;
[[TensorFlow]]


[[Machine Learning with TensorFlow Book]]
    INSERT INTO payments_log_move select * from payments_log where "time" >= $1 - interval '1 month' AND "time" < $1;


https://habrahabr.ru/company/epam_systems/blog/350654/
    DELETE FROM payments_log WHERE "time" >= $1 - interval '1 month' AND "time" < $1;


Видеолекции курса «Машинное обучение»
    SELECT INTO dname 'payments_log_y' || extract(year from ($1-interval '1 month')) || 'm' || dname1;
https://yandexdataschool.ru/edu-process/courses/machine-learning


= Шифрование / OpenSSL =
    
[[Шифрование]]


= English =
    SELECT INTO rn (SELECT count(relname) FROM pg_class WHERE relname = dname);


[https://habrahabr.ru/company/netologyru/blog/340358/ Деловая переписка на английском языке: фразы и советы]
    if rn = '0' THEN


= Интересные ссылки =
        EXECUTE 'CREATE TABLE ' || dname || '( ) INHERITS (payments_log)';


http://highload.guide/blog/query_performance_postgreSQL.html
    end if;


http://highload.guide/blog/highload-for-beginners.html
    EXECUTE 'INSERT INTO ' || dname || ' select * from payments_log_move';


http://www.intuit.ru/studies/courses/2298/598/lecture/12868
    return 'OK ' || dd || ' <> ' ||dname;


== Spring Microservices ==
END;


http://callistaenterprise.se/blogg/teknik/2015/04/10/building-microservices-with-spring-cloud-and-netflix-oss-part-1/
$BODY$


https://habrahabr.ru/post/280786/
  LANGUAGE plpgsql
 
;
</syntaxhighlight>
 
select move_data_month('2016-01-01'::timestamp);
 
select * from payments_log_y2013m04;
 
<syntaxhighlight lang="plsql">
CREATE OR REPLACE FUNCTION move_data_year(dd timestamp without time zone)
 
  RETURNS text AS
 
$BODY$
 
DECLARE
 
     dd ALIAS FOR $1;
 
     dname text;
 
     dname1 text;
 
     rn text;
 
BEGIN
 
    DROP TABLE IF EXISTS  payments_log_move;
 
    CREATE TABLE payments_log_move ( ) INHERITS (payments_log);
 
    ALTER TABLE payments_log_move NO INHERIT payments_log;
 
    INSERT INTO payments_log_move select * from payments_log where "time" >= $1 - interval '1 year' AND "time" < $1;
 
    DELETE FROM payments_log WHERE "time" >= $1 - interval '1 year' AND "time" < $1;
 
    SELECT INTO dname 'payments_log_y' || extract(year from ($1-interval '1 year'));
 
    SELECT INTO rn (SELECT count(relname) FROM pg_class WHERE relname = dname);
 
    if rn = '0' THEN
 
        EXECUTE 'CREATE TABLE ' || dname || '( ) INHERITS (payments_log)';
 
    end if;
 
    EXECUTE 'INSERT INTO ' || dname || ' select * from payments_log_move';
 
    return 'OK ' || dd || ' <> ' ||dname;
 
END;
 
$BODY$
 
  LANGUAGE plpgsql
 
;
</syntaxhighlight>
 
<syntaxhighlight lang="plsql">
CREATE OR REPLACE FUNCTION index_part_data(year character varying)
 
  RETURNS text AS
 
$BODY$
 
DECLARE
 
     year ALIAS FOR $1;
 
     dname text;
 
     rn text;
 
BEGIN
 
    SELECT INTO dname 'payments_log_y' || year;
 
    SELECT INTO rn (SELECT count(relname) FROM pg_class WHERE relname = dname);
 
    if rn = '0' THEN
 
        return 'NO SUCH TABLE ' || dname;
 
    end if;
 
    EXECUTE 'CREATE INDEX pl' || year || '_idx_identifier_id_service ON payments_log_y' || year ||
 
        ' USING btree (identifier COLLATE pg_catalog."default", id_service)';
 
    EXECUTE 'CREATE INDEX pl' || year || '_idx_pl_external_time ON payments_log_y' || year ||
 
        ' USING btree (external_time)';
 
    EXECUTE 'CREATE UNIQUE INDEX pl' || year || '_id_apparat_id_trans_idx ON payments_log_y' || year ||
 
        ' USING btree (id_apparat, id_trans)';
 
    EXECUTE 'CREATE INDEX pl' || year || '_id_apparat_idx ON payments_log_y' || year ||
 
        ' USING btree (id_apparat)';
 
    EXECUTE 'CREATE INDEX pl' || year || '_id_region_idx ON payments_log_y' || year ||
 
        ' USING btree (id_region)';
 
    EXECUTE 'CREATE INDEX pl' || year || '_id_service_idx ON payments_log_y' || year ||
 
        ' USING btree (id_service)';
 
    EXECUTE 'CREATE INDEX pl' || year || '_identifier_idx ON payments_log_y' || year ||
 
        ' USING btree (identifier COLLATE pg_catalog."default")';
 
    EXECUTE 'CREATE INDEX pl' || year || '_time_app_idx ON payments_log_y' || year ||
 
        ' USING btree (time_app)';
 
    EXECUTE 'CREATE INDEX pl' || year || '_time_idx ON payments_log_y' || year ||
 
        ' USING btree ("time")';
 
    EXECUTE 'CREATE INDEX pl' || year || '_time_proc_idx ON payments_log_y' || year ||
 
        ' USING btree (time_proc)';
 
    EXECUTE 'CREATE UNIQUE INDEX payments_log_y' || year || '_id_idx ON payments_log_y' || year ||
 
        ' USING btree (id)';
 
    return 'DONE INDEXES ON ' || dname;
 
END;
 
$BODY$
 
  LANGUAGE plpgsql
 
;
</syntaxhighlight>
 
select index_part_data('2010');
 
=== Анализ запросов ===
1. в базе
 
create extension pg_stat_statements;
 
2. в конфиге
 
<syntaxhighlight lang="text">
shared_preload_libraries = 'pg_stat_statements'
 
pg_stat_statements.max = 1000
 
pg_stat_statements.track = all
</syntaxhighlight>
 
<syntaxhighlight lang="sql">
select total_time/calls as AVG_COST,* from pg_stat_statements where total_time/calls > 10 and calls > 10 order by AVG_COST desc;
</syntaxhighlight>
 
=== Оптимизация ===
<syntaxhighlight lang="sql">
CREATE INDEX idx_events_class_date
 
ON events
 
USING btree
 
(id_class_events, date);
</syntaxhighlight>
 
ip-appcheck
<syntaxhighlight lang="sql">
CREATE INDEX idx_events_ip_appcheck707 ON events USING btree (id_apparat, id_class_events) WHERE id_class_events=707;
 
CREATE INDEX idx_events_ip_appcheck708 ON events USING btree (id_apparat, id_class_events) WHERE id_class_events=708;
</syntaxhighlight>
 
[[Категория:Работа]]

Версия 08:27, 10 января 2017

Функции

CREATE OR REPLACE FUNCTION move_data_month(dd timestamp without time zone)

  RETURNS text AS

$BODY$

DECLARE

     dd ALIAS FOR $1;

     dname text;

     dname1 text;

     rn text;

BEGIN

    select into dname1 extract(month from ($1-interval '1 month'));

    if char_length(dname1) = 1 then

        select into dname1 '0' || extract(month from ($1-interval '1 month'));

    end if;

    DROP TABLE IF EXISTS  payments_log_move;

    CREATE TABLE payments_log_move ( ) INHERITS (payments_log);

    ALTER TABLE payments_log_move NO INHERIT payments_log;

    INSERT INTO payments_log_move select * from payments_log where "time" >= $1 - interval '1 month' AND "time" < $1;

    DELETE FROM payments_log WHERE "time" >= $1 - interval '1 month' AND "time" < $1;

    SELECT INTO dname 'payments_log_y' || extract(year from ($1-interval '1 month')) || 'm' || dname1;

    

    SELECT INTO rn (SELECT count(relname) FROM pg_class WHERE relname = dname);

    if rn = '0' THEN

        EXECUTE 'CREATE TABLE ' || dname || '( ) INHERITS (payments_log)';

    end if;

    EXECUTE 'INSERT INTO ' || dname || ' select * from payments_log_move';

    return 'OK ' || dd || ' <> ' ||dname;

END;

$BODY$

  LANGUAGE plpgsql

;

select move_data_month('2016-01-01'::timestamp);

select * from payments_log_y2013m04; 

CREATE OR REPLACE FUNCTION move_data_year(dd timestamp without time zone)

  RETURNS text AS

$BODY$

DECLARE

     dd ALIAS FOR $1;

     dname text;

     dname1 text;

     rn text;

BEGIN

    DROP TABLE IF EXISTS  payments_log_move;

    CREATE TABLE payments_log_move ( ) INHERITS (payments_log);

    ALTER TABLE payments_log_move NO INHERIT payments_log;

    INSERT INTO payments_log_move select * from payments_log where "time" >= $1 - interval '1 year' AND "time" < $1;

    DELETE FROM payments_log WHERE "time" >= $1 - interval '1 year' AND "time" < $1;

    SELECT INTO dname 'payments_log_y' || extract(year from ($1-interval '1 year'));

    SELECT INTO rn (SELECT count(relname) FROM pg_class WHERE relname = dname);

    if rn = '0' THEN

        EXECUTE 'CREATE TABLE ' || dname || '( ) INHERITS (payments_log)';

    end if;

    EXECUTE 'INSERT INTO ' || dname || ' select * from payments_log_move';

    return 'OK ' || dd || ' <> ' ||dname;

END;

$BODY$

  LANGUAGE plpgsql

;

CREATE OR REPLACE FUNCTION index_part_data(year character varying)

  RETURNS text AS

$BODY$

DECLARE

     year ALIAS FOR $1;

     dname text;

     rn text;

BEGIN

    SELECT INTO dname 'payments_log_y' || year;

    SELECT INTO rn (SELECT count(relname) FROM pg_class WHERE relname = dname);

    if rn = '0' THEN

        return 'NO SUCH TABLE ' || dname;

    end if;

    EXECUTE 'CREATE INDEX pl' || year || '_idx_identifier_id_service ON payments_log_y' || year ||

        ' USING btree (identifier COLLATE pg_catalog."default", id_service)';

    EXECUTE 'CREATE INDEX pl' || year || '_idx_pl_external_time ON payments_log_y' || year ||

        ' USING btree (external_time)';

    EXECUTE 'CREATE UNIQUE INDEX pl' || year || '_id_apparat_id_trans_idx ON payments_log_y' || year ||

        ' USING btree (id_apparat, id_trans)';

    EXECUTE 'CREATE INDEX pl' || year || '_id_apparat_idx ON payments_log_y' || year ||

        ' USING btree (id_apparat)';

    EXECUTE 'CREATE INDEX pl' || year || '_id_region_idx ON payments_log_y' || year ||

        ' USING btree (id_region)';

    EXECUTE 'CREATE INDEX pl' || year || '_id_service_idx ON payments_log_y' || year ||

        ' USING btree (id_service)';

    EXECUTE 'CREATE INDEX pl' || year || '_identifier_idx ON payments_log_y' || year ||

        ' USING btree (identifier COLLATE pg_catalog."default")';

    EXECUTE 'CREATE INDEX pl' || year || '_time_app_idx ON payments_log_y' || year ||

        ' USING btree (time_app)';

    EXECUTE 'CREATE INDEX pl' || year || '_time_idx ON payments_log_y' || year ||

        ' USING btree ("time")';

    EXECUTE 'CREATE INDEX pl' || year || '_time_proc_idx ON payments_log_y' || year ||

        ' USING btree (time_proc)';

    EXECUTE 'CREATE UNIQUE INDEX payments_log_y' || year || '_id_idx ON payments_log_y' || year ||

        ' USING btree (id)';

    return 'DONE INDEXES ON ' || dname;

END;

$BODY$

  LANGUAGE plpgsql

;

select index_part_data('2010');

Анализ запросов

1. в базе

create extension pg_stat_statements;

2. в конфиге 

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 1000

pg_stat_statements.track = all

select total_time/calls as AVG_COST,* from pg_stat_statements where total_time/calls > 10 and calls > 10 order by AVG_COST desc;

Оптимизация

CREATE INDEX idx_events_class_date

ON events

USING btree

(id_class_events, date);

ip-appcheck 

CREATE INDEX idx_events_ip_appcheck707 ON events USING btree (id_apparat, id_class_events) WHERE id_class_events=707;

CREATE INDEX idx_events_ip_appcheck708 ON events USING btree (id_apparat, id_class_events) WHERE id_class_events=708;
Источник — https://wiki.wolf-a.ru/index.php?title=Работа&oldid=155