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,034
questions with no upvoted or accepted answers
9
votes
2
answers
2k
views
Postgresql with just index storage
I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated.
...
- 91
9
votes
1
answer
1k
views
Select * from statement execute very slowly, innodb io read speed is low
I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: 5....
- 91
8
votes
0
answers
2k
views
How to measure the memory usage of a Mongodb aggregation?
I recently started running into the Sort exceeded memory limit of... exception on an aggregation pipeline in MongoDB. I got around this by setting AllowDiskUse=True for the aggregation. However, I ...
7
votes
0
answers
1k
views
What can cause slow planning times for "select 1"?
I have a database that is used by a Production application. It's an db.m3.medium on Amazon RDS. We've noticed that a lot of queries are starting to get slow that typically shouldn't be slow (e.g. ...
- 327
6
votes
0
answers
3k
views
Slow Postgresql query with left join
I have a query on Postgres, and I also have added proper index. Is there anything missing here?
SELECT orders.*, demo.name as d_name
FROM orders
LEFT JOIN users as demo ON demo.id = orders.dr_id
...
- 161
6
votes
1
answer
4k
views
Queries running very slowly in Amazon RDS PostgreSQL read-replica
We have been maintaining a project which has both web and mobile application platform. The backend of the project is developed in Django 1.10 and deployed in AWS.
At the beginning, when there were ...
5
votes
0
answers
11k
views
Postgres bulk upsert performance
I am doing bulk UPSERT on a table with 30 million rows. The table has just two columns (varchar as a primary key and integer). The input data is imported to the temporary table first, and then bulk ...
- 285
5
votes
0
answers
4k
views
PostgreSQL High Disk I/O
I am evaluating PostgreSQL as an alternative to Oracle. I have a database with 533 Tables which contain up to 250,000 entries.
For a performance comparison, I built the datbase both on Oracle and ...
- 151
5
votes
2
answers
603
views
PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?
I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability )
CREATE TABLE scale_data (
section NUMERIC NOT NULL,
...
- 161
5
votes
0
answers
483
views
Postgresql row-level security generates different query plan from manually inserted WHERE clause
(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 ...
- 163
5
votes
1
answer
8k
views
Materialized Views and Indexes
Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
- 61
5
votes
1
answer
2k
views
Poor performance in my pgpool cluster
I've configured a postgres cluster with pgpool2 using the following link.
The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for ...
5
votes
1
answer
4k
views
SQL Server performance issue (CXPACKET wait type)
I have a performance issue with production SQL Server 2012 which has 24 cores and 32 GB of RAM. From wait type I can see CXPACKET is on the top based on the following SQL Server metrics, should I ...
- 409
5
votes
1
answer
4k
views
SQL SERVER 2016 high CPU
I have a brand new server with SQL Server 2016.
It has 24 CPU, about 80go of RAM.
The thing is, sometimes, the CPU gets pretty high (> 70%) with no specific reason at all.
If I look at the 'execute ...
5
votes
1
answer
320
views
Guidelines on best indexing strategy for varying searches on 20+ columns
I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation.
Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search.
The problem:
...
- 961
5
votes
1
answer
5k
views
NESTING_TRANSACTION_FULL latch
Production SQL Server 2008 performance is far below expectations. Simple single-table queries sometimes are working for 5 seconds in the mid-day compared to 250ms after hours. We cannot find which ...
- 59
5
votes
1
answer
1k
views
Measure Performance for INSERT, UPDATE and DELETE commands on DB2
I have a DB2 database with a table for daily transactions, which is cleared to a history table at the end of the day, every day.
I insert rows to this table via java-JDBC INSERT commands and they ...
- 151
4
votes
0
answers
583
views
Non parallel plan with row level security predicate to check server role
While exploring Row Level Security I picked up that whenever I had it enabled it was not creating any parallel query plans.
In SQL Server 2019 this gave me
NonParallelPlanReason="...
- 41
4
votes
1
answer
5k
views
PostgreSQL Enum : Search on ENUM type
Defined an ENUM
CREATE TYPE currency AS ENUM('GBP', 'EUR', 'USD');
Using this as a type in a table
CREATE TABLE if not exists transaction(
id BIGSERIAL NOT NULL PRIMARY KEY ,
amount NUMERIC(35,...
- 41
4
votes
0
answers
1k
views
How costly is changing column datatype in PostgreSQL?
I'm currently evaluating PostgreSQL for an app we are developing which comes with a weird usecase. We want to allow clients to create new fields which in turn will create new columns in a client-...
- 141
4
votes
0
answers
730
views
Postgresql update performance?
We have a table which stores about 20 million records:
Table "public.tbl"
Column | Type | Modifiers
-------------------------+------------...
- 141
4
votes
1
answer
2k
views
Slow postgres query that uses quick sort instead of hash-aggregate
I'm on Postgres 9.5 and working with a web analytics database that records visitor traffic.
I'm trying to optimize a very slow query that gives me a count of the unique people that visited a given ...
- 1,094
4
votes
0
answers
339
views
Performance degradation after upgrade from 3.0.11 to 3.2.12
Environment: CentoOS 6 (kernel 2.6.32), MongoDB community edition 3.0.11 and 3.2.12 , storage engine MMAP, config servers on SCCC
We are experiencing performance degradation when moving from 3.0.11 ...
- 1,207
4
votes
1
answer
2k
views
Why does postgres choose to sort instead of scanning the index?
I have the following table:
# \d service
Table "public.service"
Column | Type | Modifiers
-------------+----------+-----------
customer_id | integer | not null
date | date ...
user109810
4
votes
0
answers
753
views
Postgres: CREATE INDEX, but very slowly, to reduce performance hit on rest of database?
I have a large production PostgreSQL (9.1) database running on Ubuntu Linux. It is currently being queried and used. However there is another use I need later, and I will need to create a large (...
- 947
4
votes
0
answers
196
views
PostgreSQL/PostGIS: Query seems inordinately time-consuming
I'm doing what I would consider a pretty straightforward query on two tables that aren't huge (~626k records in one; ~47k records in the other).
Both tables have GIST indices on their spatial ...
- 215
4
votes
1
answer
79
views
How to increase performance in Cross Database operations?
We currently have a SQL Server 2008R2 being accessed by a legacy ASP application.
Among different processes, one of them essentially SELECTs data from one database and INSERTs (or UPDATEs) them into ...
- 143
4
votes
1
answer
146
views
help required with indexing and query optimzation
After browsing most of the posts and after running out for options I am posting here.
I have a table with 76,222,954 (76M) rows. It collects web site traffic based on country.
Here is my table ...
- 41
4
votes
1
answer
139
views
How can I avoid this poor Postgres query plan for a sparse aggregate query?
I have the following database schema:
CREATE TABLE foo(
id SERIAL PRIMARY KEY,
tag INTEGER NOT NULL,
val INTEGER NOT NULL
);
CREATE INDEX foo_tag ON foo(tag);
CREATE INDEX foo_val ON foo(val);
...
- 140
4
votes
1
answer
811
views
Optimizing intersect query in MySQL
I'm facing a performances problem in our MariaDB server.
We have a table linking users to pages, a user belongs to 1+ pages.
I have a page that have users and I want to get the 10 best pages based ...
- 141
4
votes
1
answer
109
views
Sample a Mysql table to increase performance
we are storing all our clients traffic data in a MySQL 5.1.73 database (MyISAM) and then we use it to generate user and internal statistics/graphs, always searchinng for a client id and a date/hour. ...
- 41
4
votes
2
answers
641
views
poor and/or unreliable InnoDB performance
I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped.
I have a database containing four InnoDB tables. The largest ...
- 273
3
votes
0
answers
278
views
Possible missing step in sampled statistics generation?
I've searched far and wide for information on why sampled statistics are less accurate, and broadly speaking every question has always been explained with the following generalizations:
sampled are ...
- 39
3
votes
1
answer
3k
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/...
3
votes
0
answers
189
views
Evaluating Success of CTFP Value Change
We have recently made a change to the CTFP setting on our test server with a view to rolling this out to live.
Our current CTFP is 15 so we tried setting it to 50 which is often seen as a better value....
- 2,033
3
votes
0
answers
616
views
How to map child (leaf-level) nodes to specific parents with/without ltree for aggregations?
What would be the best way to aggregate across hierarchies?
I am using Postgres and have the following 3 tables (folder, folder_hierarchies, and work) -
folder
Column
Type
Description
folder_id (pk)...
- 131
3
votes
0
answers
607
views
Recent degradation/variability of HashBytes performance
Up until a few days ago, T-SQL code we have running in SQL Azure that generates an MD5 for temp table values was fast (a few seconds per hundred thousand on a Business Critical Gen 5, 2 VCores DB). ...
- 175
3
votes
0
answers
921
views
How to monitor query load, counts, and durations in PostgreSQL
I'd like to understand load (average active), count, and, average time of various queries.
This is an AWS RDS Performance Insights dashboard. This is the sort of information that I want, though as ...
- 780
3
votes
0
answers
643
views
How to optimize this recursive postgresql query?
I've been searching a lot on this topic but I'm stuck at optimizing a query.
I've created a complete dbfiddle to see the problem, if you increase the amount of inserts for the comments table you'll ...
- 131
3
votes
0
answers
879
views
Postgres configuration for high-latency storage
I'm trying to setup a postgres server using Ceph as storage. After taking benchmarks, it was obvious that even though the storage has a really good throughput, the higher latency brought by network ...
3
votes
0
answers
9k
views
Steps to unlock a locked select in postgres
I have tried to update a table in Postgresql 9.6 (AWS) based in two inner joins.
Since it was taking too long, I cancelled it.
Now I'm trying to run a query to count records from the inner join but ...
- 131
3
votes
0
answers
90
views
Poor query planning in Postgres 10
The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join.
Here's my ...
- 131
3
votes
1
answer
1k
views
How can I make full text search on larger text rows more efficient?
The problem is I tried the LIKE and ILIKE and it was faster then pg_trgm and the postgres full text search, because they often run in a timeout or throw an error:
NOTICE: word is too long to be ...
3
votes
0
answers
2k
views
Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?
I run the following query against Postgres 9.5 which returns article information along with the currently valid price:
WITH selected_article_rm(id) AS (
VALUES
('80512e52-a406-4a9b-a8df-...
- 256
3
votes
0
answers
2k
views
Setup a PostgreSQL HA with synchronous replication in windows
I am fairly new to postgres ans would like recommendations for setting up HA system in windows Server machines. My current plan is to run 2 server Primary (Data center) with postgres 9.6 and same ...
- 131
3
votes
0
answers
232
views
Automating performance tests in PostgreSQL
I want to check the performance of specific queries in a Postgres database on a regular basis. Also, I want to be able to check the change in performance of different queries with any change in my ...
- 31
3
votes
0
answers
4k
views
Npgsql Timeout for simple query when run in parallel
I'm struggling to determine the cause of an exception when the code is run in parallel, but works fine when run one at a time.
As background information, I'm running a set of unit tests, where each ...
- 175
3
votes
0
answers
63
views
Query plan shows high negative value and the server slows down
My SQL Server 2016 had been presenting problems certain days, all process since the morning begins to run slowly than normally. Sometimes updating statistics works, sometimes not and had to reboot ...
- 31
3
votes
0
answers
775
views
Searching between two BIGINT
I have a table defined as:
CREATE TABLE [dbo].[IPS_INTEGERS](
[id] [int] IDENTITY(1,1) NOT NULL,
[network_start_integer] [bigint] NULL,
[network_last_integer] [bigint] NULL,
[...
- 39
3
votes
0
answers
688
views
Spike in Disk I/O due to external process causing PostgreSQL commits to pile up in the slow log
Background:
I have a live Django app that utilizes 4 Redis instances alongwith PostgreSQL 9.6.5.
Note that the first two Redis instances are big in size: back ups amount to ~2GB and ~4.4GB ...
- 2,009