MySQL 8.0 Full Index Scan in RAM sluggish

I have a MySQL 8.0 table on Amazon RDS. This table contains ~35 columns, most of which are sizable JSON blobs. The primary key is an UNSIGNED INT32. There are 8M+ rows in this table and has a size of 50GB+.

I ran a simple COUNT(*) on this table with no WHERE clause and it took over 20 minutes. I read online that the PRIMARY key BTREE includes at least 20 bytes for each JSON/TEXT/BLOB column and to try creating a separate index on the primary key. I tried this and it slightly improved performance to 10 minutes for a COUNT(*). EXPLAINing the query shows correctly that a Full Index Scan was used on this new index.

I measured the size of this second index to be ~87MB (using the query in this SO answer https://stackoverflow.com/a/36573801). This makes sense to me as 8M rows * 4 bytes = 31MB and the other 56MB is likely overhead. This entire index would easily fit in RAM, and I expect that even a Full Index Scan would complete fairly quickly (in seconds, not 10 minutes).

My AWS console shows that Read IOPS spikes to the maximum when running COUNT(*). So MySQL reads from disk for some reason, despite the second index easily fitting in RAM. Running the same COUNT(*) query again right after the first did not change time taken at all, so it seems unlikely that it was reading the index into RAM (even if it was, the disk is an SSD so 87MB would load quickly).

What’s causing my query to read from disk? How can I improve performance?

enter image description here

How can i reduce the cost in Bitmap Heap Scan, Filter and Heap Block?

Looks like the query taking time in the second last step in which it mapping index to actually rows so can I optimize this query in any terms, I m not sure if something wrong with the index? and what is Heap Block means is this related to work_mem.

Postgres Configuration: RAM 16GB vCpu 4

Table

CREATE TABLE main.user_resources (     id uuid NOT NULL DEFAULT (md5(((random())::text || (clock_timestamp())::text)))::uuid,     user_tenant_id uuid NOT NULL,     resource_name character varying(50) COLLATE pg_catalog."default" NOT NULL,     resource_value character varying(50) COLLATE pg_catalog."default" NOT NULL,     allowed boolean NOT NULL,     created_on timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,     modified_on timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,     created_by uuid,     modified_by uuid,     deleted boolean NOT NULL DEFAULT false,     deleted_on timestamp with time zone,     deleted_by uuid,     CONSTRAINT pk_user_resources_id PRIMARY KEY (id) ) 

Query:

SELECT "resource_name","resource_value","allowed" FROM "main"."user_resources" WHERE (("user_tenant_id"=abc') AND ((("resource_value" IN ('efg')) OR ("resource_name" IN ('BO','UB')))) AND ("deleted"=false)) ORDER BY "id";  

Index

CREATE INDEX idx_user_resource_user_tenant_id_and_not_deleted     ON main.user_resources USING btree     (user_tenant_id ASC NULLS LAST)     TABLESPACE pg_default     WHERE deleted = false; -- Index: idx_user_resources_resource_name  -- DROP INDEX main.idx_user_resources_resource_name;  CREATE INDEX idx_user_resources_resource_name     ON main.user_resources USING btree     (resource_name COLLATE pg_catalog."default" ASC NULLS LAST)     TABLESPACE pg_default; -- Index: idx_user_resources_resource_value  -- DROP INDEX main.idx_user_resources_resource_value;  CREATE INDEX idx_user_resources_resource_value     ON main.user_resources USING btree     (resource_value COLLATE pg_catalog."default" ASC NULLS LAST)     TABLESPACE pg_default; -- Index: idx_user_resources_user_tenant_id  -- DROP INDEX main.idx_user_resources_user_tenant_id;  CREATE INDEX idx_user_resources_user_tenant_id     ON main.user_resources USING btree     (user_tenant_id ASC NULLS LAST)     TABLESPACE pg_default;  

EXPLAIN ANALYZE

Sort  (cost=3547.20..3556.41 rows=3686 width=64) (actual time=7.080..7.438 rows=4562 loops=1)    Sort Key: id    Sort Method: quicksort  Memory: 834kB    ->  Bitmap Heap Scan on user_resources  (cost=281.12..3328.84 rows=3686 width=64) (actual time=1.070..5.325 rows=4562 loops=1)          Recheck Cond: (((user_tenant_id = 'abc'::uuid) AND ((resource_value)::text = 'efg'::text)) OR ((resource_name)::text = ANY ('{BO,UB}'::text[])))          Filter: ((NOT deleted) AND (user_tenant_id = 'abc'::uuid))          Rows Removed by Filter: 6912          Heap Blocks: exact=2177          ->  BitmapOr  (cost=281.12..281.12 rows=11470 width=0) (actual time=0.834..0.835 rows=0 loops=1)                ->  Bitmap Index Scan on idx_user_resource_mlt  (cost=0.00..4.43 rows=1 width=0) (actual time=0.021..0.021 rows=3 loops=1)                      Index Cond: ((user_tenant_id = 'abc'::uuid) AND ((resource_value)::text = 'efg'::text))                ->  Bitmap Index Scan on idx_user_resources_resource_name  (cost=0.00..274.84 rows=11469 width=0) (actual time=0.812..0.812 rows=11735 loops=1)                      Index Cond: ((resource_name)::text = ANY ('{BO,UB}'::text[]))  Planning Time: 0.214 ms  Execution Time: 7.932 ms (15 rows) 

Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR

We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it’s reasonably fast. When the where clause includes conditions for fields from both tables using an OR the query plan switches to a table scan and doesn’t utilize any of the indexes.

All four fields that are being filtered on are indexed on their respective tables.

Fast query plan where I filter on 3 fields from one table: https://www.brentozar.com/pastetheplan/?id=Hym_4PRSO

Slow query plan where I filter on four fields…three from one table and one from another table: https://www.brentozar.com/pastetheplan/?id=r1dVNDRHO

Ideally I would like to understand why this is happening and how to nudge the query engine to utilize all the indexes.

If the all ports are filtered or closed as result of nmap scan, what would be the next logical step to take?

Is that mean I would need to get a physical access to the device? or is there any other ways to get information, I’m trying to scan my own smartphone and all the ports are closed, and I did the same with my laptop and all the ports were filtered, so, I’m kind of stuck. On the other hand, all the information I found on Internet is relying on getting more results with differents nmap scans, so I guess my real question here is , Is there any other software, teqnique or anything to do that’s not involving nmap? `

(This is not a question about what does mean filtered or closed)

`

Why does my Query use a Bitmap Heap Scan

my simple query is not really fast and I don’t know why my query is using Bitmap Heap Scan.

explain analyze verbose SELECT connections, epoch_time FROM connections WHERE host_name = ‘xyz.z’ ORDER BY epoch_time;

Output:

                                                           QUERY PLAN 

Sort (cost=3257.93..3316.47 rows=23415 width=13) (actual time=8.607..9.624 rows=23259 loops=1) Output: connections, epoch_time Sort Key: connections.epoch_time Sort Method: quicksort Memory: 1859kB -> Bitmap Heap Scan on public.connections (cost=545.88..1558.57 rows=23415 width=13) (actual time=1.341..5.840 rows=23259 loops=1) Output: connections, epoch_time Recheck Cond: ((connections.host_name)::text = ‘xyz.z’::text) Heap Blocks: exact=651 -> Bitmap Index Scan on idx_host_name (cost=0.00..540.03 rows=23415 width=0) (actual time=1.266..1.266 rows=23259 loops=1) Index Cond: ((connections.host_name)::text = ‘xyz.z’::text) Planning Time: 0.171 ms Execution Time: 10.405 ms (12 rows)

The table has 97806 rows and I have created an index on the field epoch_time. The table has 4 columns (id, host_name, connections, epoch_time)

Thanks for your help!

Best regards

Chrome Vulnerabilities are detected in vulnerability scan even after upgraded with latest versions

Had few chrome vulnerabilities [CVE-2020-6420] detected by BI(Retina). Upgraded the affected machines to chrome version 84.0.4147.89. After re-scan still the same vulnerabilities are detected.

Anyone experienced it before ? please help to resolve

I can’t find my switch’s ip by both ARP and ICMP protocols scan with nmap

My switch is a TP-Link TL-SG105E perfectly function, but I can’t access to it because, it’s like hidden somehow from the network. I used tools like Netdiscover:

netdiscover -I wlp2s0 -r 192.168.0.0/24

and Nmap:

nmap -PR 192.168.1.0/24 nmap -SP 192.168.1.0/24

and I tried to find it via open port 80

nmap -p 80 192.168.1.0/24

And many other tests. All of them gave me the same result but not my switch IP or mac address. I’m wondering how it is possible that a fully functional device which previously could identify even the operating system now can’t find it on the network and still work without responding to the ARP ICMP HTTP protocols. It’s not even listed in Router’s DHCP address table. How can it still work? And how can it not be found after scanning the network? And for those who wonder, I hit the reset button and it’s still the same.

Thank you all for your time.