Tagged Questions

The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
0answers
17 views

Intermittent My SQL error: high cpu wio and cannot connect

I have a new deployed MySQL server with following specs: Server: 2CPU x 8 cores, 16GB RAM, 300GB hdd SAS RAID 1 OS: CentOS 5.11 MySQL: 5.1.73 Now I have a very strange and intermittent problem: ...
0
votes
1answer
21 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 ...
2
votes
3answers
312 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
40 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
59 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 ...
1
vote
1answer
1k 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
130 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
30 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 ...
0
votes
2answers
544 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: ...
0
votes
0answers
41 views

How to convert CSV slow query log to old good logfile format?

I received CSV slow log [from mysql 5.1 RDS] and want to analyze it. I prefer to convert it to the old logfile format and when generate report using known tools [mysqlsa, ...]. I found ...
2
votes
3answers
95 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
0answers
203 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 ...
3
votes
3answers
976 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 ...
0
votes
2answers
211 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 ...
1
vote
1answer
441 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. ...
0
votes
2answers
2k 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
1k 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 ...
3
votes
1answer
2k 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
2answers
430 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 ...
2
votes
1answer
1k 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
43 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 ...
0
votes
2answers
1k 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? ...
0
votes
1answer
593 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?
2
votes
2answers
955 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 ...
4
votes
1answer
3k 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
1answer
114 views

What is stored in mysqld-slow.log?

My MySQL log directory contains the following files: 7.9K mysqld.err-old 4.0G mysqld-slow.log 94 mysql.log 93M mysqld.err mysqld-slow.log contains a long list of queries with some ...
2
votes
1answer
1k views

MySQL's “slow query” log: strange “Query_time”

I need some help understanding what's going on with my "slow query" log in MySQL. I have set set global long_query_time=3 and yet this is what I see in the slow query log: # Time: 120723 9:04:38 ...
0
votes
1answer
129 views

Reviewing all new MySql queries on staging environment

The problem I am trying to solve: It's well known that not all developers know what is EXPLAIN in MySql, and not every knows how to use it. Large part of them consider MySql as a black box, no matter ...
0
votes
1answer
183 views

MySQL query took 98 seconds to run but EXPLAIN looks okay

I have a query that took 98 seconds to execute this morning, but I can't recreate the problem now, and the EXPLAIN output isn't giving me any clues. Am I missing something? Here's the SQL: SELECT ...
2
votes
1answer
1k views

Transaction Lock Timeouts When Updating a Row

I'm trying to determine the cause of an InnoDB transaction lock timeout. This one is very strange in that it appears to be a deadlock error instead - nonetheless, here's the output of my slow log ...
1
vote
1answer
1k views

MySQL flush logs, unknown error

I gave been trying to solve this for few days now, and cant seem to figure this out. I have been trying to set log rotation for mysql slow using linux logrotate feature, and my script seems to get ...
1
vote
1answer
237 views

time executing inserts + slow SSH

_ Hello, my setup is a slow SSH to some linux machine. From there I start a mysql terminal client and execute about 200k inserts (in a while loop inside a stored procedure). This takes about 3 hours ...
3
votes
3answers
3k views

MySQL slow log configuration

Is there a way of making MySQL slow logs to start new log file every day ? At the moment its just a single large file, and have to grep lines for every day. It would be much more convenient to have ...
2
votes
2answers
431 views

MySQL: Slow log; append CPU usage

I have the MySQL slow log feature enabled: http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html But sometimes the query_times are high simply due to high CPU load. How can I append the current ...
3
votes
2answers
3k views

Write a slow query to test slow query logging?

Is there a simple query that would take > 2 sec so that I can test the slow query logger? I am looking for something like a generic recursive or iterative statement.
2
votes
5answers
498 views

Help optimizing query in the MySQL slow-query log

Our database is set up so that we have a credentials table that hold multiple different types of credentials (logins and the like). There's also a credential_pairs table that associates some of these ...
2
votes
5answers
2k views

MySQL slow query log: huge query time, no lock time, minuscule number of rows scanned

I have a slow query log item with the following: Query_time: 55 Lock_time: 0 Rows_sent: 739 Rows_examined: 739 No one in my group can explain it besides a guess about packet loss. Is there a ...
4
votes
2answers
1k 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 ...
10
votes
5answers
4k views

Where can I find the mysql slow log?

Just as the title says, where can I see it ? Are there any config options for it ( like how many ms would determine if a query is slow or not ) ?