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
-1
votes
0answers
28 views

Replace column by LEFT OUTER join to save space

I have a table t1 with 100M row. For column A, 95% of the rows have value NULL0, the other records have values from 1 to 9. To shrink my table, I'm thinking about moving column A to table t2. t2 ...
0
votes
0answers
18 views

Mysql high number of queries in queue

we have been struggling to understand this behabiour on mariadb. we have some jobs that run every minute and they do a lot of updates on some metadata tables inside mariadb, however we have noticed ...
0
votes
0answers
12 views

How does the MongoDB $filter (aggregation) stage work internally?

I was asked a question by a colleague today whether the MongoDB $filter aggregation stage is implemented efficiently. The aggregation stage in question: $project: { absences: { $filter: {...
0
votes
0answers
19 views

optimize query with date type field in mysql

I currently have the following query prepared: select sum(amount) as total from incomes where (YEAR(date) = '2019' and MONTH(date) = '07') and incomes.deleted_at is null When ...
0
votes
0answers
41 views

Database performance, tps & IOPS

We have a Java application that uses a MariaDB database that is dedicated to this application. The application needs to insert 1 million records within 5mins so ideally we would like the database to ...
0
votes
0answers
15 views

Database performance, tps & IOPS [duplicate]

We have a Java application that uses a MariaDB database that is dedicated to this application. The application needs to insert 1 million records within 5mins so ideally we would like the database to ...
0
votes
0answers
14 views

Mysql memory_global_total is smaller than memory usage in top

When I try top in my ubuntu the result top - 16:03:09 up 16 days, 21:24, 2 users, load average: 3.53, 2.72, 3.46 Tasks: 395 total, 1 running, 282 sleeping, 0 stopped, 0 zombie %Cpu(s): 10.0 ...
0
votes
0answers
15 views

Possibility of using self-referential foreign key to create hierarchical comments

I am an accidental DBA and have a question regarding the table structure of a website I maintain (postgresql 9.6 backend). Imagine a typical content website (let's call it 'Links'), where users post ...
1
vote
2answers
101 views

MS SQL Server Performance Issues After Enabling All CPU's [closed]

My system specifications are; Windows Server 2008 R2 Enterprise SP1 128 GB RAM Intel Xeon E5-2620 (3 processors) MS SQL Server 2012 Standard (11.0.2100.60) There is 3 NumaNodes and 12 CPU's in them. ...
-1
votes
1answer
27 views

Advantage of 'with' queries over plain 'select' and joins?

Are there any advantages using WITH queries instead of simple SELECT queries using JOINs performance wise? I am asking solely about reading data (from catalogues specifically).
0
votes
0answers
9 views

Perfect DB design for Transaction feature [closed]

In my current project i am working a feature where people will buy/sell each others products. So let's say User1 has two products and User2 has 1 product. Now if User1 buys one product from User2. And ...
-4
votes
1answer
81 views

SQL Server database slow performance [closed]

I have a SQL Server database in production, and typically the performance of the application connected the database is satisfying. For some days many clients were complaining about slow performance of ...
0
votes
0answers
11 views

MongoDb Join VS Nested Query

I'm trying to build a game server and we are using MongoDb as the database. We have a collection Games that has models like this: { "_id" : ObjectId("5d1b345b8ea742034db76431"), "Users" : [ ...
-2
votes
1answer
32 views

How does 'Top SQL - By Application' values from SQL sentry correlates

I have run a performance simulation on my application server and pull the sentry report for my analysis. And While going through the top sql -by application, I found difficult to correlates the values ...
0
votes
0answers
22 views

Revenue Forecast Database Design

I am currently trying to design an Access database that will house financial data containing forecasting, actuals and prior forecast data. The current format of the Excel "database" is Customer ...
0
votes
2answers
44 views

MySQL - Got an error reading communication packets - high open tables, server goes offline

I am currently testing a product with a couple of my friends before releasing to the public. There's now 10 of us who use the application daily. Yesterday, we noticed a performance error on the MySQL ...
0
votes
1answer
27 views

MySQL using more memory than expected

We recently upgraded a database server from Ubuntu 14.04 to Ubuntu 18.04, and at the same time upgraded MySQL from 5.6 to 5.7. Since the upgrade, the memory usage has more than doubled, from ~10GB to ...
0
votes
1answer
27 views

Detect bottleneck of server via psycopg2

I have PostgreSQL superuser called "admin" which can connect to a PostgreSQL database. I am not managing the database server. I have no root-access to this machine. Sometimes the performance is not ...
0
votes
0answers
22 views

How performant is finding a substring in database?

To route requests on my website, I need to query the database to see if the first part of a URL matches a category for an item. For example: I have a request to /animals/dog/darktheme/. The ...
1
vote
1answer
39 views

Help using SQL Server Database Experimentation Assistant for performance testing

I am following the guide for capturing a trace in SQL Server Database Experimentation Assistant here: https://docs.microsoft.com/en-us/sql/dea/database-experimentation-assistant-capture-trace?view=sql-...
2
votes
3answers
80 views

Indexing a SHA hash in place of a VARCHAR

In Is it a good idea/approach to index a VARCHAR column? the concept was using VARCHAR as the column. I was wondering, if we store the SHA-1 hash or SHA-256 hash (if I am paranoid) as a BINARY(20) ...
1
vote
0answers
18 views

MySQL Percrona 8.0 became suddenly slow until restart mysql

My website today suddenly became slow and all requests were taking about 2 seconds to complete. Usually it takes like 0.2 second I checked available space on disk and I have a lot. Memory and CPU ...
8
votes
2answers
135 views

Very similar queries, vastly different performance

I have two very similar queries First query: SELECT count(*) FROM Audits a JOIN AuditRelatedIds ari ON a.Id = ari.AuditId WHERE ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1' ...
4
votes
0answers
32 views

What DBMS to choose for my production environment? [closed]

This is my very first question on the StackExchange/StackOverflow community. I originally am an electrical engineer so I hope you bear with me. I need to set up a database whose purpose is to be ...
-2
votes
1answer
56 views

SQL Server performance with FREEPROCCACHE [closed]

I watched a lesson on Youtube about SQL Server performance tuning. They demonstrated on a query "Wait Time per Core Per Sec". They use an update script as below: DBCC TRACEON(4199,-1); DBCC ...
13
votes
3answers
1k views

SQL Server has encountered occurences of I/O requests taking longer than 15 seconds

On Production SQL Server, we have following config: 3 Dell PowerEdge R630 servers, combined into Availability Group All 3 are connected to single Dell SAN storage unit which is a RAID array From ...
1
vote
2answers
38 views

MySQL and MariaDB: COLLATE on table vs COLLATE on all columns?

Will the following table behave or perform any differently CREATE TABLE t1 ( foo char(60) COLLATE utf8_bin, bar char(64) COLLATE utf8_bin, baz char(80) COLLATE utf8_bin ) DEFAULT CHARSET=utf8; ...
0
votes
0answers
17 views

Merge ReplicationWhy are uploads from subscriber 5 times slower than downloads on the subscriber

I have a merge replication environment configured between a PUBL and 5 SUB servers that are geo distributed. Most of the activity is at the publisher ( i know not the best direction- working on ...
0
votes
2answers
131 views

same query gets slower after other queries get called (using a lot of prepared statements inside stored procedures)

I have a query that, when i run it first after i restart mysql, takes 2 seconds, but when I run a sequence of other queries before it (this query belongs to a procedure between other procedures) ...
2
votes
3answers
64 views

In DB2 LUW, When should I use a 4K, 8K, or 16K tablespace rather than just creating a 32K tablespace and being done with it?

We're using DB2 LUW 10.5 & 11.1 on Windows & Linux systems, in case it's relevant to he answers. Question: Is there a time where it would be correct to use 4K rather than 32K? If so, why? (...
0
votes
1answer
26 views

Is it possible to implement minimally logged reads from a disk table [closed]

My code uses a table that links SQL Server SPIDs with CompanyIDs. For example Spid|CompanyID 1 | 123 2 | 412 Every time a request reaches the database, the request first checks this table to ...
3
votes
2answers
48 views

Tracking Down a Problem User

How do I track down a user that may be running a relatively quick query, but running it excessively? For examples, they may: be running a report over and over and over again have a book set on their ...
0
votes
1answer
33 views

Very slow JOIN + ORDER BY query only in dev environment

I have some production code and am setting up a new development environment. I found some pages were extremely slow to load. I profiled the code and found the slow query. This query is taking 75 ...
0
votes
0answers
20 views

PostgreSQL: Very high read IO on one of the servers

We have two servers dedicated to PostgreSQL 11.3: master (128Mb RAM, 16 core CPU, SSD) - all write queries and 65% of read queries slave (64GB RAM, 12 core CPU, SSD) - 35% of read queries For ...
2
votes
1answer
155 views

Improve OR inside INNER JOIN

I have this query. this is the second query. the first one was using the left/or outside the subquery, and the query plan was horrible.( Edited the question with the full sintax ): https://www....
1
vote
1answer
39 views

Is it possible to move mongodb indexes to another file system?

Is it possible to separate MongoDB files location so the indexes are on another file system? In the example below, I have database with 1.5TB of data + indexes of 330GB. I have a very fast storage ...
1
vote
1answer
58 views

What are some consequences of putting quotes in a WHERE clause for an integer column?

I have a table with a column some_column of data type integer. I've noticed that my PostgreSQL 10.6 instance is smart enough to interpret both of the queries below as same: select * from my_table ...
1
vote
2answers
38 views

How to determine if it is necessary to increase the sga and the pga in Oracle database

How to determine if it is necessary to increase the sga and the pga in a database oracle 12c, is there any procedure, any specific view where can I be guided that indicates to me that it is necessary ...
0
votes
0answers
31 views

MySQL: High CPU/Load after linux OOM killer hit

We have a single VM running MySQL 5.7.20 with 64GB memory and 4 vCPUs (Xeon E5-2690 v3 @ 2.6GHz). This MySQL node is dedicated to our Atlassian Confluence application so it's not having any other apps ...
2
votes
1answer
26 views

SQL Azure “hot” table query slowdown over time

I have an Azure SQL Database table that is acting as a "prioritized queue". As such, it gets heavily written to and deleted from over time. What's happening is that after a week or two, the DB size ...
0
votes
0answers
45 views

Does keyset/cursor pagination scale query performance better than offset pagination?

I am seeing behavior where the query that powers my infinite scrolling list seems to be slowing down as the number of rows grow. Naturally, I used offset pagination because the library I am using ...
8
votes
1answer
319 views

What are the ramifications of setting ARITHABORT ON for all connections in SQL Server?

So I've determined that the erratic behavior of my SQL Server is because of .Net SqlClient Data Provider's default setting of SET ARITHABORT OFF. With that said, I've read various articles that debate ...
7
votes
3answers
266 views

Long Running Query on Read-Only Replica that takes moments on the Primary

I've got a 4 node AG setup as follows: VM Hardware Configuration of all nodes: Microsoft SQL Server 2017 Enterprise Edition (RTM-CU14) (KB4484710) 16 vCPUs 356 GB RAM (long story to this one...) max ...
-2
votes
1answer
18 views

Slow performance - join dbo.StringList Sql Server [duplicate]

I need to update table from a certain population. The code: @IdentityNumbers dbo.StringList readonly update d set DeliveryNationalInsuranceBatchLogId = @BatchLogId, LastUpdateDate=getdate(...
0
votes
0answers
35 views

SQL Server query extremely slow. It fixes itself after rebooting [duplicate]

I have an SQL Server instance running on AWS RDS that is performing badly. A query that usually takes milliseconds to run, is taking minutes, causing my application to time out. This particular query ...
12
votes
2answers
592 views

Displaying an Estimated Execution Plan generates CXPACKET, PAGELATCH_SH, and LATCH_EX [ACCESS_METHODS_DATASET_PARENT] waits

I'm running Microsoft SQL Server 2016 SP2-CU6 (13.0.5292.0) on a 4 vCPU VM with max degree of parallelism set to 2 and cost threshold for parallelism set to 50. In the mornings, when trying to ...
3
votes
1answer
26 views

Can I apply production statistics to an empty local database to test PostgreSQL planner hypotheses?

I'm toying with the idea of exporting a database's statistics from production and applying them to a test database (same structure, small dataset). The goal is to test software for changes that ...
0
votes
0answers
37 views

Slow insert (trigger/indexes) processing

Whenever an insert is done in a table these tasks are executed: One Trigger (see down). Three indexes: one checks that a primary key is unique and the rest makes sure that two columns don't have ...
2
votes
0answers
38 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, ...
0
votes
1answer
20 views

mongodb read write locks

I'm currently investigating the possibility of using mongodb. This would contain the a collection of 100million+ documents. I need a process that will be constantly running deleting documents older ...