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
1answer
30 views

MySQL SELECT slow, but only 2 x 300K rows and indexes

Have the MySQL SELECT query below that is awfully slow. It takes ~1.0 seconds to execute despite having only 300K rows and indexes, so I would love to find a way to get it to execute faster since it's ...
2
votes
1answer
23 views

What could cause a sleeping session to have an open transaction?

For one of our SQL servers there has been a proc which creates a blocking mess: That stored proc completes under a sec most of the times but somehow is leaving transaction open. What i see from ...
0
votes
0answers
2 views

Queries does not get into performance_schema.events_statements_summary_by_digest

I have an Elixir application and want to do some performance measurements to get bottlenecks. To get query statistics I'm using Percona Monitoring and Management tool. I know that it uses ...
2
votes
3answers
278 views

Execution Performance Issue with Similar Query

I have been having some performance issues running a simple query on PostgreSQL on a table that has about 3 million rows with a join on a table that has about 120 rows. If I run a query only on the ...
-1
votes
0answers
6 views

On reducing MYSQL disk activity

Following this topic, I want to reduce the disk activity for my Moodle database. Currently, the following variables are set innodb_buffer_pool_size 8589934592 innodb_buffer_pool_chunk_size 134217728 ...
0
votes
1answer
24 views

faster ALTER COLUMN

Is there a method to do an ALTER COLUMN in postgres 12 on an huge table without waiting a lifetime? I try to convert a field from bigint to smallint : ALTER TABLE huge ALTER COLUMN result_code TYPE ...
1
vote
1answer
85 views

Can one database affect the performance of another in MongoDB?

We have a fairly beefy server (15 GB RAM, 2 cores) running a reasonably large MongoDB instance. The Mongo instance hosts two separate databases that communicate via application code. The larger ...
0
votes
2answers
94 views

select query with where clause having variable taking more time to execute [closed]

I have a query which selects a couple of rows from a view based on the variables declared. I ran the same query in lower environments and to my surprise, I got the result in less than 3 minutes ...
1
vote
3answers
496 views

MySQL DELETE statement doesn't use index although the same SELECT query does

I've got a table with ~30 million rows (and soon twice/triple times more) where I have to do quite regular updates. The table structure is like the following: id, cookie_id VARCHAR(45), country ...
1
vote
1answer
156 views

A MySQL EXPLAIN number of rows discrepancy

MySQL 5.5.49-log More questions on the query in Why does it use temporary? (MySQL) (the query is the same but the question is different): I have the following table (filled with many rows): CREATE ...
2
votes
1answer
86 views

Disk I/O increased when changing RBR to MIXED

I'm experiencing some replication issues. I have a Master-Slave replication with mysql; the slave had binlog-format = ROW, Last sunday (3 days ago) I changed the format to MIXED (this is master too; ...
1
vote
3answers
2k views

Postgres 10 Query waits on IO: BufFileWrite causing failure to get new database connections

I have a query (attached with query plan) that is run every 5-7 seconds from every device that is running our dashboard (typically, 500+ devices at peak time). This query, at the outset looks like it ...
2
votes
1answer
189 views

How to deal with seldom used big database and postgresql?

I loaded in PostgreSQL (9.3) OpenStreetMap data for whole europe (400gb). Then I installed a geocoding api (nominatim) that queries the database with gis queries. My problem is : This database is ...
1
vote
1answer
53 views

High CPU usage with MySQL RDS

I'm running t3.large MySQL instance and having high CPU usage issue. There are tons of data stored and tables in one database. After rebooting the instance everything is pretty good and all of queries ...
0
votes
2answers
29 views

Wrong index been peeked

I have an index that filters 99% of the table i.e. ix_magic_composite (for that query arguments). When I add another or filter it chooses the wrong index, i.e. fTS even if I create an index that ...
0
votes
2answers
42 views

Fastest way to update column value

I need to set all values of a given column foo to a constant varchar bar. Is there a faster way to do that than with the vanilla UPDATE mytable SET foo = 'bar'? Facts: This is an Oracle MySQL 8.0.20 ...
2
votes
1answer
82 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 ...
3
votes
3answers
481 views

Is a join faster with 5 tables joined together Or 5 separate queries? [duplicate]

I have confusion that I already have in my application 5 tables say a,b,c,d,e and have relationship with each other like a.b_id is primary key of b and chain goes on. Now my question is that which ...
3
votes
1answer
101 views

What if all the non clustered indexes on my table were filtered indexes?

I have a table that is used heavily. This table has 370,370 records. When I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [ImageSize] order ...
0
votes
2answers
435 views

Tuning mysql 5.7.1 for predominantly write workload on huge partitioned innodb tables

I recently got a task to tune MySQL for predominantly write workload on multiple innodb tables which are huge and partitioned. About the application: It's Nimsoft suite, so the SQL queries are not ...
-4
votes
1answer
34 views

What are the best practice to design social network with a lot of reports and data mining [closed]

Lets assume that I'm creating a social network like instagram or facebook with petabytes of data and my needs are: 1: High availability 2: Data mining and NLP (basically anything related with AI) 3: ...
0
votes
1answer
40 views

Postgres Performance degradation

I have a Postgres 9.6 database and after some time (15 h), database performance starts to decrease dramatically, to the point where we get client side timeouts due to a very slow response from the ...
1
vote
1answer
187 views

SQL Server : Memory pressure,Spiked user connections and CPU usage

I have the below sql server environment. SQL Server 2016 standard edition. 128 GB RAM 96 GB alllocated to SQL Server 8 tempdb's with 1 GB each. Have a major database with 2.5 TB size. Reporting also ...
1
vote
2answers
219 views

MySQL swaps on system with more than enough RAM

Om my new dual AMD EPYC 7401 24-Core (128G RAM) MySQL starts swapping even after a restart with no data in memory. NUMA # numactl --hardware available: 8 nodes (0-7) node 0 cpus: 0 1 2 3 4 5 48 49 ...
1
vote
1answer
189 views

How to influence the planner on Postgresql

I have the following: a complex query, 'match', that returns about 200 rows (ids) a huge table, 'usernames', indexed on id that maps them to usernames When I try to left join the query (1) with the ...
0
votes
2answers
325 views

How to improve performance of frequently updated table in mysql?

my table structure:: CREATE TABLE `gps_device_data`( `imei_no` bigint(15) NOT NULL DEFAULT '0', `ioelementpriority` int(1) DEFAULT NULL, `longitude` int(15) DEFAULT NULL, `latitude` int(15) ...
0
votes
0answers
25 views

Best field type for cryptocurrency big numbers?

I'm using PostgreSQL for my cryptocurrency exchange database, the question is: for saving currency amount (numbers) with their precision (like 323232323232323.45454545 ~ 23 digit + 1 dot: 15 digit ...
0
votes
2answers
381 views

Slow Sum/Group By over large amount of rows

Please can you help with a slow query that we have with a large amount of data (~500K rows). We're on Postgres 10. SELECT mike_romeo, SUM(hotel) as counts FROM whiskey_lima WHERE yankee = 'kilo' ...
1
vote
1answer
77 views

MySQL Database on RDS with memory issues

I am facing high memory utilization issue on RDS MySQL server. We are having other issues too, can someone give me a help? My database has 120GB, including indexes. Can anyone help me out to ...
3
votes
3answers
423 views

Is it normal for disk activity to disturb MySQL performance?

I'm running MySQL 5.5 on a server with two SSDs in software RAID-1 configuration. The filesystem is ext4. When any process (external to MySQL) produces a large amount of disk IO on the server, I find ...
-2
votes
0answers
18 views

The performance of transaction_isolation in MySQL [closed]

I know that transaction_isolation has 4 valid values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE, and the order above is from least amount of consistency and protection to the ...
0
votes
1answer
160 views

MySQL- Performance implications of changing a column collation from latin1_swedish_ci to latin1_bin

I am a developer so was hoping someone with DBA experience could help me out here. I am using a MySQL database (MYSQL 5.6). I came across a situation where a particular column stores Ids generated ...
0
votes
1answer
89 views

Query Tuning Issue [closed]

I have been trying to tune this stored procedure for the last several days. I have tried Data compression on the large tables to see if this improved I/O performance but it was no real change. ...
0
votes
1answer
28 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 ...
3
votes
2answers
18k views

Analyze memory usage of PostgreSQL – why is it growing constantly?

I am inserting millions of rows into a PostgreSQL 9.5 database and observe a constant growth of memory usage. As the tables are not that large and the operations performed (the insertions trigger a Pl/...
2
votes
1answer
61 views

Auto Update Statistics threshold SQL Server 2016

since SQL Server 2016 the default auto statistics update threshold changed in that way that it uses a smaller threshold depending on the table size. In our application we periodically update ...
99
votes
5answers
73k views

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
0
votes
2answers
84 views

MySQL - Simple update is very slow and causing high load average with large amount of server

Simple update is very slow and causing high load average with large amount of server : table name : CREATE TABLE IF NOT EXISTS `LISTE_PROMOTION3` ( `id_list_prom` int(11) NOT NULL AUTO_INCREMENT, ...
0
votes
1answer
15 views

Create partial index with single value

I have PostgreSQL 12 DB with a table with a several columns, two of them are a bigint ("numCol") and a boolean ("boolCol"). numCol can have any integer number, and 0 is about 0.1%-...
0
votes
2answers
305 views

mysql bad performance on hosting server

I have website running on a shared server and there are some moments that server is extremely loaded, I was able to capture some settings, could you guys take a look and maybe you see something really ...
4
votes
1answer
20k views

statistics State in MySQL Processlist

I have a busy MySQL Server having query SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM ...
1
vote
3answers
54 views

Best way to choose which columns to index to improve performance

I use a MSSQL Database for my web application and now the user audit(USER_AUDIT) records in the application have crossed beyond 5 million records/rows. Every day the performance of the application is ...
0
votes
1answer
1k 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 ...
0
votes
0answers
23 views

Postgres 12 create table as simple select + hash join takes 8+ hours for 100GB?

I'm using a "create table as select ..." statement to create a copy of a subset of the columns in a ~100GB table (400 million records). I am also performing a hash join with another table ...
0
votes
1answer
569 views

Database ExecuteSQL(MySQL client run out of memory)

I've an online game the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec the problem the memory usage still increasing i dun know why ?? i mean if we suppose that ...
0
votes
1answer
81 views

mongorestore only one field of a document

i'm trying to restore my users : { _id: ObjectId, created_at: Date, preferences: String } I don't know why but created_at field from my users all reseted. So i need to use my .bson backup file ...
2
votes
0answers
39 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 ...
0
votes
1answer
23 views

SQL Server Slowest Query is NULL

I am looking at both the SQL Server expensive queries report and the query below, but both are showing this mysterious NULL query as the slowest query on my server. Is there any way I can find out ...
0
votes
0answers
21 views

Is 96k rows INSERTed in ~45 seconds slow or normal? [closed]

I just coded a script which created a PostgreSQL table, prepared an INSERT, downloaded a CSV file with 96k rows (which accounts for maybe 5 of those seconds it took to run) and then looped through the ...
2
votes
2answers
101 views

SQL Server - quite big data flow - Disk became the bottleneck … what now?

After a lot of searching & follow up of the system we are getting out of ideas to effienctly solve our issue ... hence this question. We suspect (are rather sure) our storage became the bottleneck ...

1
2 3 4 5
119