Tag: slow

Just delete 50.000.000 of rows on a PostgreSQL table and DB still very slow

sorry for my poor english.

I have a postgres DB runing on amazon RDS (db.t3.small), with django as a backend. i have made a mistake and created 50.000.000 rows. when i figure out (because queries on that table where ultra slow) i delete it all. but the queries i make on that table stills super slow. it only have 300 rows now.

i have to clean some cache? i have to wait something? the configuration of the RDS in aws is default.

the engine version of postgres is 12.5, also have postgis installed in it.

i check for vacuum issues and run this command:

SELECT relname AS TableName,n_live_tup AS LiveTuples,n_dead_tup AS DeadTuples,last_autovacuum AS Autovacuum,last_autoanalyze AS Autoanalyze FROM pg_stat_user_tables;

the table with the problem says:

'appointment_timeslot', 1417, 0, datetime.datetime(2021, 7, 21, 18, 13, 8, 193967, tzinfo=<UTC>), datetime.datetime(2021, 7, 21, 18, 13, 30, 551750, tzinfo=<UTC>)

check for indexes that Django creates automaticly on that table and i find 4

[ ('appointment_timeslot_pkey', 'CREATE UNIQUE INDEX appointment_timeslot_pkey ON public.appointment_timeslot USING btree (id)') 'appointment_timeslot_home_visit_id_62df4faf', 'CREATE INDEX appointment_timeslot_home_visit_id_62df4faf ON public.appointment_timeslot USING btree (home_visit_id)') ('appointment_timeslot_office_id_6871b47b', 'CREATE INDEX appointment_timeslot_office_id_6871b47b ON public.appointment_timeslot USING btree (office_id)') ('appointment_timeslot_time_range_id_715578fa', 'CREATE INDEX appointment_timeslot_time_range_id_715578fa ON public.appointment_timeslot USING btree (time_range_id)') ]

Why is this IN-clause with subquery materialization slow?

Can someone help me explain why the performance of the two queries are so vastly different? (Setup-Code is at the end, DB-Fiddle is here: https://www.db-fiddle.com/f/eEuPWqR6gZcjbeSeWk4tu2/0)

1.

select id from texts WHERE id not in (select doc_id from details); 
select id from texts WHERE not exists (select 1 from details where details.doc_id=texts.id)

When running the query select id from texts WHERE id not in (select doc_id from details); the query seems to run "forever". The query plan looks like this:

                                     QUERY PLAN                                      ------------------------------------------------------------------------------------  Gather  (cost=1000.00..3703524012.67 rows=400000 width=8)    Workers Planned: 2    ->  Parallel Seq Scan on texts  (cost=0.00..3703483012.67 rows=166667 width=8)          Filter: (NOT (SubPlan 1))          SubPlan 1            ->  Materialize  (cost=0.00..20220.86 rows=799991 width=8)                  ->  Seq Scan on details  (cost=0.00..13095.91 rows=799991 width=8)  JIT:    Functions: 8    Options: Inlining true, Optimization true, Expressions true, Deforming true (10 rows)  Time: 1.319 ms

The costs already hint a much longer execution time, but I do not understand why the costs are that much bigger? Why does the Parallel Seq Scan on texts take so long? What is postgres doing here?

With fewer rows in the tables, I get the following query plan:

explain (analyse) select id from texts WHERE id not in (select doc_id from details);                                                                               QUERY PLAN                                                        -----------------------------------------------------------------------------------------------------------------------  Seq Scan on texts  (cost=11466.00..23744.18 rows=338247 width=8) (actual time=174.488..325.775 rows=10 loops=1)    Filter: (NOT (hashed SubPlan 1))    Rows Removed by Filter: 599990    SubPlan 1      ->  Seq Scan on details  (cost=0.00..9937.20 rows=611520 width=8) (actual time=0.014..56.549 rows=599990 loops=1)  Planning Time: 0.079 ms  Execution Time: 326.372 ms (7 rows)

Why does it degrade that much with more rows? If I understand the output correctly the materialization itself is not the problem. Also how does the second query avoid this problem?

Schema/Data generation:

create table texts (   id bigint primary key,   url text);    create table details (   id bigserial primary key,   doc_id bigint,   content text);       insert into details (doc_id, content) select generate_series(1,800000), 'foobar'; insert into texts (id, url) select generate_series(1,800000), 'something';  # Delete some values delete from details where doc_id IN ( 307531, 630732, 86402, 584950, 835230, 334934, 673047, 772541, 239455, 763671);

Unexpected very slow user count in WordPress

I have a WordPress page that returns timeout error every so often. When reviewing the MySQL "slow queries" log, I could notice that there is an extremely slow query coming from WordPress, but I don’t know exactly which module:

SELECT SQL_CALC_FOUND_ROWS wpe_users.* FROM wpe_users WHERE 1=1 AND wpe_users.ID IN ( SELECT DISTINCT wpe_posts.post_author FROM wpe_posts WHERE wpe_posts.post_status = 'publish' AND wpe_posts.post_type IN ( 'post', 'page', 'attachment', 'wp_block', 'products' ) ) ORDER BY display_name ASC LIMIT 0, 10;

The query appears two or more times, with State Copying to tmp table and Time of up to 53815.44 (secs? 15 hours? it’s crazy!). And it makes the Mysql process consume 557% of CPU and 8.7% of RAM

Server Error and Mysql Process List

The wpe_users table contains almost 3 million entries and the wpe_posts table contains 68,000 entries. The wpe_comments table is also starting to cause problems as it contains 361,000 entries.

I use pagination for comments and posts but not for users. The question is, how can I fix the problem without disabling paging? How can I identify where the user query is coming from if I don’t show users anywhere?

What have I tried?

  • I updated WordPress to the latest version available.
  • I tried to identify where these queries come from using the "Query Monitor" plugin but they don’t appear.
  • I verified the only 3 plugins that I have active and none of them generate such queries.
  • I closed the administration panel and killed said processes to see if the queries were generated by the administration panel but they start again…

I don’t have much experience with MySQL configuration. I am using MySQL 5.5.62 with Plesk panel on a VPS 5GB RAM 6 Cores

Could a slow website cause visitors from a Facebook ads campaign to bounce?

I have a website with traffic problems. The web site sells a product in Spanish.

I recently created a Facebook ads campaign, and it got 96 visits. But Google Analytics only registered 9 visits. I think my website could be slow and people are bouncing. Could this be the case?

I am using WordPress and the Profitbuiler plugin to create the page.

Spatial Query very slow with radius

I posted this before at Query very slow when using spatial with radius which has a lot of details about my problem but I think i didnt include enough data so I am trying here again with database and the query I am having problem tuning.

Download database and attach Database [SQL Server 2019] (I promise no viruses, its just a .bak file in a zip), also scrubbed it out of info i dont want it out there 🙂 https://1drv.ms/u/s!AuxopE3ug8yWm-QTvSxyiHGIrAlXow?e=R7m20G

I shrank the database so its smaller to download, so you must run the following script to rebuild all indexes

EXECUTE sp_msForEachTable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD;'

Run query (Non-Indexed View)

DECLARE @p3 sys.geography SET @p3=convert(sys.geography,0xE6100000010C010000209DE44540FFFFFF3F77CA53C0) SELECT l.* FROM GridListings l WHERE  l.Location.STDistance(@p3) < 15000 ORDER BY createddate OFFSET 0 ROWS FETCH NEXT 21 ROWS ONLY

Or Indexed View

DECLARE @p3 sys.geography SET @p3=convert(sys.geography,0xE6100000010C010000209DE44540FFFFFF3F77CA53C0) SELECT l.* FROM GridListingsIndexed l WHERE  l.Location.STDistance(@p3) < 15000 ORDER BY createddate OFFSET 0 ROWS FETCH NEXT 21 ROWS ONLY

What I am looking for (I am sorry if it is too much, but I am really desperate for help as lot of my queries are timing out on my app which some take between 6-50 seconds on a server with 32gb ram and 6 vcores (hyper v), the server also does other things but I think there is enough horse power

  1. I use the view above which already has non-expired listings filtered, then I use that view to filter down further listings but right now its slow with expirydate set in view and the radius against the view

  2. Look through my indexes and propose better indexes, improvement suggestions overall.

  3. If all fails, i might have to restore to separating my expired and non expired listings into separate tables, but this becomes a nightmare for maintenance

Multiple aggregations in select slow on postgres

I have a table with columns: id, antenna_id, latitude, longitude. There are two composite indexes on (antenna_id, latitude) and (antenna_id, longitude). When I do a max(latitude) for a specific antenna id(s), the speed is acceptable, but doing a min and max for both latitude and longitude at the same time is very slow.

SELECT version()

PostgreSQL 12.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.2.0) 9.2.0, 64-bit

Query

SELECT max(latitude) FROM packets WHERE antenna_id IN (1,2)

Explain 

Finalize Aggregate  (cost=439588.11..439588.12 rows=1 width=32)   ->  Gather  (cost=439588.00..439588.11 rows=1 width=32)         Workers Planned: 1         ->  Partial Aggregate  (cost=438588.00..438588.01 rows=1 width=32)               ->  Parallel Index Only Scan using idx_packets_antenna_id_latitude on packets  (cost=0.57..430103.40 rows=3393839 width=7)                     Index Cond: (antenna_id = ANY ('{1,2}'::integer[])) JIT:   Functions: 5   Options: Inlining false, Optimization false, Expressions true, Deforming true

Duration 

[2021-03-06 12:14:38] 1 row retrieved starting from 1 in 4 s 438 ms (execution: 4 s 400 ms, fetching: 38 ms) [2021-03-06 12:14:51] 1 row retrieved starting from 1 in 2 s 590 ms (execution: 2 s 560 ms, fetching: 30 ms)

The explain looks almost identical for max(longitude), min(latitude) and min(longitude) on their own. Speed is acceptable.

But when I combine the queries

SELECT max(latitude), max(longitude), min(latitude), min(longitude) FROM packets WHERE antenna_id IN (1,2)

Duration

[2021-03-06 09:28:30] 1 row retrieved starting from 1 in 5 m 35 s 907 ms (execution: 5 m 35 s 869 ms, fetching: 38 ms)

Explain 

Finalize Aggregate  (cost=3677020.18..3677020.19 rows=1 width=128)   ->  Gather  (cost=3677020.06..3677020.17 rows=1 width=128)         Workers Planned: 1         ->  Partial Aggregate  (cost=3676020.06..3676020.07 rows=1 width=128)               ->  Parallel Seq Scan on packets  (cost=0.00..3642080.76 rows=3393930 width=14)                     Filter: (antenna_id = ANY ('{1,2}'::integer[])) JIT:   Functions: 7   Options: Inlining true, Optimization true, Expressions true, Deforming true

Question

Why does the second query which does the min and max on the latitude and longitude field not use the indexes? And how can I rewrite the query so that it is faster?

How to avoid alienation by expected but slow mood shift, and still keep players out of spoilers?

I run an MLP campaign. By design, at the start of the campaign what the citizens (PCs) know about the world is true, but it’s not the whole and complete truth, and many issues of the past are either not widely known or just reframed to appear less severe than they are. The campaign revolves around them figuring out How Things Really Are, and becoming ones who keep the surface level of the Utopia running.

And here’s the question. MLP makes people think that they know how things really are. So, over time a player may decide that it’s too dark, or by other means too conflicting with their own vision.

Be it other campaign, we could compare our visions for compatibility beforehand, to make sure that it works.

But this campaign is meant to include perspective shifts; I have a few players that are prone to ‘bleeding’ (and know that!) and/or prefer to stay out of spoilers. The ‘actual state of the world’ has/will have a ‘darker past’; this Utopia is based on a few questionable decisions, and is not as stable as it appears at first. I am afraid of alienating these players, or being met by a reaction of "You asked us to play in the Utopia, and then the mood became totally different". Basically, "I was creating my character for another sort of game, one that you initially described to me; and now it’s a different game, one that I don’t actually like".

How to reduce this risk of alienation, yet still keep the mood of mystery and (classical urban-fantasy) ‘this is deeper than you have thought’, without spoilers?

Slow inserts in mariadb columnstore

I have just started on researching the feasibility of using MariaDB’s columnstore for OLAP, and I find inserts are very slow. This is MariaDB 10.5 on a debian 10 system, just an elderly desktop with 8GB RAM. This is the table, a trigger and the timings:

MariaDB [test]> show create table analytics_test\G *************************** 1. row ***************************        Table: analytics_test Create Table: CREATE TABLE `analytics_test` (   `id` int(11) DEFAULT NULL,   `str` varchar(50) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4 1 row in set (0.000 sec)  MariaDB [test]> show create trigger test_trg\G *************************** 1. row ***************************                Trigger: test_trg               sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger test_trg before insert on analytics_test for each row begin   set new.str=concat('Value: ',new.id); end   character_set_client: utf8   collation_connection: utf8_general_ci     Database Collation: utf8mb4_general_ci                Created: 2021-01-07 11:14:28.81 1 row in set (0.000 sec)  MariaDB [test]> insert into analytics_test set id=1; Query OK, 1 row affected (0.817 sec)  MariaDB [test]> insert into analytics_test set id=2; Query OK, 1 row affected (0.560 sec)  MariaDB [test]> insert into analytics_test set id=3; Query OK, 1 row affected (0.611 sec)  MariaDB [test]> select * from analytics_test; +------+----------+ | id   | str      | +------+----------+ |    1 | Value: 1 | |    2 | Value: 2 | |    3 | Value: 3 | +------+----------+ 3 rows in set (0.085 sec)

I think .5 sec for a simple insert is very slow – compare to the same table in innodb:

MariaDB [test]> show create table test\G *************************** 1. row ***************************        Table: test Create Table: CREATE TABLE `test` (   `id` int(11) DEFAULT NULL,   `str` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.000 sec)  MariaDB [test]> show create trigger test_trg1\G *************************** 1. row ***************************                Trigger: test_trg1               sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger test_trg1 before insert on test for each row begin   set new.str=concat('Value: ',new.id); end   character_set_client: utf8   collation_connection: utf8_general_ci     Database Collation: utf8mb4_general_ci                Created: 2021-01-07 11:45:07.85 1 row in set (0.000 sec)  MariaDB [test]> insert into test set id=1; Query OK, 1 row affected (0.010 sec)

Is there anything I need to do in order to make inserts perform better?

GSA works so slow

Hi guys,
I have some own site list. I gathered it with gsa, then delete bad domains and now i have about 1200 domains. I want to use them with gsa. I clicked import target url from file and chose my list. But gsa dont use this list and dont work. What can i do? Please help me. I try to built good backlings to my tier 2

MariaDB (MySQL) slow query when primary key range combined with fulltext index

I’ve a table described below, with two columns – integer primary key and title text – currently holding circa 3 million records. As seen in the metadata below, there’s a BTREE index on integer primary key column, and FULLTEXT index on title column.

MariaDB [ttsdata]> describe records; +------------------+---------------------+------+-----+---------------------+-------------------------------+ | Field            | Type                | Null | Key | Default             | Extra                         | +------------------+---------------------+------+-----+---------------------+-------------------------------+ | id               | int(15) unsigned    | NO   | PRI | NULL                | auto_increment                | | title            | varchar(2000)       | YES  | MUL |                     |                               | +------------------+---------------------+------+-----+---------------------+-------------------------------+  MariaDB [ttsada]> show index from records; +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table   | Non_unique | Key_name                | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | records |          0 | PRIMARY                 |            1 | id               | A         |     2798873 |     NULL | NULL   |      | BTREE      |         |               | | records |          1 | title                   |            1 | title            | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |               | +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

I’d like to run the following query:

SELECT SQL_NO_CACHE * FROM records WHERE   id > 1928177 AND   MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200

This query takes more 5 seconds to execute. When I remove the the range part or the fulltext part, in both cases the query executes in circa 100 ms. Below is analysis of individual queries, the last one being the one I want to use.

I’m new to MySQL and DBA in general. I’ve posted EXPLAIN statements but I have no idea how to draw any conclusions from them. I assume that the query is slow because the range filtering happens on data set obtained from full text query.

So my question is: How can I make the query fast?

The 1928177 magic number is something that just happens to be needed.

Query 1

SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200 
MariaDB [ttsdata]> explain SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200; +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ | id   | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | Extra                 | +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ |    1 | SIMPLE      | records | range | PRIMARY       | PRIMARY | 4       | NULL | 227183 | Using index condition | +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ 1 row in set (0.005 sec)  MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200; ... 200 rows in set (0.108 sec)

Time: 0.108 sec

Query 2

SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200 
MariaDB [ttsdata]> explain SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ | id   | select_type | table   | type     | possible_keys | key   | key_len | ref  | rows | Extra       | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ |    1 | SIMPLE      | records | fulltext | title         | title | 0       |      | 1    | Using where | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ 1 row in set (0.007 sec)  MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; ... 200 rows in set (0.138 sec)

Time: 0.138 sec

Query 3

SELECT SQL_NO_CACHE * FROM records WHERE   id > 1928177 AND   MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200 
MariaDB [ttsdata]> explain SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ | id   | select_type | table   | type     | possible_keys | key   | key_len | ref  | rows | Extra       | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ |    1 | SIMPLE      | records | fulltext | PRIMARY,title | title | 0       |      | 1    | Using where | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ 1 row in set (0.005 sec)  MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; ... 200 rows in set (5.627 sec)

Time: 5.627 sec