MYSQL slow queries in “slow queries log” - but same queries runs very fast manually


HOME ยป Databases

as title states, in my database various queries appear in the slow queries log, but when I run them manually they run 10x faster.


for example a relatively simple select query with several order by params takes often up to 100 seconds in log(yes table is very big)... but when I run it myself on the same DB it takes 2 seconds or so.


I've examined the server's performance and there doesn't seem to be a particular slowdown or bottleneck at the time, nor are many queries taking long time at that period but just the one.


how can I begin to analyze such a problem?


thanks for the help


Related to : MYSQL slow queries in “slow queries log” - but same queries runs very fast manually
Lots of “COMMIT;” in the PostgreSQL log of slow queries
Databases

I am trying to optimize the PostgreSQL 9.1 database for a Rails app I am developing. In postgresql.conf I have set


log_min_duration_statement = 200

I then use PgBadger to analyze the log file. The statement which, by far, takes up most of the time is:


COMMIT;

I get no more information than this and I am very confused as to what statement this is. Does anyone know what I can do to get more detailed information about the COMMIT queries? All other queries show the variables used in the statement, SELECT, UPDATE etc. But not the COMMIT queries.


MYSQL slow queries in “slow queries log” - but same queries runs very fast manually
Databases

as title states, in my database various queries appear in the slow queries log, but when I run them manually they run 10x faster.


for example a relatively simple select query with several order by params takes often up to 100 seconds in log(yes table is very big)... but when I run it myself on the same DB it takes 2 seconds or so.


I've examined the server's performance and there doesn't seem to be a particular slowdown or bottleneck at the time, nor are many queries taking long time at that period but just the one.


how can I begin to analyze such a problem?


thanks for the help


How to log slow queries in MySQL 5.0.45
Databases

I am trying to turn on logging of slow queries on MySQL v5.0.45 (compile OS: redhat-linus-gnu), but I can't seem to get it working. I tried adding...


long_query_time = 1

to my /etc/my.cnf file as the last option under the [mysqld] section and then I restarted using the command...


sudo /sbin/service mysqld restart --log-slow-queries=/var/log/slowqueries.log

Then I ran programs where I know the SELECT query is taking around 6-8 seconds, but I can't find the log anywhere. Any ideas?


PostgreSQL. Slow queries in log file are fast in psql
Databases

I have an application written on Play Framework 1.2.4 with Hibernate(default C3P0 connection pooling) and PostgreSQL database (9.1).


Recently I turned on slow quries logging ( >= 100 ms) in postgresql.conf and found some issues.


But when I tried to analyze and optimize one particular query, I found that it is blazing fast in psql (0.5 - 1 ms) in comparison to 200-250 ms in the log. The same thing happened with the other queries.


The application and database server is running on the same machine and communicating using localhost interface.


JDBC driver - postgresql-9.0-801.jdbc4


I wonder what

Why does mysql slow log reports these non-slow queries
Databases

My mysql server has long_query_time = 2 configured but I still see these queries reported in slow query log that seem fast:


# Time: 120730 5:06:41
# User@Host: <user> @ <Host> [<IP>]
# Query_time: 0.000412 Lock_time: 0.000060 Rows_sent: 5 Rows_examined: 5
SET timestamp=1343639201;
SELECT album_id FROM `TB_albums` where album_id!='res_4fe4333271bda7.42833845' and deleted is NULL order by `created_time` desc limit 5;

As you can see Query_time: 0.000412 Lock_time: 0.000060 seems way below 2 seconds


Do you have any Idea why these "fast" queries are reported?

Mysql slow queries log
Databases
Mysql slow queries log
 
I'm logging mysql slow queries with this in my.cnf file:
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
Then I noticed that I never had that folder to begin with. I created the folder and it works fine now. But does that mean the log from before was never created because I didn't have that folder or was it sent somewhere else? I heard the default is /var/lib/mysql but I couldn't find it there. Anywhere else I could look for it? Thanks.


Privacy Policy - Copyrights Notice - Feedback - Report Violation - RSS 2017 © bighow.org All Rights Reserved .