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

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.

Programming Languages

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

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?

Development Tools & Services

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

Development Tools & Services

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?

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.
Web Hosting

- Technology - Languages
+ Webmasters
+ Development
+ Development Tools
+ Internet
+ Mobile Programming
+ Linux
+ Unix
+ Apple
+ Ubuntu
+ Mobile & Tablets
+ Databases
+ Android
+ Network & Servers
+ Operating Systems
+ Coding
+ Design Software
+ Web Development
+ Game Development
+ Access
+ Excel
+ Web Design
+ Web Hosting
+ Web Site Reviews
+ Domain Name
+ Information Security
+ Software
+ Computers
+ Electronics
+ Hardware
+ Windows
+ PHP
+ ASP/ASP.Net
+ C/C++/C#
+ VB/VB.Net
+ JAVA
+ Javascript
+ Programming
Privacy Policy - Copyrights Notice - Feedback - Report Violation 2018 © BigHow