Questions tagged [slow-log]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
1 answer
67 views

Why is this query running so slowly?

My system (OpenMage / Magento 1) is running some queries like the following and even though it's not using any large tables it seems that they take an abnormally high amount of time to execute. ...
0 votes
2 answers
297 views

Block certain queries from being logged in slow query log

I have mysqld_node running on my mysql server to get statistics to be used by grafana + prometheus to graph dashboard for this MySQL server. However, i noticed that 90% of the queries in my slow query ...
0 votes
1 answer
2k views

Periodically very slow execution of simplest queries

Inspecting MySQL slow query log I found out that in certain periods of time there are a bunch of queries in log which are supposed to be very fast but were actually very slow. For example: # User@...
1 vote
1 answer
718 views

MySQL / MyISAM: Queries sometimes very slow, no waiting for lock time

I have a web application using PHP 7.0 and MySQL 5.5.54-0+deb8u1-log. Occasionally, there are long waiting times in the application, which seem to be related to slow database queries. In the slow ...
2 votes
2 answers
186 views

Where to get the error/info logs of what happens when committing several statements?

I had a problem with an index missing on a table and the only way I had to gather clues was this way: BEGIN TRANSACTION; -- OVER 100 DELETES COMMIT; In DBeaver, I selected only the first line and the ...
2 votes
2 answers
1k views

mysqldump select queries appearing in slow query log after upgrade

We recently upgraded from MariaDB 5.5 to MariaDB 10.5 Since doing so, we have a couple of select queries appearing in the slow query log when we backup databases using mysqldump. One of the tables is ...
0 votes
2 answers
62 views

Why is this Postgres query so slow?

I'm trying to debug the slow query below but I'm struggling to understand why it is slow. I can see that both plan and subplan do an index scan, including an "Index only scan" for the ...
0 votes
3 answers
251 views

Identifying root cause of slow query outliers from pt-query-digest?

I've run pt-query-digest and noticed many outliers with very high query times. Many of these queries do not perform slowly when I run them myself, and I'm having trouble figuring out the root cause of ...
0 votes
1 answer
156 views

Set log-slow-verbosity=explain was not working

Trying to enable slow_query with verbosity enabled with different options in Mariadb 10.6.*. But unable to set option- explain for log-slow-verbosity in mysql session https://mariadb.com/kb/en/explain-...
0 votes
0 answers
120 views

MariaDB SELECT query keeps stuck in one system but not in another

This is the reduced query showing problem: SELECT t.* FROM transactions t LEFT JOIN assignments a ON a.transaction_id = t.id LEFT JOIN users u2 ON u2.id = a.user_id WHERE ( u2....
0 votes
2 answers
70 views

Mysql server running as replica doesn't record the slow queries in the slow query log, even though it's enabled

I am running a MySql 8.0.29 server serving as a replica for another server on an ubuntu 20.04.4 LTS box. 'slow_query_log','ON' 'slow_query_log_file','/var/log/mysql/mysql-slow.log' 'long_query_time', ...
19 votes
1 answer
15k views

what is "planSummary: IDHACK"?

This Query scans only one document and returns only one document. But this is very slow: 2017-05-22T07:13:24.548+0000 I COMMAND [conn40] query databasename.collectionname query: { _id: ObjectId('...
3 votes
3 answers
6k views

SQL Server freezes (because of application), need logging

We have an application running ontop of our SQL Server 2005 instance and a couple of times a week this application (unannounced) will cause the SQL Server to freeze. I can't even restart the SQL ...
2 votes
2 answers
1k views

Slow Query logging everything

I have a mysql instance that is logging slow queries. I have it set to 1 sec, but I am getting back lots of these:(example) Query_time: 0.000237 Lock_time: 0.000064 Rows_sent: 0 Rows_examined: ...
14 votes
3 answers
38k views

How do I output MySQL logs to syslog?

I want to use syslog for logging MySQL(5.1.41) on Ubuntu(10.04 LTS). I found information that output error log to syslog. [mysqld_safe] syslog But I want to use syslog for logging general logs and ...
12 votes
3 answers
34k views

MySQL replication - slave is continuously lagging behind master

I am using MySQL-5.1.50 with a Master-slave replication setup. Most of the time the slave is lagging behind the master. When I run show processlist;, there is no query that's taking a long time. ...
7 votes
3 answers
3k views

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

MySQL Benchmarking I would like to evaluate the performance of a different MySQL instance by re-executing real queries from log files. I am aware of tools like mysqlslap, that produces random and ...
0 votes
2 answers
23 views

MySQL slowlog optimsing for Having and Group By Keywords

I have below entry in the slow-log, i just wonder how can i optimise this below query as it looks very simple query that, i don't see any way of optimising it, # Query_time: 32.797535 Lock_time: 0....
0 votes
1 answer
200 views

queryHash field not available in mongos slow query logs

Mongodb V4.2 has introduced queryHash in slow query logs to identify slow queries with same query shape. While this queryHash is available in slow query logs in mongod logs (shard server), it is not ...
3 votes
2 answers
973 views

How To Exclude Queries Returned From mysqldumpslow Command

I would like to ignore queries in the mysql-slow log that originate from running the command mysqldumpslow by using SQL_NO_CACHE as the trigger word. My experience using regex are limited at best and ...
1 vote
2 answers
4k views

How often does the slow_log table get updated in MySQL?

I've changed the long_query_time from 10 secs to 5 secs. I haven't seen any queries logged for a few hours now. Am I supposed to tweak my database for the slow_log table to gets updated more often? ...
1 vote
1 answer
578 views

download mysql slow query log on google cloud sql

I'm having a difficult time locating the log file to download. When running show global variables like '%slow%'; I can see it is located /mysql/logs/slow.log When I log into the instance though ...
0 votes
1 answer
293 views

read slow query log

I'm new to MySQL. MySQL version is 5.0.95. I have enabled the slow query log and edited in the cnf file the location of slow query log but I couldn't see slow query log files in the server location ...
1 vote
1 answer
95 views

mysql upgrade 5.0.45 to 5.1.7 and problem query

Have been working on a migration the past few weeks, and we have run into a mysql specific problem. After creating a new VM, faster server than the old, we have a query that takes approx 2 seconds on ...
2 votes
1 answer
437 views

What causes temporary slow (fulltext) queries?

I logged this slow query on a 400k rows MySQL 5.6 InnoDB table: SELECT SQL_NO_CACHE topic_id FROM topics_search WHERE host_id = 1 AND forum_id IN (23, 50) AND MATCH(topic_text)...
0 votes
2 answers
414 views

Monitoring slow queries on large database (Percona 5.6)

I am looking for a solution to monitor very large production database (many replicas, over 200 tables, some of tables have 10-50 GB of data) with a lot of traffic (a wide set of query complexity). The ...
0 votes
2 answers
2k views

PostgreSQL slow query log entries contain no query details, only duration

I have a PostgreSQL RDS instance hosted in AWS. I have set the log_min_duration_statement setting to 1 second. When checking my log for slow queries I found the following six entries which don't ...
0 votes
2 answers
75 views

Optimize query to not read all rows in table

I have this in my slow query log. Slow Query Log # Time: 2020-03-09T08:42:46.946084Z # User@Host: admin_admin[admin_admin] @ localhost [127.0.0.1] Id: 16 # Query_time: 0.001558 Lock_time: 0....
0 votes
1 answer
139 views

enabling slow_query without general_log

I have been playing a bit to enable slow_query_log, i never got any results until i enabled the general_log (1), but i also get logged ALL the queries, which i do not need, and it causes a performance ...
11 votes
5 answers
23k views

Delete Slow Query Log File

I was logging slow queries of my system. Now I have few queries to be optimized and reset the global slow_query_log variable to 0. Now I want to delete slow query log file mysqld-slow.log. can anyone ...
4 votes
0 answers
64 views

MySQL slow query log not logging all slow queries [duplicate]

I am trying to analyze the hibernate query performance in our application. I tried logging all hibernate queries which take more than 100ms execution time from application side as well as using mysql ...
3 votes
2 answers
1k views

Redirecting MySQL errors and slow logs into another database

I am running a number of MySQL servers as slaves and masters. It is quite annoying to look for slow logs and errors in every server, so I would like to log all the errors into an external database. ...
0 votes
1 answer
65 views

Slow SQL Queries

I'm having problems with my application performance. The tables in the database have the following records: "issues" has more than 334823 "custom_values​​" has over 1350243 "users" has 1350 "projects"...
-1 votes
1 answer
55 views

Can a query that executes quickly still be a poorly written query? [closed]

This is more of a theoretical question and here is some background. Our system uses MySQL and was experiencing some slowdowns late in the day. Running SHOW FULL PROCESSLIST showed me around 10 rows ...
-1 votes
1 answer
103 views

Mysql: slow down with order by, global database problem

i create nasty query for testing reasons. Only for speed testing. EXPLAIN SELECT m.post_id FROM wp_postmeta m LEFT JOIN wp_postmeta mi ON m.`post_id` = mi.`post_id` LEFT JOIN wp_postmeta md ON (md.`...
0 votes
1 answer
67 views

Unexpected slow queries

Using LEMP on popular cloud-service virtual server, with MyISAM tables. The problem is that sometimes once in few hours simple mysql queries (select from where) executing several seconds (5-9) ...
6 votes
2 answers
7k views

MySQL Slow Query Log - SELECT /*!N SQL_NO_CACHE */

Using mysqldumpslow, I can see that the most common entries in a MySQL slow query log are of the following form: SELECT /*!N SQL_NO_CACHE */ from wp_posts (or wp_comments). I can't seem to find much ...
2 votes
1 answer
381 views

MariaDB database migration - hangs infinitely while MySQL works no problem

I've migrated a website from a server running MySQL 5. to MariaDB 10.3, I was expecting if anything for the MariaDB to outperform MySQL and on lots of other sites on the platform it has. However, this ...
0 votes
2 answers
116 views

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

I have a simple table. In which one composite index is created. We have a job which hits select statement on slave server and which takes around 5000 seconds to execute query. Please check table and ...
0 votes
0 answers
271 views

mysql.rds_history query locked for 70 seconds

SELECT count(*) from mysql.rds_history WHERE action = 'disable set master' GROUP BY action_timestamp,called_by_user,action,mysql_version, master_host,master_port,master_user,...
1 vote
0 answers
136 views

MySQL Percrona 8.0 became suddenly slow until restart mysql

My website today suddenly became slow and all requests were taking about 2 seconds to complete. Usually it takes like 0.2 second I checked available space on disk and I have a lot. Memory and CPU ...
0 votes
1 answer
580 views

slow log contains query, but query time is less than long_query_time

mysql> select @@long_query_time; +-------------------+ | @@long_query_time | +-------------------+ | 2.000000 | +-------------------+ 1 row in set (0.00 sec) mysql> select @@...
-1 votes
2 answers
733 views

MySql Not Logging Slow Queries

I've set up slow query logging for MySQL 5.7: [mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow_query_log.log long_query_time = 10 log_queries_not_using_indexes = 0 Then to test ...
11 votes
2 answers
19k views

format of mysql query log

What is the format of the mysql query log? In particular, for lines like: 133 Query commit 133 Query rollback What does '133' stand for? And is it true that each line represents a round trip ...
1 vote
1 answer
740 views

Mariadb slow_query_log keeps disabling

I'm trying to enable slow_query_log on a Mariadb 10.1.32 server. When slow_query_log is enabled & flushed, logging is successful (slow queries are recorded and written to the file) for a minute or ...
0 votes
2 answers
4k views

How to manage a growing MySQL slow_log table?

I see MySQL supports storing slow_log as a table (internally using mysql/slow_log.CSV file), and this appears convenient, but is it a feature I should use in production? I found online articles on the ...
14 votes
1 answer
31k views

Enabling "log_queries_not_using_indexes" disables "long_query_time"?

I noticed that, in Mysql, when enabling log_queries_not_using_indexes, slow_query_log and long_query_time, the generated slow query log file will contain a lot of queries even if the query time is far ...
0 votes
2 answers
2k views

Export MySQL query log to JSON format

Is there any tool which is capable of exporting MySQL query log to JSON format (or any other structured format)?
5 votes
2 answers
3k views

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

we have recently upgraded from SQL server 2008 to new servers and upgraded to sql server 2016 (SP1 CU3) new servers hardware hp proliant 580G8 , 4 socket intel xeon E7 processor, total 60 cores, ...
0 votes
1 answer
64 views

SELECT 180 records took 2 seconds

My table is simple: CREATE TABLE `od_form_items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `formid` int(11) DEFAULT NULL, `items` mediumtext, `template` mediumtext, `draftid` int(11) DEFAULT ...