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.

Filter by
Sorted by
Tagged with
0
votes
2answers
65 views

MySQL partitioning relatively large DB Table

I have table structure similar to the following - CREATE TABLE `ProductCatalog` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `SerialNumber` varchar(20) DEFAULT NULL, `BasePrice` decimal(10,2)...
0
votes
0answers
67 views

Performance Insights MySQL RDS cannot change Counter Metrics or download full SQL

This is probably some permissions issue but I am trying to use Performance Insights on the AWS console for several MySQL RDS instances that are supposedly configured for it. I am following the ...
0
votes
1answer
53 views

Tuning query - reduce temporary and/or filesort use with join

My query runs in 60-90 seconds, I would like to see how can I improve it. One table contain partitions and have tens of million rows each partition. The other table contain 5-8 million rows between ...
1
vote
0answers
54 views

How to improve Oracle Standard Edition's performance for testing? [closed]

There's a great post on StackOverflow about improving Postgres performance for testing. https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing/9407940#9407940 However, ...
1
vote
1answer
302 views

Local JSON file vs MySql database [closed]

I've built a website using node js and it has two parts. one for users and one for website administrators for editing content on website that is locked by a login page. The user side, loads data from ...
0
votes
0answers
98 views

How to restrict CPU consumption of CPU intensive applicaiton with Db2 Workload Manager?

in Db2 v11.1 on Linux I would like to have control over applications that consumes too much CPU resources. For every application that consumes too much CPU for more then 3 seconds I want to lower CPU ...
2
votes
1answer
50 views

Should partitioning increase performance of indexed accesses and updates? [duplicate]

I often hear that partitioning huge tables should significantly increase query speed and insert/update speed, because indexes are proportionally smaller. I'm struggling to understand why that should ...
0
votes
1answer
21 views

Heavy performance drop on Progress query, looks like based on string character inside table

I'm launching following query on a Rel 11.6 Progress database: FIND FIRST Table1 WHERE Table1.Field1 = 1 AND Table1.Field2 >= 2 AND TRUE ...
0
votes
1answer
38 views

Does a single replica gives any performance improvement?

Consider a case where you have a single PostgreSQL database. Then you add a replica (hot standby) and you send all the reads to that replica. This is what does Rails for example. Does this gives any ...
0
votes
0answers
39 views

MySQL faster after restore

I have a table with ~300k records, and query with join other table. A query executed about 2 minutes. But! Yesterday I was backup and restore this database and saw, my query faster 10 times! From >...
2
votes
2answers
1k views

MySQL - Can I have quick queries in a table with 200 million rows?

THE PROBLEM I have following trivial SELECT statement: select * from `web_notifications` where `user_id` = 123456 order by `created_at` limit 500; The query is pretty simple (no join statements, no ...
2
votes
2answers
129 views

Index strategy for TEXT / NTEXT column to filter out NULL / NOT NULL rows

There is a table with TEXT or NTEXT data type column and I need to improve the performance of queries with WHERE clause filtering out NULL or NOT NULL rows in this column. Unfortunately, I cannot ...
2
votes
2answers
157 views

Highest cost section in Actual Execution Plan doesn't show any logical reads?

In the following query plan - https://www.brentozar.com/pastetheplan/?id=H1dwOCHed (it has 3 sections, the last one is the problem), there's a node at the very bottom that consumes 37% of the ...
-1
votes
1answer
44 views

Postgresql uses slower constraint over faster index

I have a few tables (25m records +) which have a very INSERT, UPDATE and DELETE biased workload (typically 50,000 to 100,000 operations a day) where the query planner seems to make an odd choice of ...
0
votes
1answer
69 views

Assign different RAM usage (buffer pool size) per db in mysql

I have quite some very large mysql databases (dev, production, staging, etc.) on a single server. Total size of indexes of all my databases is 64Gb. However, the InnoDB Buffer Pool is only 16Go, ...
1
vote
1answer
482 views

Does PostgreSQL handle case-insensitive database as good as others? [closed]

First things first, please forgive me if my question seems so naive (I am not a DBA). I look up to see how can I make PostgreSQL case-insensitive and I found the Nondeterministic Collations section of ...
0
votes
0answers
27 views

Postgres Query Performance (LEFT OUTER JOIN

How to optimize this query? I think that the left outer join is the problem but, what is the alternative? select this_.re_codigo as re_codig1_50_1_, this_.re_afdrow as re_afdro2_50_1_,this_....
0
votes
3answers
383 views

Unix timestamp vs timestamptz index performance [closed]

I'm new to the world of DB admin and have a large table (2 billion rows) of cryptocurrency tick data for multiple currencies in a Postgres database. Most of my queries will involve selecting a moving ...
0
votes
1answer
50 views

Is columnstore a good option for UPDATES with WHERE clauses?

I am developing a database project on SQL Server and I am thinking whether using columnstore index is a good idea. The project consists of a table (A) that will hold a large number of rows, with many ...
0
votes
1answer
25 views

Speed-up lookup of a row with prefix pattern

Say I have these rows in one UNIQUE column: 202101XXXXX 202101XXXXX 202102XXXXX 202102XXXXX 202103XXXXX 202103XXXXX The first 6 digits are year and month (YYYYMM), and the XXXXX part are random ...
1
vote
0answers
22 views

MySQL pagination

MySQL - I am trying to fix this pagination issue on my servers . The RAM is 40 GB and the innodb_buffer_pool_size is set to 36 GB which is a little more than 80%. Still getting paging alerts. Any ...
0
votes
0answers
475 views

Query performance drop after upgrade to MariaDB 10.5 (group_concat + join)

We have upgrade our database servers and are experiencing some performance issue. Some complex queries, with lots of join and group_concat are 30% to 60% slower than on our old servers. (Same query ...
2
votes
1answer
157 views

Save performance with large update on Index with Included Column

Points ------------------ PK QuestionId int (+30.000.000 distinct values) PK EventId int (large batches where 80.000 rows have the same EventId) Value smallint The table is about 40 ...
1
vote
3answers
234 views

Why MySQL 5.7 replication is verry slow on my new server

I have a problem with MySQL replication and I wanted to find advice or information on it. Let me show you the infrastructure. Master: This is our main MySQL server. I want to replace it by Slave B. ...
0
votes
2answers
88 views

Approaches to prevent database slowdown when another process is hitting it hard

I'm a developer not a dba, but this task was given to me... One of our databases is hit hard by a process which reads and modifies hundreds of thousands records onece or twice a day, during the time ...
1
vote
0answers
108 views

SQL: ON vs. WHERE in SEMI JOIN

Considering the following two (equivalent) SQL statements. Why do their execution plan differ (especially in performance) that much? I'm using Postgres 11.9 in this example. select p.name from person ...
0
votes
1answer
86 views

SQL (Postgres): Subquery in WHERE clause with IN operator is slow

I have the following SQL Statement: select count(*) from person p cross join task t where t.id in ( select tp.task_id from task_participant tp where tp.person_id = p.id ) and p.id = 1234 ...
1
vote
2answers
271 views

Multiple disks vs single disk on virtualized environment

We're planning to install a SQL Server on this physical architecture: A hyper-v cluster based on windows server 2019 A cluster shared volume where all VMs live A full-flash SAN that contains the CSV ...
1
vote
2answers
321 views

Performance issues since migrating from SQL Server 2012R2 to 2019

I've migrated my database from a SQL Server 2012R2 to a new SQL Server 2019. I've just backupped the database and restored it on new sql server. After that I've imported and updated some additional ...
2
votes
1answer
127 views

PostgreSQL Query with pagination is slow when using Merge Join

I am experimenting with trying to optimize a query that's used to iterate over pages of rows with a joined table. After changing the query from using OFFSET to WHERE id > last_id_from_prev_page and ...
0
votes
1answer
40 views

Improving remake of primary key performance

I'm using a MySQL 5.7 hosted on AWS that has a table with about 500,000,000 rows and 124GB in size. I want to drop the existing primary key (id) and create a new composite primary key of 2 columns: ...
0
votes
1answer
105 views

Thousands foreign keys referenced to one table and other postgres scaling philosophy

I'm inspecting one legacy and quite big database which is growing faster nowadays than before. Following query select count(*) from information_schema.tables where table_schema = 'public' returns a ...
1
vote
1answer
61 views

Decrease Insert and Update performance on bigger database

Have two partitioned tables (32 partitions / 16 files / 8 file groups). First table with 56 columns and Second table with 5 columns. One of requirment of application is to test procedures for future ...
1
vote
1answer
2k views

mariadb: Aborted connection .. Got timeout reading communication packets

What is the typical cause of warnings such as this? They appear periodically, sometimes multiple times per day then not for a day or so. 2021-01-08 13:20:46 203939 [Warning] Aborted connection 203939 ...
1
vote
0answers
80 views

Using double write buffer is 8x slower in SSD (compared with 2x~3x in HDD)

I understand the double-write-buffer enhances the reliability of data, so it makes transactions slower. But it is amazing that the slow down is such severe in the newest Samsung 980 pro (M.2 PCIe 4.0, ...
0
votes
1answer
250 views

How to speed up foreign key creation in MySQL for large tables?

I am trying to set a foreign key constraint on a 5.7 InnoDB table with 30M+ rows. It now already runs for over 1h on a quad core 64GB server. The processlist outputs the state copy to tmp table for ...
0
votes
1answer
64 views

why HDD is more sensitive to the change of innodb_fsync_threshold than SSD

What am I doing: I want to do some experiments to find some interesting facts in DBMS when changing configurations above different physical storage media. Disk: Intel P45 SSD vs. SATA HDD 7200 RPM. ...
0
votes
0answers
137 views

Why SQL Server's MERGE has such a huge Excessive grant when compared to INSERT + DELETE?

I am trying to optimize a security computation process for a Web application by trying to making it synchronous (compute on the fly for an entity) instead of asynchronous (large computation for all ...
0
votes
2answers
39 views

Postgres: Performance Issue: Query on enormous data fails to use index

This is the schema of task_statuses table Table "public.task_statuses" Column | Type | Collation | Nullable | Default ---------------...
7
votes
2answers
238 views

Clustered Index fragmentation vs Index with Included columns fragmentation

I have a relative large table (for me) with 40 million rows that is expected to grow to 80 to 120 million rows in a period of two weeks (during a campaign). Tip -------------- Id int (...
1
vote
1answer
314 views

Why do my RDS read IOPS drop after a certain period of time?

I am trying to add non-null constraints on several columns in a table with about 70,000,000 rows. Each statement is just ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;, and each ...
2
votes
2answers
128 views

Difference between avg Disk sec/write and io_stall_write_ms

We have a SQL Server 2016 SP2 Enterprise with latest CU with the database files spread out over different disks. So we have data,log, tempdb and system db's have each their own drive. Date and log ...
12
votes
1answer
337 views

Why Does the SQL Server Query Optimizer Not Convert an OUTER JOIN to an INNER JOIN When a Trusted Foreign Key Is Present?

It appears that the SQL Server query optimizer does not translate an OUTER JOIN into an INNER JOIN when the join column in the first table is defined as NOT NULL and has a trusted foreign key ...
3
votes
1answer
72 views

Why does a list of 10,000 IDs perform better in a complicated query with multiple CTEs compared to the equivalent SQL to select them?

I have a Rails application with a legacy query that I'd like to refurbish. The current implementation performs two SQL queries: one to get a large number of IDs and a second query that uses those IDs ...
1
vote
2answers
384 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 ...
-1
votes
2answers
43 views

Factorising out repeating data and replacing it with an integer key

I have noticed in my SQLite database that some tables contain columns where many rows usually repeat the same data. Transaction key from_account_key to_account_key timestamp amount data_type data ...
1
vote
3answers
122 views

How are entries in index leaf node ordered?

I am reading the book 'SQL performance explained' and when talking about indeces, it says that databases use doubly linked lists to connect index leaf nodes. Each node is stored in a database block ...
1
vote
2answers
224 views

Mysql Windows Performance Problem

My system: 2X E5 2680 V3 Cpu 32 GB Ram Windows 7 Pro Apache 2.4.41 Mysql 8.0.17 I have a script and this for Customer Online Chat Script. I have 10 clients for customer support. It uses php json ...
3
votes
2answers
92 views

How can this larger subquery be (much) faster?

In my organisation Ricoh is the supplier of printers and copiers. Papercut is used for registering which copies are made and for which account. Another application working with Ricoh is registering ...
0
votes
2answers
45 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 ...

1
3 4
5
6 7
125