29
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 {...
- 5,723
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 ...
- 78.8k
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 ...
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 ...
- 64.1k
5
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 ...
- 1,723
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 ...
- 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 ...
- 231
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 ...
- 40.6k
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
...
- 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 ...
- 31.5k
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';
3
votes
MySQL slow log deletion while slow-query-log is ON
If you unlink the slow log file, but MySQL Server still has an open file descriptor, then the file is not deleted; it's just unlinked from the directory where you previously could see it. The file ...
- 15.4k
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....
- 78.8k
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.
- 29
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 ...
- 78.8k
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 ...
- 23.6k
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 ...
- 32.4k
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,...
- 184k
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 ...
- 184k
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%')...
- 78.8k
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 ...
- 132
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. ...
- 78.8k
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 (...
- 78.8k
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 ...
- 13.6k
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 ...
- 78.8k
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: ...
- 78.8k
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 ...
- 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 ...
- 78.8k
Only top scored, non community-wiki answers of a minimum length are eligible