Skip to main content
Share Your Experience: Take the 2024 Developer Survey
28 votes
Accepted

what is "planSummary: IDHACK"?

It is just informing that mongod has chosen to use the _id index. More specifically: IDHACK means that normal query plan path (evaluation, caching, re-evaluation) was bypassed for the query shape {...
JJussi's user avatar
  • 5,703
10 votes
Accepted

MySQL commits taking time

Since your commits are stalling, it's pretty safe to assume you're running with innodb_flush_log_at_trx_commit=1 (see #4 here), so one obvious change you could do is switch to ...
Aurimas Mikalauskas's user avatar
10 votes
Accepted

Delete Slow Query Log File

As of 5.5.3, FLUSH LOGS will close and reopen the slowlog. (In old versions, FLUSH had no effect on the slowlog.) So, on *nix OS, this should work without restarting the server: rm (to delete) or ...
Rick James's user avatar
  • 78.4k
5 votes

Speed up Postgres Query that has index but still slow

Emulating your setup I think your index is badly maintained. That's about all I can come up with. I tried and emulated everything with 50 M rows. CREATE TABLE messages AS SELECT x::int AS ...
Evan Carroll's user avatar
  • 63.5k
4 votes
Accepted

MySQL slow_log table: querying takes forever

Answer originally left as a comment by Michael - sqlbot: Mostly it's a MySQL thing. The slow query log "table" -- by default -- is actually just a CSV file read by the "CSV storage engine," which ...
4 votes

MySQL slow log logs every query instead of just slow queries

Maybe your mysqld got it's long_query_time value from another source. What happens if you run: select @@long_query_time; Note that I would consider setting the long_query_time to 0 to be good ...
symcbean's user avatar
  • 339
4 votes

Replay (re-execute) MySQL SELECT queries from a log file

I found a solution for my problem. It is a combination of Percona pt-query-digest and Percona playback. First, I filter the slow log by database name and exclude all statements that do not start with ...
Stefan's user avatar
  • 221
4 votes

Why is this Postgres query so slow?

Reading 11,378,536 rows isn't magically fast just because you do it with an index. And that is where the problem is, it has to read that number of rows. We don't know which parts of that complex ...
jjanes's user avatar
  • 40.1k
3 votes

Speed up Postgres Query that has index but still slow

Your query can actually be (a bit) improved by using Evan Carroll's suggestion. Rewrite it in the following way: SELECT "messages"."conversation_id" FROM "messages" WHERE ...
joanolo's user avatar
  • 13.5k
3 votes

Logging sequential scans in PostgreSQL?

I don't know a way to log sequential scans only. Just a small remark: seqscans are not evil, you will get them (especially for smaller tables) all the time. On the other hand, you can definitely log ...
András Váczi's user avatar
3 votes

Delete Slow Query Log File

or set SET GLOBAL slow_query_log = 'OFF'; then clear the file and then SET GLOBAL slow_query_log = 'ON';
Girish Pinjarla's user avatar
3 votes

How to manage a growing MySQL slow_log table?

First of all, I discourage you from using the slow log with TABLE output, in production. The reason is that writes are locking, which limits the concurrency of your workload. Moreover, CSV stored ...
Federico Razzoli's user avatar
2 votes

optimize wordpress mysql queries for millions of rows

Query 1: My condolences, your finger must have fallen off from clicking the Next button to get to OFFSET 5261200 !! OK, so maybe some search crawler followed that button. (Google has sturdy fingers....
Rick James's user avatar
  • 78.4k
2 votes

Queries logged in the 'Slow query log', and 'start transaction' vs 'set autocommit = 0'

Mysql run with autocommit feature , it look like your application code forcefully commit the each DML.
AmitPatel's user avatar
2 votes
Accepted

MariaDB database migration - hangs infinitely while MySQL works no problem

WordPress has an inherent performance problem due to using the EAV schema pattern. Then it fails to adequately index its meta tables. I discuss that here , but since you have an extra column, I will ...
Rick James's user avatar
  • 78.4k
2 votes
Accepted

MySql GROUP BY, ORDER BY LIMIT query taking more than 5000 seconds

I would try an index for: location, abc_key, a_time If you want to stretch that a bit and create a covering index location, abc_key, a_time, val Hovever, a table without a primary key is a bit ...
Lennart - Slava Ukraini's user avatar
2 votes

Can a query that executes quickly still be a poorly written query?

Simply put: yes. Something being fast does not mean it is necessary good as there are other factors, some objective and some very subjective, to consider. Some examples: The query could be ...
David Spillett's user avatar
2 votes

mysqldump select queries appearing in slow query log after upgrade

By default, MariaDB's mysqldump will log queries (--log-queries enabled by default) to the slow log when executing the reload of a dump. The MariaDB doc says: When restoring the dump, the server will,...
RolandoMySQLDBA's user avatar
1 vote

Monitoring slow queries on large database (Percona 5.6)

You can use pt-query-digest --processlist While there are four different ways to use it, IMHO the best way to use it is to poll the processlist and collect metrics every hour. This method does not ...
RolandoMySQLDBA's user avatar
1 vote

Slow SQL Queries

These defy optimization: OR, NOT IN ( SELECT ... ), <>. See if you can reformulate the query to avoid some of those. The only hope I see is in AND (LOWER(issues.subject) LIKE LOWER('%255392%')...
Rick James's user avatar
  • 78.4k
1 vote
Accepted

Mariadb slow_query_log keeps disabling

Answering my own question. It turned out to be ClusterControl. Despite having Performance Schema enabled & configured, ClusterControl's query monitor was starting & stopping slow_query_log as ...
Mark S's user avatar
  • 122
1 vote
Accepted

SELECT 180 records took 2 seconds

On an ordinary spinning drive, you can get about 100 IOPs. If all 180 rows needed to reach off-row to get items and none needed for template, then that is 180 + miscellany to get SELECT * 180 times. ...
Rick James's user avatar
  • 78.4k
1 vote

Is 1.6 seconds a typical time to fetch nearly 3.7 million rows from a table?

Both 1.6s and 7s seem reasonable. Some issues that lead to variations: Caching (Was I/O needed, or was everything sitting in RAM -- a factor of 10 variation is possible) Distance to other server (...
Rick James's user avatar
  • 78.4k
1 vote

high writelog wait on OLTP system and fast SSD storage, log flush is slow

This is not an answer for 'what is the solution from now on' but more of how to identify the problem. Transaction log is not like data pages in terms of how it is hardened to disk. Transaction log is ...
SqlWorldWide's user avatar
  • 13.2k
1 vote

What causes temporary slow (fulltext) queries?

(Too long for Comment. These may help debug the situation.) SHOW TABLE STATUS LIKE 'topics_search' Some of the metrics may be useful. SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; Is that about ...
Rick James's user avatar
  • 78.4k
1 vote

COMMIT on top of the pt-query-digest output

Forensics... Median time for your COMMITs: 16ms. Typical time for spinning disk write: 10ms. Default setting of innodb_flush_log_at_trx_commit: 1, meaning flush to disk on every commit. Conclusion: ...
Rick James's user avatar
  • 78.4k
1 vote

Anemometer - query on analysing the slow logs

From the documentation: Anemometer is a tool for visualizing collected data from the MySQL Slow Query Log. The name comes from the instrument in a weather station that measures wind speed. SQL ...
joanolo's user avatar
  • 13.5k
1 vote

MySQL slow log logs every query instead of just slow queries

log-queries-not-using-indexes = ON I suggest that your tables have no useful indexes. That particular setting is useless, in my opinion. Notice how "Rows examined" is a lot bigger than "Rows ...
Rick James's user avatar
  • 78.4k
1 vote

Delete Slow Query Log File

It's very simple. If you want the slow query log file just take the backup and do below. tail -n slow-query.log > slow-query.log The above file will recreate with 'n' number of line with same ...
Girish Pinjarla's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible