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.
1,003
questions with no upvoted or accepted answers
2
votes
2
answers
187
views
How to troubleshoot intermittent Azure SQL performance issues
I'm running an Azure SQL instance behind an Azure Web App, both of which run perfectly fine the majority of the time. At seemingly random intervals (ever few days or so), the database DTUs peg out ...
- 121
2
votes
1
answer
107
views
MySQL performance issues with occasional complete freezes
we have a fairly large production DB currently at ~50GB but with constant growth. In the last couple of weeks, we are struggling with performances as some queries are getting very slow even if indexes ...
- 121
2
votes
0
answers
233
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, ...
- 233
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 ...
- 21
2
votes
1
answer
368
views
Optimizing cloud server (php and mysql)
I am running a vBuletin forum as a hobby and I was using a shared hosting service until a couple of days ago. Because the amount of visitors grew and the forum seemed to be slower and slower every day,...
- 31
2
votes
0
answers
509
views
Preventing low-priority tasks from dominating Postgresql server
I have a side project which involves scraping several websites and aggregating their content, which can be queried via an API. As such, I have a few background tasks which update/insert millions of ...
- 21
2
votes
0
answers
49
views
Why is it faster to SELECT my logical CASE field into a new temp table as opposed to UPDATing the original temp table to SET the field?
I have a pretty large temp table (about 4.5 million rows) with no indexes, so it's stored in a heap. Let's call it #TempTable1.
#TempTable1 has a BIT field called ToInclude which is set in a ...
- 35.1k
2
votes
0
answers
657
views
MySQL performance degradation with heavy inserts
I have an application that does heavy inserts into 5.7 MySQL database. The performance is really good for normal workloads, however, when there's an intense activity in the database the queries (...
2
votes
1
answer
95
views
Incredibly long execution time for a update query?
I am using MySQL 5.6.
In my table invoices I added two datetime columns that I will be setting, based on if another column is a certain value.
UPDATE invoices
SET twoWeekAlert = DATE_ADD(now(), ...
- 225
2
votes
1
answer
50
views
MongoDB map-reduce impacts insert performance on source collection
I have a "hot" MongoDB(4.0.5) capped collection with a timestamp (and several other) indexes. Many applications (Java, NodeJS & C++) insert (never update) records into this collection at a rate ...
2
votes
0
answers
383
views
Performance drop after change tracking & isolation snapshot
Several days ago, I enabled change tracking on a db and some of its tables.
A few days ago, I also SET ALLOW_SNAPSHOT_ISOLATION ON.
(Probably important note: I never SET READ_COMMITTED_SNAPSHOT ON, ...
- 437
2
votes
0
answers
91
views
Improve split large table algorithm in MySQL
I have a very large table ~1TB of history data in MySQL 5.6. I want to split the data into many smaller tables per sites. My current idea is to iterate in chunks of 10'000 records and inside this loop ...
- 163
2
votes
1
answer
61
views
Advantage of querying data outside a filtered index
Is there any way to take an advantage of having a filtered index to speed DML queries targeting data sets outside that index?
My playground:
Table with billion rows and 100 columns
Unique clustered ...
- 503
2
votes
0
answers
148
views
How can i tune query without creating index
I am just starting to learn about tuning the queries and have found this very simple query which takes over 6 hours to run because of no supporting indexes. As its vendor product and they are not ...
- 2,169
2
votes
0
answers
85
views
Querying a High Cardinality Field
I am designing a data model for our orders for our upcoming Cassandra migration. An order has an orderId (arcane UUID field) and an orderNumber (user-friendly number). A getOrder query can be done by ...
- 121
2
votes
1
answer
366
views
Postgres memory settings (RAM, work_mem, etc.) for complex text searches on indexed tsvectors
I know very little about Postgres memory settings.
I have developed a paid-access database, which mostly stores text (and some metadata)
2 tables have 10 millions rows with little text (1 paragraph ...
- 21
2
votes
0
answers
425
views
Optimizing a paginated query with various filters and dynamic sorting
Introduction
Imagine a (fairly advanced) ToDo app in which items are catogorized into lists (which can be temporarily deactivated), have custom additional attributes, can be assigned to a user and can ...
- 21
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 ...
- 121
2
votes
0
answers
75
views
What's the more efficient way of selecting with multi left joins?
I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities.
All tables have a id column as the primary key. student.id is varchar(64) (similar ...
- 21
2
votes
0
answers
58
views
the cost decreased but the time was practically the same, why?
Table definition:
CREATE TABLE schema.mylogoperation (
id_mylogoperation serial,
data DATE,
myschema VARCHAR(255),
column_var_2 VARCHAR(255),
user VARCHAR(255),
action TEXT,
...
- 153
2
votes
0
answers
223
views
What's the more performant database design for query speed and write overhead when storing many boolean columns?
Setting the scene
I have a PostgreSQL 11 database running on a very fast 64core, 10 SSD HW RAID 10 server. In the database I need to create a table with a couple of million rows, let's call it cars. ...
- 951
2
votes
0
answers
2k
views
Postgresql slow remote execution when using alternative clients
I noticed a strange perfomance issue when running sql queries on a remote Postgresql Server.
Everything good when using psql:
Connect via ssh and run things locally
$ ssh [email protected]
user@...
- 121
2
votes
0
answers
64
views
Recommendations around managing large amounts of data that needs to be aggregated (SUM)
I am looking for alternatives to redesign a part of our system that needs to perform cumulative SUMs over large data sets of rows based on user and dates.
How the system works
We are running a web (...
2
votes
0
answers
121
views
Postgres / slow query / order by
I'm joining two tables, filter out results by second table and order results by first table's ID.
The query is super-slow on Postgres Database. I see the issue is in the ORDER BY statement. But ...
2
votes
0
answers
3k
views
postgresql: live tuples not matching table row count
We're experiencing poor performance with a table. The table is regularly updated with insert/updates, and is a critical table in an application.
In looking into the problem I've noticed that the row ...
- 21
2
votes
1
answer
66
views
How can I optimize this MYSQL Script For many Records?
I have This script by Helping @lukstorms ,
-- DROP TABLE IF EXISTS test_words;
CREATE TABLE IF NOT EXISTS test_words (
id int unsigned NOT NULL PRIMARY KEY,
words text NOT NULL
);
-- DROP TABLE ...
- 21
2
votes
1
answer
84
views
One vs. Many Tables for Frequently but Asynchronously Updated Columns?
As part of my Postgres 10 schema, I have two tables; current and historic -- both have the same columns.
current has about 20 (+/-) columns, and holds the most up-to-date information for accounts. I ...
2
votes
0
answers
1k
views
How to VACUUM FULL a single partition without stalling the parent?
I have a table called TABLE_A that's partitioned by month. Each partition is named according to it's month, TABLE_A_201810, TABLE_A_201811 etc
I've noticed that if I attempt to VACUUM an old ...
- 1,125
2
votes
1
answer
127
views
Regularly updating table with huge amount of rows
I have a table containing (in addition to some metadata) a score and a boolean flag:
CREATE TABLE scores
(
score_id SERIAL NOT NULL
CONSTRAINT score_pkey
PRIMARY KEY,
user_id ...
- 351
2
votes
1
answer
157
views
Spike and time out errors every x th minute
I have very complicated situation here.
Application team was reporting that they see spikes and errors every 20 mins. We checked what was running and found log backups.
So we tuned log backups using ...
- 2,169
2
votes
0
answers
239
views
why is update from a join containing zero records taking a minute and 20 seconds?
SQL Server 2008R2. I have a simple join that returns zero rows in less than a second. When I try to run an update on that join, it should also take less than a second but it is taking one minute and ...
- 131
2
votes
1
answer
3k
views
Innodb: When bulk inserting or updating - how to disable transactions?
When doing a mass insert like INSERT INTO SELECT FROM the speed benefit of doing that in chunks of 10k records can be enormous, in my tests a query that was running for 5 days took only 2 hours using ...
- 402
2
votes
0
answers
414
views
Plan Guide for the LINQ-SQL Query
I have a query which is slow and it takes 15-20 sec when i run from the application.So i captured that query using SQL profiler and executed on SSMS with OPTION(RECOMPILE) and the query runs faster in ...
- 1,295
2
votes
0
answers
87
views
Problem query - execution plan
I have a problem with a long running query, I'm not a DBA but it's become my problem. I've been doing a lot of reading over the last few weeks but for the life of me can't figure out why this query is ...
- 21
2
votes
0
answers
255
views
SORT(DISTINCT) :Is it because of Kitchen sink queries?
One of the SQL queries I am working on is slow in performance. I have been trying to fix it for the last two days, unsuccessfully. The query is generated from an application (Linq to sql -EFF), and ...
- 1,295
2
votes
0
answers
490
views
PostgreSQL - Index Not Used on Large Table
I've the following query
SELECT *
FROM "items"
WHERE (("field1" = 'somevalue' OR "field2" = 'somevalue' OR "field3" = 'somevalue'))
ORDER BY "createdAt" DESC
LIMIT 20 OFFSET 0;
Here is the ...
- 121
2
votes
1
answer
43
views
How to compare across rows in large table?
I need some help with a complicated query. Here are the dataset structures I'm dealing with. This is a MySQL database.
*transactions_table*
zip | date
-----------------
1234 | 2010-05-01
4567 | ...
- 121
2
votes
1
answer
472
views
MySQL InnoDB or MySQL NDB Cluster which one to use
We have an application with 8000 users accessing in parallel(max). We are trying to bring in horizontal scaling. I came across MySQL NDB cluster. Should I go with it? Just for auto sharding and other ...
- 121
2
votes
0
answers
129
views
Is it correct to get SQL Server read write ratio from sys.dm_os_buffer_descriptors?
I'm reading a book and the author taught us a trick to calculate the read write ratio by using sys.dm_os_buffer_descriptors.
I suspect the correctness of this method. After a page has been read into ...
- 151
2
votes
0
answers
591
views
Poor SQL query optimization of Recursive CTE with iTVF, environment specific
I'm developing a recursive CTE which utilizes an inlined TVF. In our dev environment, the recursive CTE executes exactly as expected.
In our production environment, the first iteration of the CTE ...
- 141
2
votes
0
answers
3k
views
Mysql: Stored procedures vs. inline SQL?
We are evaluating whether we should write stored procedure OR write inline queries. I read this link to get an opinion.
I want to know pros and cons from mysql perspective as the above question is ...
- 151
2
votes
0
answers
52
views
How can I efficiently search for the most recent rows where a column matches any result from a different query?
I have the following schema:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE friends (
user_id BIGINT NOT NULL REFERENCES users,
...
- 21
2
votes
0
answers
326
views
MongoDB not performing well when set Read from secondary
I have a mongoDB database, with 1 primary and 1 secondary in replicaset(and 1 arbiter). Both the servers are hosted on aws on c4.2xlarge instances(with 1.5 TB of SSD on each).
Mongo version of primary ...
- 155
2
votes
0
answers
80
views
What can cause a static SQL query against a static data set take vastly different amount of time to complete?
I have this very strange issue on one of the production servers - a parameterless stored procedure is either taking 1-2 minutes or 45+ minutes to finish, and it seems completely random and not ...
- 21
2
votes
0
answers
658
views
Why is Oracle SQL slower than cursors?
I am converting an SQL Server system to run on Oracle (11g). The system has all been set up, data loaded, etc. The Oracle system was loaded using all standard default values.
The amount of data is ...
- 29
2
votes
0
answers
73
views
Redesign query/database because of high load
Need help with query or redesigning the db. Please bear with me the query will be changed to pdo ASAP. I'm not a DB admin, nor am I some MySQL wiz.
I have the following tables:
CREATE TABLE IF NOT ...
- 21
2
votes
0
answers
5k
views
"Acknowledge over PGA limit" wait event
Is it possible for this wait event to activate/showup even when the PGA has not crossed over PGA_AGGREGATE_LIMIT?
I am continuously seeing this wait event in the ASH, but the PGA allocated stays ...
- 113
2
votes
0
answers
2k
views
Postgres performance issue during /copy
I run a weekly load from csv files on one of my Postgressql tables (27 GB) using /copy command. During this time, the response time of all other tables in the database increases ( from 4-8 ms to 300 ...
- 555
2
votes
1
answer
305
views
Slow PostgreSQL 9.6.5 query with LIMIT, but only for tsquery with no results
Table a has > 3 million rows and this index:
CREATE INDEX idx_a_txt
ON a
USING gin
(txt);
This query takes 5 seconds if I search for something that doesn't exist, and 31 milliseconds if I ...
- 424
2
votes
0
answers
276
views
SQL Edition Downgrade Impact on Jobs
we’ve made a downgrade from Enterprise Evaluation Edition to Standard and we saw the over night jobs all running much longer these are ETL packages and SQL Stored Procedures to load data from ...
- 41