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,424
questions
0
votes
3
answers
173
views
Retrive MS SQL Server performance
I need to measure the performance of a MS SQL database, possibly with a query.
There is a way to obtain the average execution time of the more heavy query in a certain period of time?
- 123
1
vote
1
answer
730
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
329
views
Parameter Sniffing in SQL Server 2022
I'm working on the SQL Server performance area and I want to know that is the Parameter Sniffing problem resolved in SQL Server 2022 completely ?
What trace flags are useful in this area ?
Thanks in ...
- 149
2
votes
0
answers
46
views
For selling items in a game, should the shop and item tables be merged?
I'm developing a game that uses a Postgres database. In the game players can have many different items, and each player can have multiples of an item (example: 200 apples). My item table currently ...
- 121
2
votes
2
answers
147
views
How to design a table for semi-structured data?
I have the following record and I am trying to store it in a table.
{
"properties": {
"visitor_id": "0184d099_n00mPLKQT"
},
"flags": {
"5001&...
- 121
0
votes
1
answer
1k
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 ...
2
votes
1
answer
143
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
100
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
145
views
Problems seen in production due to high replication factor
Has anyone run a fully replicated Cassandra cluster in production? We have a business use case that essentially requires the dataset stored by Cassandra to be fully replicated. This would require us ...
2
votes
0
answers
163
views
deadlock on too many requests and DoS occurred, Why?
I ran into a strange monitoring DBA error "Deadlock" when I try to Pentest my web app. (Send lots of request to DB then Deadlock Occurred.)
Why did this occur?
I add the Deadlock XML Too:
...
- 121
2
votes
1
answer
1k
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
1
vote
1
answer
206
views
MySQL 5.7 --> MariaDB 10.8 switch: mysqlcheck --check --extended hangs on table
Recently I switched from MySQL 5.7 to MariaDB 10.8 and it's been generally great as a drop-in replacement, but I've run into a problem: the process for a cron job that daily runs
/usr/bin/nice /usr/...
3
votes
1
answer
430
views
Performance problems in SQL Server. Parameter Sniffing or not in my scenario?
Here I am facing a performance problem that occurs with particular statements in a stored procedure, that has many statements, on occasion
SP is executed once a second, normally completes less than 50 ...
- 5,589
0
votes
1
answer
104
views
AUTOMATIC_TUNING SQL Server
IS it possible to force the correct plan in the stored procedure syntax?
because current I found the option only at the base level
ALTER DATABASE TEST SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON );...
3
votes
1
answer
627
views
What are the performance implications of using checksums vs change tracking for SQL Server
Back in the day, an implementation with checksum_agg was made to check if "something changed" in a table in a SQL Server table, to then make updates in another database. The users want to ...
- 133
2
votes
1
answer
447
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, ...
- 809
0
votes
3
answers
755
views
Running sp_updatestats probably overrides manually done UPDATE STATISTICS WITH FULLSCAN and ruins performance
We experience this on both SQL Server 2014 and SQL Server 2019.
We have an application, which loads some data into our database. At the end of this load process, the application runs sp_updatestats ...
- 665
1
vote
0
answers
695
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
0
answers
104
views
Best practice to address high CPU usage in a shared db server
Newbie here.
Suppose i have a db server (any db system, mysql, mongo, cassandra...) shared by two applications with one table of each. As time goes by the two tables have higher and higher traffic and ...
0
votes
2
answers
243
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
2k
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
1
answer
186
views
Accelerated Database Recovery SQL Server
I have ADR enabled on my production database running on SQL Server 2019. In the SQL Server logs , it shows the messages:
[VersionCleaner][DbId:6][sweepOfSelectivePages]: Cleanup of sidelist
aborted ...
0
votes
1
answer
144
views
More efficient calculations of stock splits
I am storing basic end of day (EOD) stock values and need to calculate splits occasionally. The current SQL is extremely slow and consumes a large amount of temp space.
My database is Postgres14
The ...
- 27
-1
votes
1
answer
505
views
SQL optimization for XML Path [duplicate]
SELECT distinct STUFF(
(SELECT distinct ',' + hg.CODE
FROM SP_HD hg
Inner join Purchase.SP_DT on GOODS_RECEIPT_ID = hg.ID
WHERE PO_DT_ID = d.PO_DT_ID
FOR XML PATH(''), TYPE
)....
- 67
1
vote
1
answer
563
views
Query performance tuning on partitioned table
I have a simple table partitioned by Date for each day
so ranges are like ('2023-03-07 23:59:59.997', '2023-03-08 23:59:59.997') etc
with about few millins records per each range
the table is as ...
- 13
0
votes
2
answers
152
views
I tried to optimize my PG. The results are decent. Did I do wrong somewhere? Can I do better?
I have a PG database with around 1B rows. I run queries to see how good is the performance. most of the queries` execution time is subsecond, but the round trip takes much more.
I'm looking to see if ...
- 35
1
vote
2
answers
403
views
68 ms vs 793ms. Same query, almost the same analyze. Why is it happening?
The query:
EXPLAIN ANALYZE SELECT COUNT(id)
FROM metrics
WHERE f1 = [randomly generated]
AND f2 = [randomly generated]
AND f3 = [randomly generated]
AND f4 = [randomly generated]
AND f5 = [randomly ...
- 35
0
votes
2
answers
133
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
0
votes
1
answer
753
views
Partitioning on two columns with discrete values
I have a table with 10 billion rows in Postgres.
The columns are:
user_id | entity_1 | f1 | ... | f5 | start_time | end_time
As you can see, this table holds the data of all of the users.
None of the ...
- 35
0
votes
0
answers
209
views
Efficiently Filter Parent Table with Join to Two (or more Children Tables)
Given the following schema for Microsoft SQL Server:
CREATE TABLE Parent (
TenantId int,
Id int,
ChildAId int,
ChildBId int,
);
CREATE TABLE ChildA (
TenantId int,
Id int,
);
...
- 532
5
votes
2
answers
237
views
Reoccurring performance issues at the server level
I am facing odd performance issues at one of my servers that is using SQL Server 2019 Standard Edition.
Users started complaining that the application is very slow/unresponsive. To check what is going ...
- 192
-3
votes
1
answer
102
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
109
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
1
vote
0
answers
125
views
Search query in large multi-tenant database
I have a fairly large table (700k rows) containing the main object of my application. This is a multi-tenant app. Typically, users have access to 1k rows of this table, but there are edge-cases with ...
- 123
0
votes
2
answers
198
views
Better estimating "worst case memory usage" for MySQL
Tools such as MySQLTuner add up globally allocated memory and add it to the product of max connections and per connection memory requirement. I formulated the following query to better estimate how ...
- 223
0
votes
0
answers
21
views
Is there a difference in performance loading different datatypes
Context: loading (COPY) a high volume of data to a warehouse (Redshift specifically however curious about all)
Question: Is there a performance difference in the different datatypes that I am loading? ...
3
votes
1
answer
1k
views
How much resources does a sleeping connection require in SQL Server?
One of our DBAs complained to our team that he noticed about a dozen sleeping connections are virtually permanent. Each of them indicates a very short query (a single record from a single record was ...
- 1,261
0
votes
1
answer
69
views
Performance Quetions. Search database with range query or with and id
I have a database, consisting of the following columns
id, a string looking like this 8b28347448d3fff (15 length)
x, a decimal (8,6)
y, a decimal (9,6)
All the columns have Indexes on them. Now, I ...
- 177
2
votes
1
answer
6k
views
sql server max memory ideal value best practice
Microsoft SQL Server database is running on the live server and the max memory value is 2147483647 by default.
There is no problem in this way, but how far can I lower this value to be more ideal and ...
- 21
0
votes
1
answer
288
views
Expensive column causes slow query with large offset
I am attempting to do a query that retrieves an expensive column on a table with a large offset. However, as the offset gets larger the query goes slower and slower until it does not resolve in a ...
0
votes
0
answers
48
views
Wich is more performant: one single table in relationship with a lot of other tables or one single table with an intermediary table?
I am designing a database where a table entity Interaction is in a 1-1 relationsip with a lot of other entities for example Appfollow_Metadata, Cloudtalk_Metadata etc... and these entities may reach ...
- 101
0
votes
1
answer
256
views
How does SQL Server's Performance Dashboard categorize a query as "ad-hoc" or otherwise?
I'm looking at the CPU usage in sql server's server performance dashboard report.
I have 10 dbs on the server.
So the pie chart shows me 85% for adhoc queries. Whereas only 15% is split amongst all ...
- 3,597
0
votes
0
answers
13
views
Same Spatial Query on nodes table gives different read time
I have created 2 databases.
First one has PGSnapshot Schema which looks like this.
On this I fire query
select id,ST_AsText(geom)
from nodes
where
ST_Intersects(geom , ST_GeomFromText('POLYGON ((-...
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 ...
- 1
1
vote
1
answer
136
views
Is there a way to tell Postgres to automatically give up on a plan and consider alternatives after exceeding estimated cost
There seems to be a few situations in which Postgres considers an "on average slightly better" plan over a "more predictable plan"
The failure mode of which can be the following:
...
0
votes
1
answer
126
views
Querying large tables in postgresql
Got a problem querying two large tables on postgres, both of them having a indexed column to identify the year, wich i'm using to reduce the number of rows, something like this:
WITH table_1 AS (...
- 21
0
votes
2
answers
62
views
Oracle query different performances between prod and staging environment
So I have those 2 Oracle 12c databases R2, prod and staging environment, both are perfectly aligned and running on identical hardware.
The same select statement will take about 400ms on staging while ...
- 101
0
votes
2
answers
1k
views
Optimizing IN clauses with thousands of integers
I have a search engine that outputs the id's of the elements to show in the search results, or to use in an "analytics" module.
In this one, I perform a lot of different queries to show ...
- 101
1
vote
3
answers
1k
views
MySQL: Improve performance on large table for dynamically built query with composite index correctly
Assuming that I have a MySQL table with ~ 30 million entries and 40 columns I have an highly active query (5 queries/second) which is quite slow (avg. ~ 20 seconds) and has a high number of rows ...
- 215
1
vote
0
answers
114
views
Optimizer Occasionally chooses a bad plan - how to tune query to ensure it is consistent
I have a query in my environment which occasionally gets a "bad" plan and takes a long time to run.
We can see from the query store output below that the query normally uses plan 25493651 ...
- 1,960