Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [slow-log]

The tag has no usage guidance.

0
votes
1answer
18 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 ...
2
votes
1answer
36 views

Simple indexed select query takes 350 seconds (?!) even though Filtered 100.00 “Using index condition”

My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different). EXPLAIN SELECT * FROM `...
0
votes
1answer
67 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 ...
-1
votes
0answers
52 views

log_queries_not_using_indexes appears to log queries that ARE indexed

Firstly, I also have long-query-time = 999 to prevent any "slow" queries from showing up. So the only queries that should be in my "mysql_slow.log" file should be those without indexes. But here's ...
1
vote
1answer
58 views

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

Currently long_query_time is set to 5, and that works. However, many queries are part of a transaction, and the slow query log only keeps the final commit;. # Time: 2018-08-15T15:18:18.205309Z # ...
1
vote
1answer
138 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 ...
0
votes
2answers
701 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 ...
0
votes
1answer
42 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 ...
0
votes
1answer
58 views

Very common mysql query sometimes does not use index

As far as I can tell, for some reason a small fraction of very common queries fail to use indexes. If all such queries failed to use indexes, the server would crash within 1 minute hence only a few ...
0
votes
2answers
104 views

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

I am trying to work out if we need to upgrade our server hardware or need to configure the MySQL settings to make things faster. A simple SELECT * From table took 1.6 seconds from the command line. ...
0
votes
1answer
47 views

How to properly debug and inspect SQL Queries in PHP File?

My website AdminPanel has a PHP file which takes almost 30-40 seconds in loading. I doubt the queries which are making the mess, but may be it is the code but the code structure seems perfect. I want ...
2
votes
2answers
817 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, ...
2
votes
2answers
422 views

Speed up Postgres Query that has index but still slow

Have roughly 50 million rows in a table called 'messages'. The problem is even with indexes particular queries run very slow. My background is more Mysql so I'm more of a newb on Postgres (9.5). Any ...
2
votes
1answer
3k 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('...
0
votes
1answer
308 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 ...
5
votes
2answers
2k views

MySQL commits taking time

We are facing an issue, where at certain times there are number of commits that takes a more than a second. The time interval for this is not fixed. Our application sends thousands of commits but at ...
-1
votes
2answers
336 views

optimize wordpress mysql queries for millions of rows

I run a wordpress woocommcerce site with millions of rows in tables. After I made partition, there still some slow queries as below: Question: how can optimize the 3 queries? Query 1 # Query 1: 0....
1
vote
1answer
106 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
1answer
517 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@...
0
votes
1answer
22 views

pt-query-digest slowlog output format looses query cache hit information?

I'm using pt-query-digest (v2.2.20) slowlog output format to filter out queries based on their timestamp, to run further digests on the filtered slowlog : ptqd 11-21.slow.log --output slowlog --no-...
1
vote
1answer
65 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
1answer
62 views

Slow subquery in select join

I have a slow subquery as below (taking 2 min 35.03 seconds) SELECT * FROM erp_gl_batch_item i LEFT JOIN erp_gl_batch b ON i.gl_batch_id = b.gl_batch_id LEFT JOIN erp_customer c ON i....
0
votes
0answers
24 views

Slow performing table

I've got two identical in structure tables which hold different but a similar set of data in two separate databases. table a has 1.178 mil rows ~ 9.6GB table b has 825,000 rows ~ 9.0GB They both ...
2
votes
0answers
268 views

Modify slow query log rotation on Amazon RDS instance

Our Amazon RDS instance rotates slow logs every hour, and keeps only the last 20 of them. This is not optimal for monitoring as it prevents us from seeing any slow query that might have been ...
7
votes
2answers
537 views

COMMIT on top of the pt-query-digest output

I've been optimizing my MySQL server and after tweaking a few queries, I ended up with the COMMIT item on top of the pt-query-digest output, as shown in the extract below: # 322s user time, 770ms ...
5
votes
1answer
169 views

Anemometer - query on analysing the slow logs

I'm new to SQL anemometer. I need to know whether this tool would be able to query to the source database and analyze the slow query logs on real time or not?
3
votes
1answer
556 views

MySQL slow_log table: querying takes forever

I'm running a query on the slow_log: SELECT start_time FROM mysql.slow_log WHERE start_time < TIMESTAMP '2016-01-31 00:00:00' Without a WHERE clause (using a LIMIT), the selects are fast. ...
2
votes
2answers
898 views

MySQL slow log logs every query instead of just slow queries

I have the following MySQL configuration for logging the slow queries: [mysqld] ### Logging options general_log_file = /data/mysql-log/mysql_general.log log-error = /data/mysql-log/...
0
votes
2answers
568 views

Logging sequential scans in PostgreSQL?

Is there a way to log queries which use sequential scans in PostgreSQL? Or just log everything and then later grep for seqscans?
0
votes
1answer
75 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.`...
1
vote
2answers
5k views

Mysql FLUSH LOGS in live server with replication

I need to run the slow query and unindexed query logging for some time on a live production db with replication. Changing GLOBAL const value sounds like a good solution. This article also provides ...
0
votes
0answers
27 views

Is there a way to identify file/process by query string with high query time?

There is a MySQL DB server with high load average goes up and down around 1.50 with many PHP scripts running on. I checked the MySQL slow log file and saw many terrible queries logged. Huge pile of ...
0
votes
1answer
55 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 ...
5
votes
1answer
799 views

Why is this simple query taking more than 1 second?

The table has around 17M rows and is MyISAM. select DateModified,PELTM,PSLTM from financial_key_ratios where SecurityId = 4525 and DateModified >= '2015-01-07'; This simple query seems ...
1
vote
1answer
102 views

Small database size but very slow performance. What can I do?

We have this opencart installation with a database size of roughly 120 MB, 90MB of which is MyISAM and other is InnoDB. Most of the queries like : SELECT DISTINCT *, a.*, vd.seo, p.image, p2s....
4
votes
1answer
9k views

How to set slow query log in MariaDB my.cnf?

It should be easy, but when I put these lines: log_slow_queries = 1 long_query_time = 1 log-slow-queries = /var/log/mysql/slow_query.log log_queries_not_using_indexes in [mysqld], MariaDB fails to ...
0
votes
1answer
597 views

Slow query log on production?

What are the best practices regarding the slow query log? Should it be active in production environment? If so, what would be a reasonable long query time? I know its different for every application, ...
1
vote
1answer
165 views

mysql user host field in slow query log

Mysql shows the following value in the slow query log # User@Host: user[user] @ [127.0.0.1] I'm assuming one if the "user" entries is a proxied login. An admittedly very quick glance of the code ...
1
vote
1answer
4k views

Monitoring SQL Server 2012 slow queries/queries which are causing blocking

I am using SQL Server 2012 and I want to get hold of the queries which are taking longer to execute along with the queries which are causing blocking on the server. When I look at the server for the ...
5
votes
2answers
7k views

postgres Poor performance on ORDER BY “id” DESC LIMIT 1

I have table items with following schema (in postgres v9.3.5): Column | Type | Modifiers | Storage -----------+--------+-------------------------------...
3
votes
2answers
3k views

How to find queries that timed out in MongoDB?

Is it possible to find queries in the system.profile-Collection of a MongoDB-database that did not finish due to a timeout? I found some queries that ran longer than the time specified in $maxTimeMS. ...
1
vote
1answer
47 views

MySQL: not quoting WHERE conditions slows query execution

I just ran into a curious issue with MySQL. I had a very slow query, even if properly indexed, which was like this: SELECT * FROM `tbl1` WHERE `parm1` = 'taleggio' AND `parm2` = 10 As said both ...
2
votes
1answer
126 views

Optimizing a 3 table join query

SELECT job.id_job, job.fk_company, job.fk_user, job.fk_job_category, job.fk_job_type, job.fk_place, job.job_identifier, job.external_job_id, job.title, job.short_description, job.status, job....
6
votes
2answers
22k views

Identify slow query without slow query logs in mysql server

I am wondering is there any other way to to check our slow queries without logging slow query. Suppose, I have a highily busy server can't afford to log much to save memory and I/Os. Then, is there ...
3
votes
1answer
1k views

In slow_log tables, sql_text column showing “throttle: index not used' warning(s) suppressed” instead of exact query

I'm trying to find the slow running queries from slow_log table (as I don't have direct access to server to check slow log file). In sql_text column instead of query it is showing "throttle: index not ...
2
votes
2answers
974 views

Mysql slow query log always include “# Time:”

I am trying to parse the mysql slow log with logstash so it's important that the format is consistent. I have noticed that if statements execute at the same time, the line # Time: yymmdd is only ...
1
vote
2answers
179 views

MySQL slow query log file time elapse

Does anybody know when the mysql slow log file delete slow query records? based on my daily observation. The log file starts at 7am. I have searched for this but nothing answers me clearly. My server ...
0
votes
2answers
630 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)?
3
votes
0answers
993 views

amazon rds logs all queries

I have enabled slow query log on Amazon RDS with following configuration. long_query_time = 0.5 general_log = 0 log_output = TABLE min_examined_row_limit = 1000 slow_query_log = 1 ...
0
votes
2answers
46 views

Is this query supposed to be slow?

Select * from (select tmp.userid userid, tmp.username ,tmp.likescore+tmp.commentscore+tmp.sharescore+tmp.tagscore+tmp.userscore+tmp.lsharescore+tmp.friendscore as score from ( SELECT u.userid,...