Questions tagged [performance]
An evaluation of whether a system works well enough to be fit for purpose. Normally performance refers to the speed with which a system completes an operation or set of operations over time.
6,376
questions
0
votes
1
answer
1k
views
MySQL- Performance implications of changing a column collation from latin1_swedish_ci to latin1_bin
I am a developer so was hoping someone with DBA experience could help me out here.
I am using a MySQL database (MYSQL 5.6).
I came across a situation where a particular column stores Ids generated ...
CommunityBot
- 1
1
vote
1
answer
454
views
MySQL Database on RDS with memory issues
I am facing high memory utilization issue on RDS MySQL server. We are having other issues too, can someone give me a help?
My database has 120GB, including indexes.
Can anyone help me out to ...
CommunityBot
- 1
0
votes
2
answers
603
views
Fastest way to update column value
I need to set all values of a given column foo to a constant varchar bar. Is there a faster way to do that than with the vanilla UPDATE mytable SET foo = 'bar'?
Facts:
This is an Oracle MySQL 8.0.20 ...
CommunityBot
- 1
3
votes
1
answer
1k
views
MySQL creates tables very slow
On my home machine running Ubuntu 18.04 and MySQL version 8.0.12 with better hardware, table creation, database creation and so on are very slow. On my other computer with older hardware (slower SSD), ...
CommunityBot
- 1
3
votes
1
answer
344
views
TransactionLog not getting truncated in AlwaysOn Cluster
Folks, stuck with a wierd transaction log truncation(reuse) problem in an AlwaysOn cluster and running out of options, the transaction log is not getting truncated and keeps growing.
Only one database ...
CommunityBot
- 1
2
votes
3
answers
696
views
Is it a good practice to use index hints in production code?
I came across a situation where one of my MySQL (5.6) views (type - MERGE) was performing quite bad in order to fetch records (~3500 rows) for an entity id say 123.
This view performs inner joins ...
CommunityBot
- 1
0
votes
2
answers
281
views
SQL Server effectiveness of mirroring partial database
Our database stores 200 TB of data. We've setup clustering options of two VM nodes with one storage but due to limited resources, we cannot replicate the entire database for disaster recovery (only ...
CommunityBot
- 1
0
votes
2
answers
921
views
How to improve performance of frequently updated table in mysql?
my table structure::
CREATE TABLE `gps_device_data`(
`imei_no` bigint(15) NOT NULL DEFAULT '0',
`ioelementpriority` int(1) DEFAULT NULL,
`longitude` int(15) DEFAULT NULL,
`latitude` int(15) ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Does a read replica help if the problem are heavy queries?
The application that our team is responsible for is having ever more often DB performance problems. The application querying it doesn't throw lots of concurrent queries, the problem we have are rather ...
CommunityBot
- 1
0
votes
1
answer
28
views
Migrated to dedicated MariaDB makes app slower
I'm new to dba stackexchange, hope you can help me :-)
I do have a Hetzner root server (EX52-NVMe Intel Core i7-8700 Hexa Core 64 GB DDR4 2× 1024 GB NVMe SSD) and Docker on it. At first, it was ...
- 76.9k
0
votes
1
answer
42
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 ...
- 13.8k
0
votes
1
answer
2k
views
Database ExecuteSQL(MySQL client run out of memory)
I've an online game
the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec
the problem the memory usage still increasing i dun know why ??
i mean if we suppose that ...
CommunityBot
- 1
0
votes
1
answer
44
views
Batch update in a large dataset
We have 3 tables:
Product (product)
Stock (product_Stock)
Price (product_price)
In each table, we have more than 290 bilions of rows. These tables have a compound primary key: product_fk and ...
- 256
0
votes
1
answer
17
views
Network bottlenecks is concentrated on a specific host during query
I have deployed a CoC version of PieCloudDB in a private cloud environment. My S3 service consists of a cluster of three machines. During the deployment of PieCloudDB database, I specified one of the ...
- 1
0
votes
1
answer
25
views
Postgresql - optimizing multicolumn search with "prefix" search pattern as one of the filters
I'm now working on the project that automates "contracts" creation and signing. Recently I received a requirements to implement search functionality for "contracts", so I struggle ...
- 1
21
votes
1
answer
6k
views
Hash keys probe and residual
Say, we have a query like this:
select a.*,b.*
from
a join b
on a.col1=b.col1
and len(a.col1)=10
Assuming the above query uses a Hash Join and has a residual, the probe key will be col1 and the ...
- 82.9k
1
vote
2
answers
72
views
View memory usage by database on an SQL server
My team (no DBAs in the team) is responsible for a database hosted on an SQL server with multiple other databases used by other areas of the business. Memory usage has recently been spiking to 100% on ...
- 5,452
3
votes
1
answer
478
views
What might be the cause of regular periodic slow transactions on an OLTP system?
I have a two node Oracle RAC cluster with that runs an oltp application against it. I have both a production and a duplicate test environment with the same setup. Upon running stress tests on the ...
CommunityBot
- 1
2
votes
1
answer
1k
views
Measuring IOPS for innodb_io_capacity
Aurimas Mikalauskas's 17 Key MySQL Config File Settings gives the following advice regarding innodb_io_capacity and innodb_io_capacity_max:
measure random write throughput of your storage and set ...
CommunityBot
- 1
26
votes
2
answers
137k
views
SELECTing multiple columns through a subquery
I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them.
SELECT
DISTINCT petid,
userid,
(SELECT ...
CommunityBot
- 1
2
votes
1
answer
308
views
How to influence the planner on Postgresql
I have the following:
a complex query, 'match', that returns about 200 rows (ids)
a huge table, 'usernames', indexed on id that maps them to usernames
When I try to left join the query (1) with the ...
CommunityBot
- 1
1
vote
1
answer
2k
views
Getting by list of ids: `unnest() JOIN` vs `= ANY()`
Here I have an array of 42 ids from a table of 800,000 and I want to lookup the rows with those ids.
I know of two ways to do this (besides creating a lot of parameters -- that one is harder to write ...
CommunityBot
- 1
1
vote
2
answers
65
views
Why is Postgres so slow to order those 200 rows that are already ordered?
I have two relatively complex SQL queries which I join using a UNION ALL. Each individual query is fast and returns instantly. The problem is that once joined together they perform terribly bad, and ...
- 38.5k
6
votes
4
answers
2k
views
Does inserting in order have the same effect as clustering?
I have a table that will contain some pre-computed data based off other tables. (Computing on the fly is too computationally expensive given the size of data I have to deal with.) I will be generating ...
- 172k
1
vote
1
answer
576
views
Simple query with a single join very slow
I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary ...
CommunityBot
- 1
0
votes
0
answers
49
views
What is the fastest way to transfer data from SQL Server 2014
I have a Database that is corrupted, backup and restore does not work, the table contains 180 million records with a total size of 1TB.
I need to copy the data from the table to another, but it is ...
- 21
3
votes
2
answers
3k
views
MariaDB freezes with no visible reason
MariaDB server experiences some sort of freezes happening sporadically with no visible reason or connection to outside factors (load, etc). After a thorough investigation the common picture for those ...
- 1,024
0
votes
3
answers
3k
views
mysql simple update with 40 million and 128GB RAM taking too much time
We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows.
and the job runs every day that truncates the table and inserts new data from ...
CommunityBot
- 1
12
votes
3
answers
25k
views
Postgres is performing sequential scan instead of index scan
I have a table with about 10 million rows in it and an index on a date field. When I try and extract the unique values of the indexed field Postgres runs a sequential scan even though the result set ...
- 172k
2
votes
3
answers
8k
views
Postgresql 11 - slow insert performance when table reaches ~5 million records
I have a table with ~6 million records that gets loaded on a postgresql instance in a small machine (with standard PC HDD).
It seems to be a I/O problem, since there are free memory and processing ...
2
votes
1
answer
366
views
Delete using join or using sub-query?
I need to delete rows from a table based on what is present in a temporary table.
For me, both of these statements work :
DELETE from main_table where `id` in (select `deletable_id` from ...
CommunityBot
- 1
0
votes
2
answers
696
views
MySQL - Simple update is very slow and causing high load average with large amount of server
Simple update is very slow and causing high load average with large amount of server :
table name :
CREATE TABLE IF NOT EXISTS `LISTE_PROMOTION3` (
`id_list_prom` int(11) NOT NULL AUTO_INCREMENT,
...
CommunityBot
- 1
1
vote
0
answers
38
views
Querying master.dbo.syslockinfo much slower on new Sql Server machine
After changing the machine running SQL Server 2019 Standard to a new one (Intel(R) Xeon(R) CPU E5-2667 v4 @ 3.20GHz, 128GB RAM, SAS SSD RAID10), I noticed a very significant drop in the performance of ...
- 11
3
votes
2
answers
86
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 ...
- 76.9k
0
votes
0
answers
32
views
How to monitor Postgres workers' utilization?
I have a client who has a legacy application using a Postgres DB. They have SEVERE performance issues, so they figured it might be a good idea to scale their DB server to the GCP's max. Performance ...
- 101
69
votes
5
answers
130k
views
Is it safe to use innodb_flush_log_at_trx_commit = 2
I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
- 17.3k
0
votes
0
answers
17
views
Verify a postgresql index can be dropped securely
Indexes use resources because they have to be updated when data changes.
How to check if an index can be securely dropped (ie. not used at all)?
This request seems to return the unused indexes, ...
- 235
2
votes
1
answer
2k
views
MariaDB Inner join is slow
Here is my SQL (with explain):
EXPLAIN
SELECT * FROM
GBI gbi
INNER JOIN VF_KRED kred ON
gbi.vendor = kred.vendor;
Giving below the outcome:
Now, the select SQL takes ~10 seconds to produce the ...
CommunityBot
- 1
0
votes
1
answer
300
views
mysql Quiz leaderboard filter by points, time taken
I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken.
...
CommunityBot
- 1
1
vote
0
answers
14
views
Performance degradation after upgrading to MySQL 8
We have upgraded MySQL in our application from version 5.7.43 to version 8.0.35.
We are getting below exception while trying the fetch the records from the database.
231104 04:04:03 : com.mysql.jdbc....
- 17.3k
1
vote
2
answers
6k
views
very high Temporary tables created on disk, how to fix them?
Hello we use nextcloud hand use as backend a mysql-db on Debian 10. I run ./mysqltuner.pl which said
Temporary tables created on disk: 99% (48K on disk / 48K total)
(I recently restart the mysql-...
CommunityBot
- 1
0
votes
2
answers
802
views
MySQL - Got an error reading communication packets - high open tables, server goes offline
I am currently testing a product with a couple of my friends before releasing to the public. There's now 10 of us who use the application daily. Yesterday, we noticed a performance error on the MySQL ...
CommunityBot
- 1
1
vote
1
answer
100
views
Rewriting a DELETE statement to reduce pagelatch_ex waits
I have the following TSQL statement, which is often executed in multiple sessions in parallel and leads to serious pagelatch_ex waits:
DELETE dbo.huge_table1
WHERE c1 = 0
AND c3 IN (SELECT v ...
- 301
0
votes
1
answer
642
views
Which database scheme will be better for dating app performance?
I'm creating dating app like Tinder with Spring Boot and want to make sure that even with huge amount of data it will be working with best possible performance.
My tables looks like above. All tables ...
1
vote
1
answer
67
views
Does SQL implementations (or at least SQL Azure) skip obvious duplicate data with joins when returning data?
Say I have:
very wide table A where I need all columns
that I need to join with a small table B that has a LOT of rows.
Of course when I join these two tables I get returned all the content of A for ...
- 256
0
votes
1
answer
645
views
Fastest way to check if record is related to a particular user in postgres
While implementing a sharelikes table I added a procedure to add a new record for a like.
I wanted to add a check before insert that the item being liked does not belong to the users who submitted ...
CommunityBot
- 1
0
votes
2
answers
1k
views
PostgreSQL 10 Optimizing slow query performance
I have the following query:
select i.id as id,
i.ts_updated as updated,
i."name" as name,
cast(attr.data_norm as text) as data_norm,
cast(_59.json_agg as text) as _59, ...
CommunityBot
- 1
0
votes
1
answer
3k
views
MariaDB 10.6 join query 10 times slower than 10.3
I have two VPS servers (LEMP) running Wordpress sites. Specs:
Old
4GB RAM
Ubuntu 18.04
MariaDB 10.3
New
8GB RAM
Ubuntu 22.04
MariaDB 10.6
I migrated a site and found that it was around 10 times ...
CommunityBot
- 1
0
votes
3
answers
1k
views
MySQL Connections slows down my website
I have a website that runs perfectly normal, until today it becomes very slow. After a few hours debugging, it all points down to database.
I ran these queries
show status like 'Connections';
+-----...
CommunityBot
- 1
0
votes
0
answers
50
views
Postgres slow replication debug strategy
I'm using logical replication to move data from server X to Y. We had a hiccup on server X that caused the replication to fall severely behind. We don't know what it was, and right now I'm trying to ...
- 307