205 questions
0
votes
0
answers
73
views
Magento2 database table locking error causing site to go offline several times a day
Several times a day a database insert query runs that seems to take a long time and locks up a table, and this in turn causes our site to go offline (error 503). It does not come back up until certain ...
- 1
0
votes
1
answer
51
views
MySQL 8.0 Stored Procedure Showing as Slow Query Due to Total Execution Time
I have a stored procedure that runs daily to archive data from a large table. It moves data in batches of 10,000 rows from table_name to table_archive_table, and deletes the moved rows from table_name....
1
vote
1
answer
158
views
How to optimize query performance in a large fact table with billions of rows?
I'm thinking of building a data warehouse for a retail company, and we have a fact table called Sales that contains billions of rows. The table stores transaction-level data with columns like ...
- 11
2
votes
3
answers
172
views
Laravel Eloquent Query Running So SLOWLY as compared to mySQL counterpart
Why is this laravel eloquent query running so SLOWLY?
I have a query running in a Laravel job that executes very slowly and inconsistently. Sometimes it takes 1-2 minutes to fetch the result, while at ...
- 439
0
votes
0
answers
162
views
AWS RDS MariaDB slow logs empty rows
I enabled the slow query logging on my AWS mariadb RDS as described here. And set up the publishing to Cloudwatch logs.
Parameter group family:
mariadb10.11
My parameters:
log_output = FILE
...
- 65
1
vote
2
answers
71
views
slow query log includes even queries that are fast because it includes time spent waiting for lock
is there a way to exclude the time spent waiting for the lock so I can see only the queries that are actually expensive?
- 1,914
0
votes
1
answer
83
views
How can I optimize the performance of a slow-running query in MySQL?
I have a query that is taking over 30 seconds to run and is causing delays in our application. I've tried indexing the tables and adjusting the query structure, but it's still slow. Are there any ...
1
vote
0
answers
171
views
MySQL AWS Record Locking Timeout not appearing in Slow Query Log?
UPDATED BELOW 11.09.2023
We have a sizeable database of roughly 200,000 lines of code complete with read-replica hosted on AWS. The problem is we're occasionally encountering a deadlock timeout error ...
- 512
0
votes
2
answers
232
views
optimize TIMESTAMPDIFF in mysql query
I need help optimizing a query for large data table
when I test manually it shows fast , but in my slow query log it is logged as taken 10s+ for some reason .
SELECT q.id, q.village_id, q....
-3
votes
2
answers
61
views
How important would indexing be on this large MySQL table with 50k users per day? [closed]
I have a site that gets a lot of traffic this time of year (around 50,000 users a day). The traffic goes up every year and all the sites on my server are starting to crash more, apparently because of ...
- 1,039
0
votes
1
answer
1k
views
How to check Slow Query for MySQL in GCP
Building a MySQL DataBase with GCP's CloudSQL.
To check Slow Query in MySQL, we have enabled the following labels
[Label]
slow_query_log: on
log_output: file
long_query_time: 3
After configuring the ...
0
votes
3
answers
466
views
How to improve slow query performance?
I have a multi-join query that targeting the hospital's chart database.
this takes 5~10 seconds or more.
This is the visual expain using mysql workbench.
The query is below.
select sc.CLIENT_ID as '...
- 163
0
votes
1
answer
77
views
Slow query log - Row_send record count differs with actual records
Recently in my application which uses MariaDB 10.6, I am facing some weird issues where the same query took more than the expected time and consumes more IO at random times.
Enabled slow query to ...
- 345
0
votes
0
answers
42
views
Why in codeignitor 3 mysqli update query taking more then 20 seconds
while debugging when I reach on this point it took some time more then minute.
$this->db->where('userid', $userid);
$this->db->update('tbluser', ['activity' => date('Y-m-d H:i:s'...
0
votes
1
answer
61
views
Slow Log Issue: SP Calls Not Highlighting Against Temporary Table
I have activated slow logs in mysql 5.7.30. I notice some SP's calls are not highlighting in slow logs even its taking more then time to my threshold value which is 2-sec. On investigation, I found ...
- 47
0
votes
1
answer
70
views
Slow Query - Changing it producing odd results
I have a query I am trying to optimize but the results aren't making sense to me.
It takes roughly 7 seconds to complete this:
SELECT users.*,states.name AS state_name, CONCAT_WS(" - ",L1....
- 1,195
0
votes
3
answers
1k
views
MYSQL 8.0.26 using CPU upto 300-400%
See my processes screenshots
I have ubuntu server 20.0 having following VPS configuration
4 vCPU Cores
8 GB RAM
50 GB NVMe
I am also attaching my mysqld.cnf
#
# The MySQL database server ...
- 1,556
2
votes
1
answer
2k
views
MySQL 8 slow query issue
My MySQL 8 conf as below :
[mysqld]
slow_query_log = ON
long_query_time = 5
log_slow_admin_statements = 1
log_queries_not_using_indexes = 1
But when I got slow query log in my table that will show :
...
- 45
0
votes
1
answer
144
views
Mysql query return too slow
I have written a query. It works better. But currently, all tables have 100K rows, and one of my queries returns too slow. Can you please suggest to me how I can optimize the query?
select *
from ...
0
votes
0
answers
48
views
Getting slow query. What column should I index and what type of index?
I'm new to MySQL. I'm getting this slow query below.
# Query_time: 3.357236 Lock_time: 0.000054 Rows_sent: 1 Rows_examined: 107494
# Rows_affected: 0 Bytes_sent: 67
SET timestamp=1652612113;
...
- 173
1
vote
1
answer
1k
views
WP Slow queries / Duplicated queries - images
I have problems with slow and duplicated queries, I'll need assistance, I'm working on this for more than 10h+.
Latest WP version - Latest Flatsome theme and just Query monitor and Woocommerce plugins ...
- 45
0
votes
1
answer
894
views
Getting slow query from SELECT COUNT(*) on WordPress
I have the query below and it gives me a count of 32,000.
SELECT COUNT(*)
FROM wp_term_relationships, wp_posts
WHERE wp_posts.ID = wp_term_relationships.object_id
AND post_status IN (...
- 173
-1
votes
1
answer
406
views
mysql insert too slow and high io/cpu usage some time
the table row is about one hundred million, sometimes the io bps about 150
IOPS about 4k
os version: CentOS Linux 7
MySQL version: docker mysql:5.6
server_id=3310
skip-host-cache
skip-name-resolve
...
- 319
0
votes
0
answers
278
views
How to myke the MySQL Slow Query Log respect the long_query_time?
My Slow Query Log is full of queries, that should not be written there, e.g.:
# Time: 2022-03-11T12:23:29.258308Z
# User@Host: ...
# Schema: ...
# Query_time: 0.022791 Lock_time: 0.000201 Rows_sent: ...
- 15.2k
2
votes
1
answer
1k
views
mariadb slow query not logged
As the title suggests, no log is recorded in the log file even though the related settings have been completed.
slow_query_log_file = /var/log/mysql/mariadb-slow.log
slow_query_log = 1
...
- 33
-1
votes
1
answer
585
views
Slow queries contributing to high CPU consumption
These queries appear in our slow query log and we suspect these may be contributing to a high CPU consumption. Any insight on how to optimize them?
Query 1
Takes title of article and list of keywords ...
- 159
1
vote
1
answer
72
views
Slow simple mysql query
I have a problem with the speed of query. Simple mysql query, but when I have a lot of records (currently > 1 000 000), the performance is really slow. Question is similar to this one, but can't ...
- 900
0
votes
3
answers
3k
views
Slow count query (mariaDB)
I have a problem with the speed of query. Simple mysql query, but when I have a lot of records (currently > 1 000 000), the performance is really slow. Question is similar to this one, but can't ...
- 900
0
votes
2
answers
68
views
Slow join with order query
I have a problem with the speed of query. Question is similar to this one, but can't find solution. Explain says that MySQL is using: Using where; Using index; Using temporary; Using filesort
Slow ...
- 900
0
votes
0
answers
67
views
MySQL - COUNT(*) very slow [duplicate]
i have one table MySQL InnoDB with 130000 rows and the next query is very slow.
SELECT COUNT(id) FROM mytable;
OR
SELECT COUNT(*) FROM mytable;
Query_time: 12.020727
+--------------+--------------+--...
- 349
0
votes
2
answers
2k
views
Amazon RDS - Slow queries at every minute change
We recently noticed that we have a lot more records in our slow query log at every minute change (at around second 0 of every minute). This didn't seem to be related to any of our cronjobs so I ...
- 326
3
votes
4
answers
6k
views
Mysql slow query log Errcode: 13 - Permission denied
I want to enable slow query log.
When I execute the following query
set global slow_query_log = 'ON';
I get :
#29 - File '/var/log/mysqld_slow.log' not found (Errcode: 13 - Permission denied)
I tried ...
- 419
0
votes
1
answer
835
views
MariaDB error and slow query log became empty
So I enabled error log and slow query log on MariaDB and I could see the data in log files. But after a couple of hours when I checked back again then they were empty. It is showing both log files ...
- 3,469
0
votes
1
answer
82
views
How to make mysql-slow.log not telling me that I am not using index?
The table named markets having such columns: id, visible, position, and I created index on visible, every time
and the slow-log always
# Time: 2021-05-12T00:46:14.248654Z
# User@Host: tase[tase] @ ip-...
- 21.9k
0
votes
1
answer
46
views
mysql: why below query unused union index?
table a
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(...
- 319
1
vote
1
answer
126
views
Is there a way to parallelise time.Sleep but keeping the effective execution time in Go?
I'm developing a slow query logs parser package associated with a slow query log replayer in golang. For the replayer, have the following piece of code (in which I added comments for readability):
for ...
- 195
0
votes
2
answers
928
views
SQL gets slow on a simple query with ORDER BY
I have problem with MySQL ORDER BY, it slows down query and I really don't know why, my query was a little more complex so I simplified it to a light query with no joins, but it stills works really ...
- 1,688
1
vote
1
answer
60
views
query is taking long time
SELECT a.sectionid as sectionid, views.timestamp as timestamp,
views.time_on_page as time_on_page, views.video as video,
views.access as access, masterid
FROM (
SELECT m....
user14821698
1
vote
2
answers
3k
views
query logging in MariaDB on Windows 10
How to enable {all type of, not just slow} query logging to FILE in MariaDB Server version: 10.4.11 running on Windows 10 ?
I have modified my.ini file and added these line at the bottom but it did ...
- 17.6k
1
vote
1
answer
456
views
mysqldump cannot backup database
I am trying to backup a small database (15.7 KiB) using the below command line and keep hanging, keep in mind that there is nothing running the background, either no PHP queries:
mysql -u root -...
- 65
0
votes
0
answers
737
views
SQL Insert New Record if not exists else update the existing one
I have 2 tables Staging & Main Table both has the columns SKU and Price. Records are inserted into staging table from there depending on the record its decided whether new record is to be inserted ...
- 109
0
votes
0
answers
32
views
Slow Query Log Examination - Inexplicably slow query
I have something that a slow query log has spit out but not sure what it means. I googled around but found nothing and it does not seem like the log itself gave me anything to go on. It sends no rows ...
- 57
0
votes
1
answer
929
views
Very slow MySQL LeftJoin on AWS RDS, but very quick elsewhere
MySQL in PROD is running through AWS RDS (Aurora, db.t3.small). The resource ultization across the board is well within happy levels.
If I execute the following SELECT, it's almost instanteneous:
...
- 319
0
votes
1
answer
229
views
Filter AuroraMysql slow query log file by specific queries
I downloaded the slow query log file from Aurora Mysql. I want to playback these queries but separate them to just writes and reads. So a tool to extract only selects, or Inserts+Updates would be nice
- 8,235
0
votes
1
answer
783
views
Unable to login as root in mysql
I've been trying out all solutions mentioned in google for this problem to no avail. This website is built using bitnami wordpress stack in AWS. All I want to do is to log slow query. I only have wp-...
- 2,466
1
vote
2
answers
2k
views
Very slow performance on thousands of millions records in MYSQL query
id market_id date keyword sku a b c
1 1 2019-01-01 some text for this QAB-XU-VV 3.1 2.4 3....
- 1,218
-3
votes
1
answer
44
views
MYSQL Query Optimization with subqueries and joins
Hi I have 8 million row data and need to optimize Mysql query to fetch a row from that data. I am using below query but its server response time is too high that creating issue in page loading speed
...
2
votes
3
answers
196
views
MySQL query SLOW Don't know how to optimize
MySQL query SLOW don’t know how to optimize
I think I m fine with hardware 60GB RAM 10 Cores SSD
Hi I m having a big issue with this query running slow on Mysql they query is below:
# Thread_id: ...
- 21
2
votes
0
answers
385
views
Random slow response time making app crash on beanstalk
We have a php app using laravel on elastic beanstalk that's been running for almost 2 years. Since the last 2 weeks(almost 3) we've been having random really high response time which cause the ...
- 290
5
votes
2
answers
5k
views
Are full count queries really so slow on a large MySQL InnoDB tables?
We have a large tables with millions of entrys. A full count is pretty slow, see code below. Is this quite common for a MySQL InnoDB table? Is there no way to accelerate this?
Even with the query ...
- 239