mysql - My Insert queries is blocking my select queries. CPU usage of server goes upto 100% for postgres -
i using select , insert queries on table. cpu usage goes upto 100% postgres. how can optimize it
table structure , queries follow
create table ad_request ( request_id bigserial not null, client_id integer default 0, ad_type integer default 0, ad_unit integer default 0, host_url character varying(150) default null::character varying, ip_address character varying(20) default null::character varying, referer character varying(350) default null::character varying, platform character varying(20) default null::character varying, browser character varying(20) default null::character varying, served_campaign_id bigint default 0, status character(1) not null default 'n'::bpchar, created_time timestamp without time zone not null default now(), constraint ad_request_pkey primary key (request_id) ) ( oids=false ); alter table ad_request owner postgres; select served_campaign_id ad_request client_id=1 , ad_type=1 , ad_unit=1 , ip_address='192.168.1.1' , created_time > current_timestamp - interval '24 hour'; insert ad_request(client_id,ad_type,ad_unit,host_url,ip_address,referer,platform,browser,served_campaign_id) values(?,?,?,?,?,?,?,?,?); output of explain analyze seelct query is
seq scan on ad_request (cost=0.00..52629.15 rows=1 width=8) (actual time=245.512..245.512 rows=0 loops=1) filter: ((client_id = 1) , (ad_type = 1) , (ad_unit = 1) , ((ip_address)::text = '192.168.1.1'::text) , (created_time > (now() - '24:00:00'::interval))) total runtime: 245.544 ms (3 rows) and output of insert query is
result (cost=0.00..0.02 rows=1 width=0) (actual time=0.092..0.094 rows=1 loops=1) total runtime: 0.198 ms (2 rows) and ouput of select * pg_stat_activity pga inner join pg_locks pl on pga.procpid=pl.pid;
datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_start | backend_start | client_addr | client_port | locktype | database | relation | page | tuple | virtualxid | tran sactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted -------+---------+---------+----------+----------+----------------------- ------------------------------------------------------------+---------+--------- --------------- -------+-------------------------------+-------------------------------+-------------+-------------+------------+----------+----------+------+-------+------------+----- ----------+---------+-------+----------+--------------------+------+-----------------+--------- 20916 | adtag | 1307 | 10 | postgres | select * pg_stat_activity pga inner join pg_locks pl on pga.procpid=pl.pid ; | f | 2015-04-01 06:00:27.700 012+00 | 2015-04-01 06:00:27.700012+00 | 2015-04-01 04:04:49.810039+00 | | -1 | relation | 20916 | 10969 | | | | | | | | 1/1272802 | 1307 | accesssharelock | t 20916 | adtag | 1307 | 10 | postgres | select * pg_stat_activity pga inner join pg_locks pl on pga.procpid=pl.pid ; | f | 2015-04-01 06:00:27.700 012+00 | 2015-04-01 06:00:27.700012+00 | 2015-04-01 04:04:49.810039+00 | | -1 | relation | 0 | 1262 | | | | | | | | 1/1272802 | 1307 | accesssharelock | t 20916 | adtag | 1307 | 10 | postgres | select * pg_stat_activity pga inner join pg_locks pl on pga.procpid=pl.pid ; | f | 2015-04-01 06:00:27.700 012+00 | 2015-04-01 06:00:27.700012+00 | 2015-04-01 04:04:49.810039+00 | | -1 | relation | 0 | 1260 | | | | | | | | 1/1272802 | 1307 | accesssharelock | t 20916 | adtag | 1307 | 10 | postgres | select * pg_stat_activity pga inner join pg_locks pl on pga.procpid=pl.pid ; | f | 2015-04-01 06:00:27.700 012+00 | 2015-04-01 06:00:27.700012+00 | 2015-04-01 04:04:49.810039+00 | | -1 | relation | 0 | 2671 | | | | | | | | 1/1272802 | 1307 | accesssharelock | t 20916 | adtag | 1307 | 10 | postgres | select * pg_stat_activity pga inner join pg_locks pl on pga.procpid=pl.pid ; | f | 2015-04-01 06:00:27.700 012+00 | 2015-04-01 06:00:27.700012+00 | 2015-04-01 04:04:49.810039+00 | | -1 | relation | 0 | 2676 | | | | | | | | 1/1272802 | 1307 | accesssharelock | t 20916 | adtag | 1307 | 10 | postgres | select * pg_stat_activity pga inner join pg_locks pl on pga.procpid=pl.pid ; | f | 2015-04-01 06:00:27.700 012+00 | 2015-04-01 06:00:27.700012+00 | 2015-04-01 04:04:49.810039+00 | | -1 | virtualxid | | | | | 1/1272802 | | | | | 1/1272802 | 1307 | exclusivelock | t 20916 | adtag | 1307 | 10 | postgres | select * pg_stat_activity pga inner join pg_locks pl on pga.procpid=pl.pid ; | f | 2015-04-01 06:00:27.700 012+00 | 2015-04-01 06:00:27.700012+00 | 2015-04-01 04:04:49.810039+00 | | -1 | relation | 20916 | 11042 | | | | | | | | 1/1272802 | 1307 | accesssharelock | t 20916 | adtag | 1307 | 10 | postgres | select * pg_stat_activity pga inner join pg_locks pl on pga.procpid=pl.pid ; | f | 2015-04-01 06:00:27.700 012+00 | 2015-04-01 06:00:27.700012+00 | 2015-04-01 04:04:49.810039+00 | | -1 | relation | 0 | 2672 | | | | | | | | 1/1272802 | 1307 | accesssharelock | t 20916 | adtag | 1307 | 10 | postgres | select * pg_stat_activity pga inner join pg_locks pl on pga.procpid=pl.pid ; | f | 2015-04-01 06:00:27.700 012+00 | 2015-04-01 06:00:27.700012+00 | 2015-04-01 04:04:49.810039+00 | | -1 | relation | 0 | 2677 | | | | | | | | 1/1272802 | 1307 | accesssharelock | t (9 rows)
Comments
Post a Comment