Questions tagged [performance-tuning]
Improving the performance characteristics of a database application or system.
484
questions
2
votes
0answers
37 views
SQL Server sys.query_store_runtime_stats max_dop field reporting wrong values?
I have been trying to use the Query Store in SQL Server as an easy way to identify queries running in parallel and the degree of parallelism. However, when I look at the max_dop field in sys....
-1
votes
2answers
53 views
Index usage across databases [closed]
I would like counseling as to what is the best way to get performance in the following scenario. This is on a SQL2016 server.
I have a newly created central database where data is replicated to from ...
0
votes
2answers
70 views
Performance benefits of multiple .ndf files
Is there a benefit to having multiple mdfs with SSDs? I assume that with multiple cores there is, since there is less contention?
1
vote
1answer
40 views
Postgresql Query estimated time vs actual time difference
We have a hosted Postgres 11 DB server to fetch medium sized dataset and ingest into another application.
"States" table is the largest one in the following query, which will fetch ~1.5 ...
1
vote
2answers
71 views
Best Way to Optimize MySQL / MariaDB for 250M Row Table on a 64GB 16 core Machine [closed]
I have an extremely large dataset (250M Rows in one table, 170G on disk) on a very powerful desktop computer that I'm utilizing to clean-up the data and do analysis on it.
As part of this analysis:
I ...
0
votes
2answers
36 views
In what range can you expect a cloud based Sql Server data reading in mb/s? [closed]
Say everything is optimized, queries are simple, indexes are good, etc.
Should a SQL (Azure) database where you spend about 150 to 300 euros per month easily handle data reading with about 250-500 mb ...
0
votes
0answers
26 views
MYSQL optimization for Magento 2
Im running my magento 2 project on 3 core 8gb ram VM.
I did some benchmarks and i see that my DB queries take quite some time to execute. Traffic on this site is almost zero. Mysql is running on 5.7....
8
votes
2answers
208 views
Sqlite: Finding the next or previous element in a table consisting of integer tuples
I have a sqlite table called tuples defined like
create table tuples
(
a INTEGER not null,
b INTEGER not null,
c INTEGER not null,
d INTEGER not null,
primary key (a, b, c, d)
) ...
1
vote
1answer
68 views
Optimisation select with large IN and millions of records in result
I work with a table which consists of 1.5B records.
DB: RDS, PostgreSQL 12.4, 16GB RAM, 4vCPU
Schema:
CREATE TABLE public.trip (
id bigint NOT NULL,
cell_to character varying NOT NULL,
...
1
vote
2answers
51 views
Configure MySQL to minimize file locks
*** EDIT ***
8GB RAM
SHOW VARIABLES: https://pastebin.com/ANAiq1E2
SHOW GLOBAL STATUS: https://pastebin.com/pwWKRiie
*** END EDIT ***
To start off, I'm not a DBA, so I may have overlooked something ...
0
votes
1answer
50 views
Query data via username - pair - best performance
RDBMS: MariaDB 10.3
Let's say you have a table which stores specific information (info_1, info_2, etc.) according to a given pair of usernames; like so:
+----------+----------+----------+----------+---...
0
votes
0answers
22 views
Semantics and significance of a one time query execution, delete with Like vs =
I was required to delete the values of a certain table XX_choice_value, but this will be a delete run only once in two different stages. Raw query execution and nothing more, and then in my Pull-...
0
votes
2answers
85 views
Investigating RDS MySQL 8.0.16 stalls on write-heavy workload
I'm trying to track down some sporadic and difficult-to-reproduce issues with a MySQL 8.0.16 RDS instance connected to a web application with a write-heavy workload on a couple tables with large JSON ...
0
votes
1answer
23 views
set autotrace on is just estimate plan?
I've made a test:
SQL> set autotrace on; << autotrace on
SQL> select * from danilo.testepart where nome = 'danilo3';
ID NOME
---------- -------------------------------------------...
0
votes
1answer
21 views
How to set up indexes for dynamic filtering queries in Oracle
Given I have SOURCE_EVENT table in Oracle:
create table SOURCE_EVENT
(
ID NUMBER(19) not null
constraint PK_SOURCE_EVENT
primary ...
0
votes
2answers
56 views
How can i tune below proc either by changing query or adding index
I am having issues with one of the proc running very slow approx an hour:
Please pardon as I have to write entire code out of system to paste here. I will be editng the question with more details on ...
3
votes
4answers
122 views
SQL Server memory
It's regarding the SQL Server memory. When the server is running out of memory, we recommend adding additional memory to the hardware to overcome CPU utilization issues.
But before proceeding to the ...
0
votes
1answer
97 views
InfluxDB with Zabbix or Telefraf?
I'm considering the use of InfluxDB to store thousands of measurements per second from routers in a network. Each measurement is lightweight (between 10-20 bytes). Now, I'm wondering what is the ...
1
vote
0answers
123 views
Anomalous performance of mass INSERT in Oracle
Can you please help me diagnose, explain, and possibly solve the
problem of unstable peformance of mass insertions in Oracle?
I need periodically to load large amounts of data into an Oracle
database. ...
0
votes
2answers
101 views
PAGEIOLATCH wait and Page Life Expectancy very low
Training for SQL Server, has following scenario:
SQL Server on Azure VM, DS-series machine, it has 32 GB of RAM
Server has below performance conditions:
- very high PAGELATCH_IO waits
- average Page ...
0
votes
2answers
41 views
Are there better alternatives rathet than joining a table twice for having one record for a specific column in the output?
I have two tables with below structures:
create table TABLE_LC
( lc_num NUMBER,
brn_code NUMBER,
brn_role NUMBER)
create table BRN
( brn_code NUMBER,
brn_name VARCHAR2(50))
Some example data ...
0
votes
1answer
275 views
PostgreSQL LWLock: lock_manager issue
In our application we need to insert a lot of historical data that will be consumed later by some mobile devices.
These data is organized by a numeric incremental attribute that the device will use to ...
1
vote
1answer
37 views
how to speed up / fix this Ubuntu Apache MySQL Server Configuration?
I got new to the project, my predecessor is far gone and I'm left on my own.
Currently the MySQL-database seems super slow and after checking some variables and values I cannot identify the problem on ...
0
votes
0answers
33 views
Slow on searching duplicate records in millions of records
I've below three tables
CREATE TABLE [dbo].[RegisteredUser] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[UserCode] char(9) NOT NULL,
[Email] [varbinary](5000) NULL,
...
0
votes
0answers
19 views
Sampled rows is very less for auto update stats in SQL Server 2016 [duplicate]
I have SQL Server 2016. One of the table is having 9.24 million records. One of the stats (no filter on stats) on it is getting auto updated by SQL Server with sampling rate of 1 % (120023 rows).
As ...
0
votes
0answers
8 views
Postgres tracking count of recent items in count table or by selecting count of recent records from history table
Which would generally be more efficient, or does it not matter?
Option 1 is incrementing a count in a table that stores counts by the unique id, and resetting the count to zero when enough time has ...
-2
votes
1answer
25 views
What are some good SQL Server Performance Metrics from System Views?
I am working on a project where I need to collect as many SQL performance metrics as possible, all from system views.
I currently have collected, and in parenthesis the system view from which I have ...
0
votes
1answer
41 views
Mysql 8 - Count(*) - performance issue
I have this table with 2 million records
CREATE TABLE `my_table` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ID_USER` int(11) DEFAULT NULL,
`BODY` mediumtext CHARACTER SET utf8 COLLATE ...
0
votes
0answers
46 views
Index usage for database: where are the sys tables located
I have seen a couple of scripts that query some sys.someName tables to find usage of indexes in a database. My guess is that these tables are tied to a database e.g. there is a sys.indexes table in ...
-2
votes
1answer
44 views
Is Buffer Pool Extension (BPE) needed if my database is already on SSD?
Here is my simplified scenario, let's say I have one (and only one) database of 500GB on SQL Server 2016 EE, this database is on SSD drives already (both data and log files), and my server has 64GB ...
1
vote
0answers
91 views
Sql server partitioned table: what happens when an update of a record changes the destination partition?
I my company we are facing performance issues on a big Sql server DB (running on Azure, total size > 150GB).
We have one big table (1,5GB, 3 millions records) that has several quite slow "...
3
votes
3answers
443 views
Need help to understand tuning up a slow SQL server query [closed]
For one of our databases the query like below is extremely slow.
Due to security reasons I am not able to share the actual query or plan but just to give an idea how query is written is below
SELECT [...
0
votes
1answer
38 views
MySQL Clustered Index or Non Clustered index? (Database Design)
I know this might be a basic thing for you guys in this corner of SE but I'm having some trouble figuring out the best option for designing my DB.
I am creating a custom app where the user will be ...
1
vote
2answers
23 views
MySQL index size doesn't include all values
I have a table with ~1.4M rows and an id field as primary key and not all primary keys are included in index:
mysql> SELECT DISTINCT(COUNT(id)) FROM my_table;
+-------------+
| (COUNT(id)) |
+------...
0
votes
0answers
37 views
Slow query using SQL Server difference (soundex) function
We've got a nasty query that is always doing a clustered index scan even if I add in a non-clustered index with the Forename and Surname columns.
Query takes 2-3 seconds on a 2 million row table and ...
0
votes
1answer
1k views
MongoDB - Replication Oplog Window has gone below 1 hours
I'm working with MongoDB Atlas and I have a cluster of 3 with M30 and 100gb of storage.
My current usecase is the following:
- An user dispatches one search in my platform
- The platform dispatches ...
-3
votes
2answers
211 views
Why does IO Statistics show a lot of LOB Logical Reads on the Worktable when none of the tables in my query use any LOB datatype fields?
I have a procedure that runs a query dynamically across a few hundred databases that all have the same exact schema and aggregates the results to a temp table.
The query only involves 3 tables (each ...
1
vote
2answers
61 views
Very large table makes the application performance too slow
We have an old application that has problems with performance. I narrow it down to few spots in the system that definitely require attention.
I found one table referenced in this app with 1,332,730,...
0
votes
1answer
29 views
Which query has better performance? [closed]
I have 3 tables as follow:
class
id:bigint(PK)
name:varchar (unique)
principal:
id: bigint(PK)
pid:bigint
role:boolean
uniqueConstraint(pid, role)
entry:
cid: (FK, reference class)
...
2
votes
2answers
277 views
Increasing WAL Segment Size?
Is it possible to increase the WAL segment size in an existing PostgreSQL 11 database? What are the disadvantages of having larger WAL segment sizes?
The reason for wanting to increase it from 16 MB ...
2
votes
1answer
250 views
ZFS Tunings for PostgreSQL
What are the recommended ZFS tunings for PostgreSQL 11 (with TimescaleDB 1.6 extension) running on striped mirrored vdevs of SATA3 SSDs?
I am considering:
ashift=12
atime=off
relatime=on (or off?)
...
0
votes
3answers
135 views
When is it ok to use an Index Hint on a query in production code?
I've recently been working with decently sized tables (between 100s of million of rows to 10s of billions), and have ran into a few cases where I was forced into a corner to use query hints, which I ...
1
vote
1answer
74 views
Batch - Update vs Insert then Delete [closed]
There is a plan to create a new ETL job to do a particular task. It will retrieve data from database1, and from that data, it would update equivalent records in database2. Both database1 and database2 ...
6
votes
2answers
239 views
Disable top (n) Sort Optimization for a specific view
I have a a view with a complex logic and three levels of deepness (nested views). Because of the complexity I can't paste the execution plan.
As the purpose of the view is to provide some business ...
1
vote
2answers
291 views
Mariadb Delete with Join query stuck sending data
I am using Mariadb (10.4.10-MariaDB-log) with Galera Cluster (3 Nodes).
I am running a large data queries with joins , but
No idea why delete with join is not working.
DROP TEMPORARY TABLE IF ...
-2
votes
2answers
77 views
T-SQL Query performance
We are using SSIS package to execute the following statement.
SQL statement
delete from ABC
OUTPUT DELETED.[ABC_Id], 5
into XYZ (OId, OCode)
where ([ABC_Id] = ?...
1
vote
1answer
157 views
Funky cardinality estimate skews, high memory grants and missing statistics
I have a select statement within a procedure that is causing me some grief at the moment. The query estimates about 100 million rows and that in turn grants large chunks of memory (about 8 gigs per ...
0
votes
1answer
154 views
in what all scenarios SQL Server update statistics
I know SQL Server update stats when 20% of data is changed in a table. It keeps tracks of it by using modification counter which we can see in [modification_counter] column of sys.stats table.
But I ...
0
votes
0answers
28 views
SQL Access Advisor with big Select query
I need to get recommendations from SQL Access Advisor for big Select query (> 10000 simbols).
I use DBMS_Advisor, but one procedure i need support only 4000 symbols.
How can I use this package with ...
3
votes
0answers
77 views
SQL Server, what is the optimal Processor & Core configuration for VMs? [duplicate]
In my day job I deal with alot of SQL Servers...and lately I've had a rash of clients running 16 cores but configured as 1 core per processor and their SQL Standard edition runs like a dog, as ...
