amazon rds – PostgreSQL slow query log entries contain no query details, only duration

I have a PostgreSQL RDS instance hosted in AWS. I put the log_min_duration_statement setting at 1 second. When checking my log for slow queries, I found the following six entries that contain no queries / instructions:

slow request log

Anyone have any idea why this happened? How do you know which slow requests caused these log entries?

postgresql – the index is not used, the filter is too slow

I am analyzing the query which takes several minutes to execute and I want it to be a little faster:

 psql -h localhost -p 3336 -U user -d dbName '-c EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM filings AS filing
    WHERE (
    filing.sources_id != 10 OR filing.action_date < NOW()::date)
    AND (filing_type_id IN (4538,5080))
    ORDER BY filing.action_date desc LIMIT 10 '

Reply:

                                                                                  QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..4124.25 rows=10 width=704) (actual time=128412.522..158528.958 rows=10 loops=1)
   Buffers: shared hit=177017 read=162030 dirtied=81 written=34435
   ->  Index Scan Backward using filings_action_date_idx on filings filing  (cost=0.56..6437894.65 rows=15612 width=704) (actual time=128412.429..158528.832 rows=10 loops=1)
         Filter: ((filing_type_id = ANY ('{4538,5080}'::integer())) AND ((sources_id <> 10) OR (action_date < (now())::date)))
         Rows Removed by Filter: 351983
         Buffers: shared hit=177017 read=162030 dirtied=81 written=34435
 Planning time: 5.454 ms
 Execution time: 158529.062 ms
(8 rows)

I don't understand why my index is useless and the filtering of 300K lines is used instead

Filter: ((filing_type_id = ANY ('{4538,5080}'::integer())) AND ((sources_id <> 10) OR (action_date < (now())::date)))

although the index is in place:

"filings_filing_type_id_sources_id_action_date_idx" btree (filing_type_id, sources_id, action_date)

Instead, this index is in action:

"filings_action_date_idx" btree (action_date)

according to the query plan

Mysql optimizes slow execution queries with many to many table joins

I have the following query with joins to a number of many-to-many junction tables:

profile_language
profile_industry
profile_contract_type
profile_contract_hour
profile_qualification

Execution takes approximately 3 to 4 seconds. When I try the same by excluding the many-to-many junction tables, the query is executed in less than 0.4 seconds.

select distinct `profiles`.*
      , `locations`.`name` as `location_name`
       , `candidate_view`.`last_viewed`
         , CASE WHEN candidate_shortlist.profile_id IS NOT NULL THEN true ELSE false END AS shortlisted
     , CASE WHEN unlocked_profiles.profile_id IS NOT NULL THEN true ELSE false END AS unlocked 
from `profiles` 
inner join `jobseekers` on `jobseekers`.`id` = `profiles`.`jobseeker_id`
inner join `locations` on `locations`.`id` = `profiles`.`location_id` 
inner join `profile_language` on `profile_language`.`profile_id` = `profiles`.`id` 
inner join `profile_industry` on `profile_industry`.`profile_id` = `profiles`.`id` 
left join `profile_contract_type` on `profile_contract_type`.`profile_id` = `profiles`.`id` 
left join `profile_contract_hour` on `profile_contract_hour`.`profile_id` = `profiles`.`id` 
left join `profile_qualification` on `profile_qualification`.`profile_id` = `profiles`.`id` 
left join (SELECT MAX(created_at) AS last_viewed
                , profile_id
             FROM candidate_views
            WHERE recruiter_id = 43 
             GROUP BY profile_id ) AS candidate_view on `candidate_view`.`profile_id` = `profiles`.`id` 
left JOIN (SELECT order_items.purchaseable_id as profile_id
             FROM orders
       INNER JOIN order_items on order_items.order_id = orders.id
       INNER JOIN recruiters on recruiters.id = orders.recruiter_id
            WHERE recruiters.company_id = 37
              AND order_items.purchaseable_type = "App\Profile" ) AS unlocked_profiles on `unlocked_profiles`.`profile_id` = `profiles`.`id` 
left join `candidate_shortlist` on `candidate_shortlist`.`profile_id` = `profiles`.`id` and `candidate_shortlist`.`recruiter_id` = 43 
    where `profiles`.`searchable` = 1 
      and `profiles`.`deleted_at` is NULL 
 order by `profiles`.`id` desc limit 25 offset 0

This is the explanatory information:

+----+-------------+-----------------------+------------+--------+-------------------------------------------------------------------+-------------+---------+----------------------------------+------+----------+----------------------------------------------+
| id | select_type | table                 | partitions | type   | possible_keys                                                     | key         | key_len | ref                              | rows | filtered | Extra                                        |
+----+-------------+-----------------------+------------+--------+-------------------------------------------------------------------+-------------+---------+----------------------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY     | profiles              | NULL       | ALL    | PRIMARY,profiles_jobseeker_id_unique,profiles_location_id_foreign | NULL        | NULL    | NULL                             | 2826 |     1.00 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | jobseekers            | NULL       | eq_ref | PRIMARY                                                           | PRIMARY     | 4       | testjobsdb.profiles.jobseeker_id |    1 |   100.00 | Using index                                  |
|  1 | PRIMARY     | locations             | NULL       | eq_ref | PRIMARY                                                           | PRIMARY     | 4       | testjobsdb.profiles.location_id  |    1 |   100.00 | NULL                                         |
|  1 | PRIMARY     | profile_contract_type | NULL       | ref    | PRIMARY                                                           | PRIMARY     | 4       | testjobsdb.profiles.id           |    1 |   100.00 | Using index                                  |
|  1 | PRIMARY     | profile_contract_hour | NULL       | ref    | PRIMARY                                                           | PRIMARY     | 4       | testjobsdb.profiles.id           |    1 |   100.00 | Using index                                  |
|  1 | PRIMARY     | profile_qualification | NULL       | ref    | PRIMARY                                                           | PRIMARY     | 4       | testjobsdb.profiles.id           |    1 |   100.00 | Using index                                  |
|  1 | PRIMARY     |             | NULL       | ref    |                                                        |  | 4       | testjobsdb.profiles.id           |    2 |   100.00 | NULL                                         |
|  1 | PRIMARY     | profile_language      | NULL       | ref    | PRIMARY                                                           | PRIMARY     | 4       | testjobsdb.profiles.id           |    2 |   100.00 | Using index                                  |
|  1 | PRIMARY     | order_items           | NULL       | ALL    | order_items_order_id_foreign                                      | NULL        | NULL    | NULL                             |    9 |   100.00 | Using where                                  |
|  1 | PRIMARY     | orders                | NULL       | eq_ref | PRIMARY,orders_recruiter_id_foreign                               | PRIMARY     | 4       | testjobsdb.order_items.order_id  |    1 |   100.00 | NULL                                         |
|  1 | PRIMARY     | recruiters            | NULL       | eq_ref | PRIMARY,recruiters_company_id_foreign                             | PRIMARY     | 4       | testjobsdb.orders.recruiter_id   |    1 |   100.00 | Using where                                  |
|  1 | PRIMARY     | candidate_shortlist   | NULL       | eq_ref | PRIMARY,candidate_shortlist_profile_id_foreign                    | PRIMARY     | 8       | const,testjobsdb.profiles.id     |    1 |   100.00 | Using index                                  |
|  1 | PRIMARY     | profile_industry      | NULL       | ref    | PRIMARY                                                           | PRIMARY     | 4       | testjobsdb.profiles.id           |    4 |   100.00 | Using index; Distinct                        |
|  2 | DERIVED     | candidate_views       | NULL       | ref    | candidate_views_profile_id_foreign,Index 4                        | Index 4     | 4       | const                            |   21 |   100.00 | Using where; Using index                     |
+----+-------------+-----------------------+------------+--------+-------------------------------------------------------------------+-------------+---------+----------------------------------+------+----------+----------------------------------------------+

Note that the junction tables are necessary to create a dynamic search query in php and not appear in the example above, but they would be added to the where clause if a search parameter was entered, for example:

and `profile_contract_type`.`contract_type_id` in (1,2,3,4)

In addition, when I modify the request to perform a count, it takes even longer, approximately 4 to 5 seconds, for example

select count(distinct `profiles`.`id`) as aggregate from `profiles`...

How can I optimize this query. Any help appreciated.

If my development machine is slow and has low end specifications, why can't I just rent a virtual machine on Azure?

I need 16 GB of RAM on my development machine to do Android programming, but I can't really afford to buy a new one. So, what prevents me from creating a virtual machine on Azure, from using it only a few hours a day (since it is billed per second) and saving a lot of costs? If I use ad-hoc instances of Azure, I realize substantial savings, because I don't care about 24/7 deployment.

backup – USB3 external hard drive suddenly went slow

The machine is a MacMini mid-2012 which functions as a server (collocated in a remote data center).

The MacMini has an external USB hard drive to provide backup storage (WD Elements Portable 2 TB, powered by USB)

Every night the boot volume (internal SSD) is saved to the external drive using the Carbon Copy Cloner application (with automated task).

From the task history, I can see that the bakcup normally takes around 30 minutes.

The last backup lasted 1 h 45 min.

Although no errors have been reported by CCC, I wonder:

  • what could have caused this?
  • is this an imminent failure signal?
  • what tools can i use to investigate the problem?

I am sure that:

  • the problem is with the external hard drive, not the internal SSD (because the hard drive is then saved on a remote Mac and this operation also took much longer)

  • hard drive is not full (about 50% available)

  • there is almost no load / activity on the server because the nackup takes place late at night when nobody is using the server

networking – network speed is horribly slow only between two specific nodes

I have a VPN in which (mainly two) clients communicate with each other. I realized that the flow between two of the clients is extremely low ((2) -> (1) in the image below) in one direction and later discovered that this problem also occurs without VPN (direct connection between two routers connected to the Internet).

The following image should help understand my infrastructure:

Network speeds

or Ref. is a benchmark for determining the maximum speed for each node (the one where two speed test providers in my case).

(1) is a Raspberry Pi using the latest Raspbian.

The flow of the Raspberry Pi (1) is approximately 20 MBit / s in the local area network in both directions (wireless).

Flow of (2) is also fine from any computer on the Internet in any direction.

The combination described in the picture is the only combination I know of in which the bottleneck appears.

The latency is really stable between the two nodes but I realized during a ping since (1) at (2) this icmp_seq sometimes increases by more than one. Is this an indicator of packet loss?

PING 192.168.4.1 (192.168.4.1) 56(84) bytes of data.
64 bytes from 192.168.4.1: icmp_seq=2 ttl=128 time=58.10 ms
64 bytes from 192.168.4.1: icmp_seq=4 ttl=128 time=48.8 ms
64 bytes from 192.168.4.1: icmp_seq=6 ttl=128 time=71.5 ms
64 bytes from 192.168.4.1: icmp_seq=7 ttl=128 time=66.3 ms
64 bytes from 192.168.4.1: icmp_seq=9 ttl=128 time=58.4 ms
64 bytes from 192.168.4.1: icmp_seq=12 ttl=128 time=53.9 ms

Please let me know what information you need – for now, I have no idea where to look for the problem.

Hostinger website is slow

I have two websites at Hostinger (cheaper plan, 2 separate accommodations). One charges reasonably well while the other often charges very slowly (it … | Read the rest of https://www.webhostingtalk.com/showthread.php?t=1801556&goto=newpost

googlebot – Slow crawl speed for many pages (over 100,000) on a travel website

Context:

We have a relatively new hotel search site where users can freely search their preferences, such as "a child friendly hotel with bunk beds, good breakfast and clean rooms". There will be relevant comments displayed in each of the hotels in the results, according to the concepts mentioned in the request, in this case "child-friendly", "bunk beds", "breakfast", "clean".

We believe our website can offer unique value to travelers, and we can save users time by reading numerous reviews and finding related information. We have identified some concepts that we would like Google to index, for example, we have a referral page for "Boutique Hotels in Chicago, IL". Given the number of cities in the region, we have over 100,000 pages of this type.

however, Google currently indexes our pages at a rate of only ~ 350 pages per day. And more than 100K pages will take a year to be indexed. I would love to hear your suggestions / tips for speeding up the indexing speed.

Currently our ideas to improve the speed of indexing / SEO in general:

  1. Create internal links / improve navigation on the site – Is the creation of internal links important for SEO in this case? how to set up internal links as a hotel search site? (Search seems like a natural way to navigate the results. Perhaps Ariane's thread (city -> concepts, e.g. child-friendly hotel)?)
  2. Add a page on – state our mission and who we are.
  3. Rendered on the server side – the website is currently created in React.js, so Googlebot needs more resources to display each SEO page.

In the long term, we will reach out and build awareness of our website. However, given the current pandemic, we would like to focus more on the website / content itself.

Are there any other suggestions / comments on the above SEO ideas? Thank you very much for your time and your help!

usb on the go – Super slow file transfer via OTG and Android 9 system lag

So I am using a new Lenovo TB-8505X with Android 9 and I have tried several file managers and always when I try to transfer files to one of my USB sticks, the speed is too slow, like a MB and gets even slower and the system starts to lag, when the transfer is complete, the system resumes normal work! At first, it starts to transfer quickly, like instantly 10 to 20% of the file, then it slows down!

On my old Lenovo Tab Essentials with Android 5, everything transfers quickly and the system does not drag! Why this and what can we do about it?

I have already tried to enable USB debugging and all that, to no avail!

Does any of you have this problem, seems to be a pie problem …

Is it even solvable?

The device works perfectly except for this one problem!

P.S. Doesn't that seem like a warranty issue or not?

macos – Apple Podcasts app: downloading / streaming episodes is extremely slow with high failure rates

I recently started using the Apple Podcasts app for macOS and iOS. I find that the download / stream episode is extremely slow, with very high failure rates for macOS and iOS apps. I am using the latest current version of macOS (Catalina 10.15.3) and iOS (13.3.1).

I have tried various ways to try to solve the problem, such as:

  1. Reinstall the iOS app.

  2. Reset network settings on iOS.

  3. Switch to another network / Internet connection on macOS and iOS.

but in vain. I'm curious to understand:

  1. Is this a problem / bug widely identified with the app? I understand that the iOS application and macOS share the same code base. (The macOS app is a Mac Catalyst app, i.e. derived from the iOS code base. Correct me if I'm wrong.)

  2. Are the individual shows / episodes hosted by Apple or is the hosting managed by the content creator / publisher (causing slowdown due to limitation or server side issues)? I have experienced the general slowness of downloading episodes on different podcasts.

  3. If this is a known and widely identified issue / bug with the Apple Podcasts app, has anyone using the developer / public beta known if this potential issue is fixed in macOS 10.15.4 and / or iOS 13.4?