All Questions
Tagged with mysql performance
1,747 questions
0
votes
1
answer
108
views
How to reduce running time? Execution time exceeded
Excuse my grammar Im dyslexic:
The main issue is that after calculating all the lags and flows at the ISIN level, when I try to do the final grouping by bucket, asset class, and date, I'm forced to re-...
- 11
0
votes
1
answer
31
views
Should I break a large user table into smaller tables for specific roles and information?
I am designing a database for a system that has a users table. Currently, the table has around 50 columns, which include:
Personal information (e.g., name, email, phone_number, address, etc.)
Work-...
- 101
2
votes
1
answer
32
views
Innodb - Can you safely ignore the warning "Memory size allocated for the temporary table is more than 20% of innodb_buffer_pool_size"
We get this warning in our online shop
report.WARNING: Memory size allocated for the temporary table is more
than 20% of innodb_buffer_pool_size. Please update
innodb_buffer_pool_size or decrease ...
- 125
0
votes
0
answers
101
views
Updates to most tables take 0.2ms, updates to a few take 13+ms. Why?
We run a pretty busy 20TB MySQL 8.0.34 running on AWS RDS on an r6i.8xlarge, and about 3 tables are showing slower-than-normal updates that are puzzling me.
Normal single-row UPDATE .. WHERE .. ...
- 129
0
votes
0
answers
18
views
What MySQL configuration is required to enable performance_schema.variables_by_thread?
I am trying to read the performance_schema.variables_by_thread table with the following query, but it always returns an empty set:
mysql> select * from performance_schema.variables_by_thread limit ...
- 143
0
votes
0
answers
57
views
Issue with query performance coming from mysql to mariadb
we were in the process to migrate from Mysql to MariaDB, but we've encountered a performance issue with several of our queries.
The same (already simplified) query
SELECT
count(*)
FROM
vorgang,...
- 9
0
votes
1
answer
39
views
Manage the amount of data on tables
I can have several tables with 10+ millions rows in by year.
I would to know what's the best approach to maintain responsiveness with this amount of datas and I have two ideas.
Don't touch these ...
- 1
0
votes
0
answers
240
views
Random long writes (waiting for handler commit)
I have some INSERT/UPDATE queries who takes a long time randomly.
I try to launch the same INSERT several times with profiling enable.
And I remark that the duration is always in waiting for handler ...
- 1
0
votes
1
answer
58
views
best set of indices
This is a MySQL case with 5,000,000 records in trade table, and 5,000,000 records in registries table.
little cases exist where uni is duplicate. i.e. values of uni is almost different. same comment ...
- 101
0
votes
5
answers
167
views
MySQL solution for many to many associative table that scale over billion entries
Scenario
Imagine we have a table user and an item user.
These 2 tables have an associative table called user_item to define a many to manyrelationship.
We start 100 item records
We have 500 Millions ...
- 151
2
votes
2
answers
420
views
Unable to enable Performance Insights on Amazon RDS db.t3.small instance
I recently upgraded my Amazon RDS instance from db.t2.micro to db.t3.small. After the upgrade, I also activated the performance_schema parameter in the associated parameter group because I've read ...
- 123
1
vote
1
answer
59
views
How to choose hardware for MYSQL Server in Azure - Flexible Server
I have a MYSQL Flexible Server in Azure with the following hardware configuration:
MYSQL 8.0
Compute Tier: Business Critical - 16 vCores
Size: E16ds_v4
Storage: 269 GB
AutoScale IOPS
RAM: 128 GB
I ...
- 21
0
votes
3
answers
127
views
How to best optimize computer for intensive Mysql queries?
I have a series of tasks I run in MYSQL 8 every day. Actually these tasks are run from a vb.net application that interfaces with MYSQL using 8 connections at a time. These tasks are a series of ...
- 23
0
votes
0
answers
28
views
Mysql sharding in depth
I have a table that looks like this. Learning sql recently,
USERID|MATCHID|TOURNAMENTID|SCORE|COUNTRY
There are different tournaments running day to day and each tournament has many matches. some ...
- 37
2
votes
1
answer
624
views
MySql Aurora - how often to run ANALYZE TABLE?
I have a database that has existed for about 10 years, with hundreds of tables, many of which have several million rows.
We don't have a DBA and we have an increasing number of slow queries. We're ...
- 143
0
votes
0
answers
124
views
Why are my Laravel migrations (MySQL) so much faster in MacOS?
This is not so much of a problem as it's mostly just up to some hundreds of milliseconds of difference per migration; but I could not understand why my desktop [Windows 11] with I7-13700K, 64GB RAM, ...
- 113
2
votes
2
answers
338
views
Is there known such big perfomance diff between current MySQL and MariaDB?
I installed Mysql 8.0.36 on Debian 12 VM (name it VM_A), then created for simple performance testing table your_table and procedure prepare_data (inspired SO answer) like this:
CREATE TABLE your_table ...
- 141
0
votes
1
answer
131
views
innodb_buffer_pool_size complications
I had some complications regarding innodb_buffer_pool_size. I read the official documentation, and I found out the formula innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.
so if my ...
3
votes
2
answers
249
views
MySQL chooses a slow execution plan for selecting rows by range condition with ORDER BY, LIMIT and OFFSET clauses
I have a table "client_data" with 2 indexes:
CLIENT_USER_CREATED (CLIENT, USER, CREATED)
CLIENT_CREATED (CLIENT, CREATED)
The primary key is (ID, CLIENT, CREATED)
The table is partitioned ...
0
votes
2
answers
400
views
MySQL struggling to remove tmp tables
Windows Server 2016, MySQL 5.6, PHP 5.6, 64 GB of RAM.
My server is very busy, especially at certain times of the day. I get thousands of queries and sometimes hundreds of connections per second. ...
- 389
1
vote
1
answer
42
views
Slower MySQL queries involving both wp and custom table
Below are less efficient MySQL queries involving wp posts, taxonomies, and a custom movie table search. The custom movies table contains around 30k rows, and wp posts have been generated with the ...
- 141
2
votes
3
answers
421
views
How to manage mysql database table index
I am not aware of how to manage an mysql database table index and I attempted to implement it, but I am uncertain about managing the index for this extensive MySQL database table (Will do it at my end ...
- 141
0
votes
0
answers
33
views
Temporal Attribute using supersededBy vs updating the entity?
Back in the day when databases were on physical disks and seek times are slower, I was told that one performance tweak that can be done is to avoid doing UPDATE or DELETE in a table and instead rely ...
1
vote
1
answer
33
views
RDBMS performance on updating an index key(non primary)
I am creating a scheduler service for which I am using RDBMS(Mariadb, engine:InnoDB), which has following schema for jobs table
CREATE TABLE `jobs` (
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,...
- 111
0
votes
2
answers
70
views
Help trying to optimize a query for active user items (where on a boolean column)
We have a production project running on MySQL with following scheme
[There is obviously many more columns but I omitted irrelevant columns to simplify the question]
# Has approximately 9 million rows
...
0
votes
1
answer
495
views
Can this slow sysbench mysql result be "normal" for consumer hardware?
I have 4 different MySQL 8.0.30 servers on Rocky Linux 9, in one I replaced NVME SSD hoping it will perform better.
I made an image with "Macrium Reflect" software and copied exact same ...
- 204
1
vote
0
answers
19
views
Mysterious Increase in Server Load Every Hour with MySQL 5.7.4
I have a CentOS 7 server with MySQL 5.7.4 installed. The machine has 8 cores and 16 GB of RAM, and only the MySQL service is running. The database is not very large, and there are not many queries. ...
- 23
0
votes
1
answer
161
views
Insert Statement on large MySQL table does not terminate
I have a MySQL database with several tables.
One table (InnoDB) is quite large (about 490 million rows).
The table consists of 4 columns:
id: int(11), auto_increment
term: varchar(255)
docId: int(11)
...
- 3
0
votes
1
answer
48
views
What is a common type of storage hardware for dedicated MySQL servers? [closed]
I am curious what kind of storage hardware is common to see in servers that are dedicated for MySQL ? For example online services like Amazon AWS, Azure, Google, etc that offer(ed) dedicated mysql ...
- 204
0
votes
1
answer
1k
views
MySQL | Measure overall query cost (performance, cpu usage, memory usage)
The Problem
You have a MySQL query, let's say a SELECT and you need to identify its overall cost in all aspect (Host Resource Usage, Query speed / performance ...) so that your result can be ...
- 151
1
vote
0
answers
181
views
Critically high website CPU usage very sudden, persistent upon reboots [closed]
Quick summary. Wordpress community website, large userbase. Has caching for guests (WP Super Cache). Always operates around load of 1-2, or 5-6 at peak hours CPU.
As of 4pm EST on Feb 15, the CPU load ...
- 111
1
vote
2
answers
749
views
MySQL is not using index on query plan with Order By and Limit
I'm trying to understand the behaviour of the MySQL query planner where an index is not used when a query includes Order By and Limit combined.
The table in question is big with around 8 millons of ...
- 11
1
vote
1
answer
100
views
keeping order rows saved to calculate rank and percentile
we have a large table, around 12M records, everyday at midnight,
we run a query to sort all rows by a certain criteria and save the order in a column.
the reason we do that is because we have a ...
0
votes
1
answer
766
views
Do prepared statements give a performance advantage when running multiple times with similar parameters?
I've used prepared statements (MySQL & PHP) for many years, but only with an eye to being safe against SQL injection. I've never re-used a prepared statement, I've always built them up every ...
- 265
0
votes
0
answers
80
views
MySQL occasional performance issues
I am currently working on system with MySQL DB and SpringBoot (Hibernate). Both are located on the same server. All tables use InnoDB. Although there are no slow queries (5+ seconds), there is ...
- 1
0
votes
1
answer
122
views
How to tell if a MySQL query speed is bottlenecked by storage or CPU speed?
Using MySQL 8.0.30 on Rocky Linux 9
For slow MySQL queries in general, not for a speciffic one, is there a way to tell if query speed was bottlenecked by storage speed, cpu speed, or maybe even ram ...
- 204
0
votes
1
answer
83
views
MySQL | Related counter on one to many relation
Problem
Is there a way to automatically count related tables (one to Many), where the count will automatically increment / decrement depending if we add or remove a related item.
So, I could obviously ...
- 151
1
vote
2
answers
1k
views
Very slow DROP INDEX, ADD PRIMARY KEY index
This is probably related to another question I had, but not sure.
So my table url_meta is 25 GB including data and indexes, 70 million records.
Data 18.3 GiB
Index 7.3 GiB
Overhead 5.0 ...
- 204
1
vote
1
answer
675
views
Optimizing 2x 1.8GB tables takes 50 minutes
I am running MySQL 8.0.30 on Rocky Linux 9, cpu is 12 threads AMD Ryzen, 128 GB ram, a decent NVME SSD. And ... I struggle with relatively large databases, currently around 200GB in total, around 20 ...
- 204
1
vote
1
answer
973
views
Calculating MySQL server requirements
I almost didn't post on here as I know this is a somewhat open ended question and I'm in danger of being berated but I've been doing a lot of reading about various things and still don't really have a ...
- 605
2
votes
1
answer
194
views
MYSQL query slow on sorting
I came across an issue, i have the following query:
SELECT
DISTINCT
p.id,
p.title,
p.description
FROM
(
SELECT
`post_id`,
`s_type`
FROM
`search`
WHERE ...
- 21
2
votes
0
answers
148
views
Mysql high Created_tmp_tables
I am seeing some high Created_tmp_tables and I just can't seem to figure out where they are coming from. Those rates apparently cause high Opened_tables aswell. PhpMyAdmin is complaining that ...
- 21
2
votes
1
answer
2k
views
Optimize the number of connections of the connection pool and MySQL
We are using Oracle Cloud's Ampere A1 instance consisting of 4 OCPUs (equivalent to 4 vCPUs) and 24GB of usable memory on Oracle Linux 7.9.
On the server is running an java game server and a web ...
- 23
2
votes
1
answer
1k
views
Why would INSERT, REPLACE or UPDATE statements sometimes take over 1 second to execute?
I have a very good server (great CPU, huge RAM, NVMe) with a huge InnoDB buffer pool that's not full yet.
The website is very write-intensive, but it runs fast enough (SELECT queries well optimized, ...
- 839
1
vote
0
answers
949
views
Does the "max vCPU" limit only apply to the "CPU" wait state?
In the Average Active Sessions chart of Performance Insights for Aurora MySQL 8, there is a horizontal Max vCPU (at 16, in my case). Now I wonder if this limit applies only to the CPU wait state (...
0
votes
2
answers
352
views
Use One Table or Many One-To-One Tables
I am trying my best to learn SQL and I am sorry for the basic question, however, would it make sense to make this table Blog have numerous one-to-one table relationships? I feel that this table Blog ...
3
votes
1
answer
3k
views
How to performance tune high /wait/io/redo_log_flush values in "commit"
According to the "Top SQL" view in AWS Performance Insights, commit tops the list of SQL statements in terms of average active session (AAS).
And most of the time seems to be spent in wait/...
0
votes
2
answers
150
views
Terrible performance on WP-generated query migrating from MySQL to MariaDB
I'm trying to migrate a website from a WP-focused hosting provider that uses Percona for their DB node to a Jelastic-based provider that offers MariaDB for its DB nodes.
There's one query in ...
- 21
-3
votes
1
answer
152
views
Poor query performance depending on the placement of `where` conditions (MySQL)
There is a query, in which changing the ordering of where conditions affects its performance drastically (5k vs 700k rows_examined).
The weird thing is that both versions of the query have the exact ...
- 1
-2
votes
1
answer
137
views
How much changing MySQL datadir from SSD Drive "C:/.." to HDD Drive "D:/.." will slow down the query processing speed?
I am working on my local device and have created a schema that makes drive "C:/" (SSD) nearly full (it took up nearly 60 GB from the available space), so I think I should move the data to ...
user262207