其他
别裁员!别裁员!别裁员! 一招降低IT数据库成本
背景
2020疫情无情，诸多企业因此受挫，特别中小企业，甚至到了要裁员的地步, 但是人才是最宝贵的，裁员一定是下下策，如何渡过这个难关，疫情带给我们什么反思?
开源节流有新方法，通常数据库在企业IT支出中的占比将近一半，降低数据库成本对降低企业IT成本效果明显，但是一般企业没有专业DBA，很难在这方面下手，不过没关系，有了云厂商，一切变得简单。借助阿里云我们找到了可以为企业IT节省至少一倍成本的方法.
到底时什么方法呢? 回顾一下年前做的一系列MySQL+PG联合解决方案的课程.
《阿里云 RDS PostgreSQL+MySQL 联合解决方案课程 - 汇总视频、课件》
在众多数据库中, PG是一个企业级的开源数据库, 各方面的功能与Oracle对齐, 适合范围广, 能处理的数据量庞大. 采用PG的大型企业例如平安，邮储银行，阿里，华为，中兴，人保, 招商, 富士康, 苹果, SAP, saleforce等以及全球财富1000强等众多企业。 《外界对PostgreSQL 的评价》
export PGPASSWORD=xxxxxxxx! psql -h pgm-bp1z26gbo3gx893a129310.pg.rds.aliyuncs.com -p 1433 -U user123 db1 MySQL:mysql -h rm-bp1wv992ym962k85888370.mysql.rds.aliyuncs.com -P 3306 -u user123 --password=xxxxxx! -D db1yum install -y mysql-* yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum install -y postgresql12CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), birth TIMESTAMP, hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL);DROP PROCEDURE IF EXISTS BatchInsert;
delimiter // -- 把界定符改成双斜杠 CREATE PROCEDURE BatchInsert(IN init INT, IN loop_time INT) -- 第一个参数为初始ID号（可自定义），第二个位生成MySQL记录个数 BEGIN DECLARE Var INT; DECLARE ID INT; SET Var = 0; SET ID = init; WHILE Var < loop_time DO insert into employees (id, fname, lname, birth, hired, separated, job_code, store_id) values (ID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, ID); SET ID = ID + 1; SET Var = Var + 1; END WHILE; END; // delimiter ; -- 界定符改回分号mysql> select count(distinct id) from employees ; +--------------------+ | count(distinct id) | +--------------------+ | 200000 | +--------------------+ 1 row in set (16.67 sec)mysql> select count(*) from (select id from employees group by id) t; +----------+ | count(*) | +----------+ | 200000 | +----------+ 1 row in set (15.52 sec)begin; CALL BatchInsert(1, 2000000); commit;CREATE TABLE employees1 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), birth TIMESTAMP, hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL);
DROP PROCEDURE IF EXISTS BatchInser1;
delimiter // -- 把界定符改成双斜杠 CREATE PROCEDURE BatchInsert1(IN init INT, IN loop_time INT) -- 第一个参数为初始ID号（可自定义），第二个位生成MySQL记录个数 BEGIN DECLARE Var INT; DECLARE ID INT; SET Var = 0; SET ID = init; WHILE Var < loop_time DO insert into employees1 (id, fname, lname, birth, hired, separated, job_code, store_id) values (ID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, ID); SET ID = ID + 1; SET Var = Var + 1; END WHILE; END; // delimiter ; -- 界定符改回分号-- 开启事务插入,否则会很慢 begin; CALL BatchInsert1(1, 2000000); commit; Query OK, 1 row affected (1 min 7.06 sec)select t1.lname,count(*) from employees t1 join employees1 t2 using (id) group by t1.lname order by count(*) desc,lname limit 10;CREATE TABLE employees2 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), birth TIMESTAMP, hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL );
DROP PROCEDURE IF EXISTS BatchInser2;
delimiter // -- 把界定符改成双斜杠 CREATE PROCEDURE BatchInsert2(IN init INT, IN loop_time INT) -- 第一个参数为初始ID号（可自定义），第二个位生成MySQL记录个数 BEGIN DECLARE Var INT; DECLARE ID INT; SET Var = 0; SET ID = init; WHILE Var < loop_time DO insert into employees2 (id, fname, lname, birth, hired, separated, job_code, store_id) values (ID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, ID); SET ID = ID + 1; SET Var = Var + 1; END WHILE; END; // delimiter ; -- 界定符改回分号
-- 开启事务插入,否则会很慢
begin; CALL BatchInsert2(1, 2000000); commit;
Query OK, 1 row affected (1 min 7.06 sec)create index idx_employees2_1 on employees2(id);DROP PROCEDURE IF EXISTS select1;
delimiter // -- 把界定符改成双斜杠 CREATE PROCEDURE select1(IN init INT, IN loop_time INT) -- 第一个参数为初始ID号（可自定义），第二个位生成MySQL记录个数 BEGIN DECLARE Var INT; DECLARE ID1 INT; DECLARE vid INT; DECLARE vfname VARCHAR(30); DECLARE vlname VARCHAR(30); DECLARE vbirth TIMESTAMP; DECLARE vhired DATE; DECLARE vseparated DATE; DECLARE vjob_code INT; DECLARE vstore_id INT; SET Var = 0; SET ID1 = init; WHILE Var < loop_time DO select t.id,t.fname,t.lname,t.birth,t.hired,t.separated,t.job_code,t.store_id into vid,vfname,vlname,vbirth,vhired,vseparated,vjob_code,vstore_id from employees2 t where t.id=id1; SET ID1 = ID1 + 1; SET Var = Var + 1; END WHILE; END; // delimiter ; -- 界定符改回分号-- 开启事务查询
begin; CALL select1(1, 2000000); commit;
Query OK, 1 row affected (1 min 10.23 sec)mysql> insert into employees select * from employees; Query OK, 27600000 rows affected (4 min 38.62 sec) Records: 27600000 Duplicates: 0 Warnings: 0 mysql> insert into employees select * from employees; Query OK, 55200000 rows affected (11 min 13.40 sec) Records: 55200000 Duplicates: 0 Warnings: 0 mysql> select count(*) from employees; +-----------+ | count(*) | +-----------+ | 110400000 | +-----------+ 1 row in set (28.00 sec) mysql> select count(distinct id) from employees ; +--------------------+ | count(distinct id) | +--------------------+ | 2000000 | +--------------------+ 1 row in set (1 min 17.73 sec) mysql> select count(*) from (select id from employees group by id) t; +----------+ | count(*) | +----------+ | 2000000 | +----------+ 1 row in set (1 min 24.64 sec)mysql> update employees set lname=lname||'new'; Query OK, 110400000 rows affected, 65535 warnings (21 min 30.34 sec) Rows matched: 110400000 Changed: 110400000 Warnings: 220800000select t1.lname,count(*) from employees t1 join employees1 t2 using (id) group by t1.lname order by count(*) desc,lname limit 10;mysql> create index idx_employees_1 on employees(id); Query OK, 0 rows affected (3 min 49.04 sec) Records: 0 Duplicates: 0 Warnings: 0CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), birth TIMESTAMP, hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL);\timing
insert into employees (id, fname, lname, birth, hired, separated, job_code, store_id) selectID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, IDfrom generate_series(1,200000) id;
INSERT 0 200000Time: 355.652 mscreate or replace function BatchInsert(IN init INT, IN loop_time INT) -- 第一个参数为初始ID号（可自定义），第二个位生成记录个数 returns void as $$ DECLAREVar INT := 0; beginfor id in init..init+loop_time-1 loopinsert into employees (id, fname, lname, birth, hired, separated, job_code, store_id) values (ID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, ID); end loop; end; $$ language plpgsql strict;
db1=# select batchinsert(1,200000); batchinsert -------------
(1 row) Time: 1292.559 ms (00:01.293)db1=> insert into employees select * from employees ; INSERT 0 400000 Time: 322.335 ms db1=> insert into employees select * from employees ; INSERT 0 800000 Time: 835.365 ms db1=> insert into employees select * from employees ; INSERT 0 1600000 Time: 1622.475 ms (00:01.622) db1=> insert into employees select * from employees ; INSERT 0 3200000 Time: 3583.787 ms (00:03.584) db1=> insert into employees select * from employees ; INSERT 0 6400000 Time: 7277.764 ms (00:07.278) db1=> insert into employees select * from employees ; INSERT 0 12800000 Time: 15639.482 ms (00:15.639) db1=> \dt+ employees List of relations Schema | Name | Type | Owner | Size | Description --------+-----------+-------+---------+---------+------------- public | employees | table | user123 | 2061 MB | (1 row)db1=> select count(*) from employees ; count ---------- 25600000 (1 row)
Time: 604.982 msdb1=> select count(*) from (select id from employees group by id) t; count -------- 200000(1 row)
Time: 2982.907 ms (00:02.983)insert into employees (id, fname, lname, birth, hired, separated, job_code, store_id) selectID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, IDfrom generate_series(1,2000000) id;CREATE TABLE employees1 ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), birth TIMESTAMP, hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL);
insert into employees1 (id, fname, lname, birth, hired, separated, job_code, store_id) selectID, CONCAT('chen', ID), CONCAT('haixiang', ID), Now(), Now(), Now(), 1, IDfrom generate_series(1,2000000) id;
INSERT 0 2000000Time: 3037.777 ms (00:03.038)select t1.lname,count(*) from employees t1 join employees1 t2 using (id) group by t1.lname order by count(*) desc,lname limit 10; lname | count ----------------+------- haixiang1 | 129 haixiang10 | 129 haixiang100 | 129 haixiang1000 | 129 haixiang10000 | 129 haixiang100000 | 129 haixiang100001 | 129 haixiang100002 | 129 haixiang100003 | 129 haixiang100004 | 129 (10 rows)
Time: 8897.907 ms (00:08.898)create index idx_employees1_1 on employees1(id); CREATE INDEXTime: 1436.346 ms (00:01.436)do language plpgsql $$ declarebeginfor i in 1..2000000 loop perform * from employees1 where id=i; end loop; end; $$;
DOTime: 9515.728 ms (00:09.516) db1=> select 9515.728/2000000; ?column? ------------------------ 0.00475786400000000000 (1 row)db1=> INSERT INTO employees select * from employees; INSERT 0 27600000 Time: 25050.665 ms (00:25.051) db1=> INSERT INTO employees select * from employees; INSERT 0 55200000 Time: 64726.430 ms (01:04.726) db1=> select count(*) from employees; count ----------- 110400000 (1 row) Time: 7286.152 ms (00:07.286) db1=> select count(distinct id) from employees; count --------- 2000000 (1 row) Time: 39783.068 ms (00:39.783) db1=> select count(*) from (select id from employees group by id) t; count --------- 2000000 (1 row) Time: 14668.305 ms (00:14.668) db1=> select t1.lname,count(*) from employees t1 join employees1 t2 using (id) group by t1.lname order by count(*) desc,lname limit 10; lname | count ----------------+------- haixiang1 | 516 haixiang10 | 516 haixiang100 | 516 haixiang1000 | 516 haixiang10000 | 516 haixiang100000 | 516 haixiang100001 | 516 haixiang100002 | 516 haixiang100003 | 516 haixiang100004 | 516 (10 rows) Time: 33731.431 ms (00:33.731)db1=> update employees set lname=lname||'new'; UPDATE 110400000 Time: 385372.063 ms (06:25.372)db1=> create index idx_employees_1 on employees(id); CREATE INDEX Time: 70450.491 ms (01:10.450)通过以上测试, 在大多数场景中, 阿里云RDS PG相比MySQL的综合性能提升了1个数量级, PG+MySQL结合使用可以大幅降低企业成本. 疫情无情PG有情, 别裁员了, 建立多元化的技术栈, 强化企业IT能力更重要.
更多应用场景和使用方法请参考回顾视频, 包括如何将mysql数据同步到pg(dts):