The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
0answers
46 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
1answer
144 views

mysqlslow - excluding queries from mysqldump

I would like to ignore the queries in my mysql-slow log that originate from mysqldump using SQL_NO_CACHE as the trigger word. My experiences using regex are limited at best and I'm unable to find any ...
1
vote
1answer
4k views

Extremely slow mySql restores

Please bear with me as I ask this question, which looks similar to questions already on DBA@SE. In the current configuration, we have a server (a Linux quadcore box with 4 GB RAM) - Both the Database ...
0
votes
1answer
178 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)?
0
votes
0answers
40 views

Improving slow queries, slow query log attached

I am running MariaDB under Cpanel and hosting Wordpress websites. My slow query log has reached ~400MB in size, and I would like to know how to eliminate the DB issues. Link to the slow query log. ...
0
votes
2answers
82 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
22 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 ...
2
votes
1answer
283 views

Mysqldump without slow query

Situation: I currently monitor an instance of MySQL for slow queries. We have a mysql user dedicated to backups which performs a mysqldump, but the mysqldump gets logged as a slow query. Question: ...
0
votes
1answer
23 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 ...
4
votes
1answer
158 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
73 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....
1
vote
1answer
225 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 ...
1
vote
2answers
591 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 -----------+--------+-------------------------------...
0
votes
1answer
112 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, ...
0
votes
1answer
46 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 ...
2
votes
2answers
1k views

Why is my RDS mysql slow query log full of server restarts?

One of my RDS mysql databases is giving me warnings about the slow query logs taking up significant space. The logs are rotated once an hour, and contain a dozen repetitions of: /rdsdbbin/mysql/bin/...
1
vote
1answer
195 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 ...
2
votes
2answers
206 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
38 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
57 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....
2
votes
2answers
254 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 ...
1
vote
1answer
155 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
411 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 ...
0
votes
2answers
106 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 ...
1
vote
0answers
288 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
45 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,...
0
votes
0answers
50 views

Slow queries while using views in MySQL

We have MyISAM tables on MySQL 5.5 and there is this select * from theView where serial_number = 'QWE12345' or select * from theView where user_id = 1234 kind of queries when searching. When I ...
1
vote
1answer
478 views

Is there a way to run mysqldumpslow against slow queries logged to a table?

AWS RDS by default logs slow queries to a database table. Is there a way to run mysqldumpslow against that table, without exporting it to a file first? Or is there a better way to get a report of ...
0
votes
1answer
83 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 ...
3
votes
3answers
3k views

MySQL Explain has different row count than slow query log

I have this entry in slow query log: # User@Host: user[host] @ [ip] # Thread_id: 1514428 Schema: db Last_errno: 0 Killed: 0 # Query_time: 2.795454 Lock_time: 0.000116 Rows_sent: 15 ...
1
vote
0answers
42 views

Need help in optimizing a Slow Query

I am using userpoints module to provide points for different actions that they do on my site. mysql> desc userpoints; +-------------+---------+------+-----+---------+----------------+ | Field ...
1
vote
0answers
116 views

MySQL: Slow Update Queries

Our database server hangs for exactly 30 seconds on random UPDATE queries. It doesn't matter what field type the query is updating, it just seems like random selection. There are a few applications ...
2
votes
3answers
203 views

How to include Innodb_trx_id in MySQL slow log

Evaluating MySQL's slow query log with pt-query-digest from the Percona Toolkit, I need to group queries by transaction. As it seems, the easiest way to do this is to group by the Innodb_trx_id ...
1
vote
1answer
487 views

Lock time not involved in MySQL slow query selection?

I wish to analyze some read-write contentions problems on a very huge table where writes are very fast, less than 1ms in most case. The point is that the contention makes those write queries sometimes ...
1
vote
1answer
37 views

Request shown in slow logs

In my MYSQL configuration I activated logging of request not using indexes. slow_query_log=1 slow_query_log_file = /home/mysql/sqlprive.log long_query_time=7 log_queries_not_using_indexes =1 But ...
1
vote
2answers
1k views

Mysql. Block/disconnect slow queries

I am experiencing issue. Someone 'attacked' my server: simply by searching the same phrase with multiple requests. As it is text search request and database indices are not used, the engine searches ...
7
votes
1answer
7k views

Slow Queries Not Logging

I am attempting to enable slow query logging on our server in order to identify any queries that could use optimization. Sounds simple enough, however my file is not being written to. I get no errors ...
1
vote
0answers
332 views

MySql Very Slow Writes & Out of memory error

I am having a rds db instance in AWS which is used by Drupal 7 instatllation. I am getting the below error messages in the log: > 130912 11:57:39 [ERROR] /rdsdbbin/mysql/bin/mysqld: Out of memory &...
0
votes
1answer
354 views

Mysql slow.log user specific

I use a slow.log logging on my MySQL server, to catch bottlenecks in my script, but at the same time, i use phpmyadmin on this server. My script and pma has different MySQL user accounts and now, when ...
0
votes
1answer
1k views

Why would mysql “show global status” query be taking 15 minutes?

I'm reviewing the slow log, and on one of my slaves the average time for SHOW GLOBAL STATUS is 914s. Any idea how to determine the cause of this?
3
votes
3answers
3k 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
1answer
988 views

Fast query, in theory using index, still in the slow-query log

I'm having a hard time interpreting the EXPLAIN results of these queries. They both end up in the slow-query log, but the execution time is ~0.0050ms and the final result set is always under 100 rows. ...
1
vote
2answers
7k 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 ...
2
votes
1answer
2k views

Innodb Slow queries since convert from MyISAM

a few days ago we converted some write intensive tables from MyISAM to InnoDB hoping to have a better performance due the better locking system of InnoDB, but instead of gain performance, we start to ...
4
votes
1answer
2k views

Update to session table slow

I have a number of websites with session tables on a web server, these are used by Joomla CMS and Symfony framework systems. With slow_query_log=0.6, the update queries for the session tables come up ...
1
vote
1answer
50 views

simple query is not using indexes

My query is not using indexes. it was taking around 5 seconds. output is 500 rows only. it is a simple query Please find the explain plan and table structure here. Explain Plan mysql> explain ...
8
votes
1answer
10k 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
2answers
2k 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? ...
4
votes
2answers
2k 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 ...
3
votes
2answers
2k views

Enable slow_query_log tries to write to slow_log.CSV

I'm running MySQL 5.5 I wanted to truncate the slow log file, so I did: SET GLOBAL slow_query_log = 0; then truncated the file in filesystem. But, when I enable it back it fails: SET GLOBAL ...