Questions tagged [database-tuning]

Performance tuning done by adjusting database parameters or physical layout of the database.

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

Aurora mysql throttle during writes

I came across a situation where I need expert opinion. Our application ingests data into table A in aurora mysql 3.02 every 5 seconds. This is a write intensive process. During this time, I am using ...
Falcon's user avatar
  • 91
0 votes
1 answer
121 views

Different performance of SQL Server instances on same server

I have 4 development SQL Server 2016 instances on a virtual server (128 GB, 12 virtual processors). Each instance is configured the same (16 GB memory, automatic processor and I/O affinity masks). ...
pvallero's user avatar
0 votes
1 answer
2k views

AWS RDS Postgresql and max_worker_processes

AWS RDS Postgresql 12.10 According to https://www.enterprisedb.com/postgres-tutorials/comprehensive-guide-how-tune-database-parameters-and-configuration-postgresql, max_worker_processes Set this to ...
RonJohn's user avatar
  • 615
-2 votes
1 answer
373 views

Can min_wal_size bigger than max_wal_size?

is it possible if min_wal_size bigger than max_wal_size ? What happen if it does ? What are the circumstances when min_wal is bigger than max_wal ? I am using PG 12. Thanks
padjee's user avatar
  • 177
0 votes
2 answers
986 views

SQL Query, how to know if is resource intensive? I/O

I am not a SQL guru, but I am troubleshooting an issue with our marketing platform as its become slugish and slow, I've requested the top 50 heavy/resource intensive queries executed on our sql server ...
David Garcia's user avatar
0 votes
0 answers
1k views

How to correctly set wait_timeout for MySql server

I have a dedicated server to run a WordPress website. I use Nignx + php fpm + MySql 8.0. Php processes are set to run for a maximum of 60 seconds, and mysql is used just to run this website. I checked ...
Andrea C's user avatar
0 votes
1 answer
65 views

What dataset(s) is Marcus Winand using in his book "SQL Performance Explained"?

I am currently reading SQL Perfrmance Explained by Marcus Winand. I would like to follow along with the queries and examples he used to explain database tuning. It would greatly allow me to understand ...
luminare's user avatar
  • 103
1 vote
1 answer
498 views

Why is the execution time on Datagrip longer on MySQL 8 than MySQL 5.7 on a new Linux Server?

I am migrating from a 4 year old setup to a brand new high performance server and am experiencing slower performance than on the old machine. The old setup is a Ubuntu 16.04 Server on bare metal Intel ...
merlin's user avatar
  • 293
1 vote
2 answers
3k views

Postgresql: does effective_cache_size includes shared_buffers?

I am tuning a PG installation with 32GB of RAM. The question is: which one of the following sentences is true? effective_cache_size + shared_buffers ~= 30GB (PG knows that it has shared buffer plus ...
collimarco's user avatar
2 votes
1 answer
2k views

How to determine right value for innodb_sync_array_size

How do I determine the right value for innodb_sync_array_size in MySQL? We have version 5.7 deployed on AWS RDS with 32 vCPUs, and it sees about 26 concurrent threads on average. When I measure waits ...
Shahid Thaika's user avatar
2 votes
2 answers
4k views

Tuning Java Pool after getting ORA-04031

I'm running Oracle 12.2.0.1 Standard edition on Windows 2012 RC2. I'm NOT running Oracle's Automatic Memory Management. We are seeing these in the alert log: ORA-04031: unable to allocate 4096 ...
TrojanName's user avatar
2 votes
1 answer
1k views

Why does my MySQL page cleaner thread step up its minimum CPU usage over time and never decrease back to zero?

I’m running a MySQL 5.7.29 instance on Ubuntu 16.04 on a dedicated Amazon EC2 t3a.XLARGE instance (4 vCPUS, 16 GiB Memory connected to an iOPS optimized EBS volume with 1000 provisioned iOPS) and have ...
CoffeeNotJava's user avatar
0 votes
2 answers
413 views

Optimization: Increase Innodb_pool_size useful?

We run an AWS RDS instance with 8vCPUs and 64GB memory. All tables are using InnoDB engine. I have kept the default setup of innodb_pool_size to be half the memory. What I see is that almost 30Gb of ...
Kaymaz's user avatar
  • 103
1 vote
1 answer
497 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 ...
fernandalinsr's user avatar
0 votes
0 answers
92 views

Tuning SQL Server 2012 [closed]

We migrated from Oracle to SQL Server, the new server has good resources and should burn through data. When reading OS MasterMap data it consumes over 50% of cpu and takes several minutes to load. I ...
Clives-online's user avatar
2 votes
1 answer
1k views

Adhoc queries memory consumption in SQL Server

I have a SQL Server 2012 and it has just one (1) database in it. When I generate a server dashboard report it shows that 50% of CPU usage is related to my database and the rest is associated to Adhoc ...
Commander's user avatar
  • 333
0 votes
1 answer
399 views

opened_files large, open_files tiny

on my mariadb server OPENED_FILES is currently 2,775,453 and OPEN_FILES never seems to exceed 65. I understand that opened_files is cumulative and is meaningless without a time period. ...
Daniel Garcia's user avatar
1 vote
2 answers
2k views

With partitioned table, How can I use hints to group each partition separately

Suppose I have the following table data(partitioned_key_index, some_dummy_measure) Assume that partitions are of an equal large size. with Oracle 11g. The end result should be like this ...
Fadi Bakoura's user avatar
3 votes
1 answer
2k views

Oracle 12c - Slow query to tables ALL_CONS_COLUMNS and ALL_CONSTRAINTS

We have successfully upgraded Oracle 11g to Oracle 12c. So far it's been going well. But We noticed one problem, queries made for Oracle's constraint tables (ALL_CONS_COLUMNS and ALL_CONSTRAINTS) are ...
Lee's user avatar
  • 61
0 votes
1 answer
77 views

Data modeling with the goal to get the best performance in Oracle and SQL Server [closed]

I have a question about how I can model my stock database in order to get the best performance possible. In SQL Server or in the Oracle, each update executed generates a little lock. I'd like to ...
Midnight biker's user avatar
2 votes
1 answer
303 views

Recommended settings for a small table with high update rate [closed]

I have a small table which can be explained as follows: create table mytab ( primary_key_column number, a number, b number, c number, d number, e number, f number ); ...
FORTRAN's user avatar
  • 235
0 votes
0 answers
76 views

Postgresql behaviour between diferent stages

I have three Postgresql instances (Postgresql clusters) in three diferent stages. The instances are the sames with the same datas and the same parameters. But i do not have the same behaviour between ...
Mika's user avatar
  • 331
1 vote
1 answer
3k views

Low Memory Usage High CPU mysqld

I'm managing a server with medium to high traffic. It serves a wordpress website. It seems that it use a lot of CPU (over 300% at some points) and always stack around 15% of memory usage. It is a ...
fat_mike's user avatar
  • 121
0 votes
2 answers
1k views

High "Pending normal aio writes"

During daily batch operations (mass delete/upsert operations), regular throughput is sometimes affected, e.g. operations such as a single lookup on a PK or a small range (7 rows examined, 1 row ...
dland's user avatar
  • 199
1 vote
1 answer
2k views

Is pgtune still valid for newer PostgreSQL versions?

Since pgtune has not been updated since more than 4 years ago, should it still be used with newer PostgreSQL versions? (say, >=9.3) What about the online version?
Joril's user avatar
  • 441
0 votes
1 answer
161 views

Configuring Transactional Replication for an OLTP Database

We conduct backup/restore (every 24 hours) on an OLTP database for reporting and SSRS queries. This prevents contention from simultaneous writes and reads; preventing locking, blocking, writer-reader ...
user avatar
0 votes
1 answer
378 views

Table and Primary Key sizes in Oracle

I am analyzing the size of some tables in Oracle, and I noticed that the primary key occupies almost the same size of its related table. To be clear, my statistics are the following (approx.): NAME ...
Diego Queiroz's user avatar
0 votes
2 answers
945 views

reduce number of innodb pages read by a query

In my mariadb I have a large innodb table (600 millions rows). Simple queries on the table using a secondary index perform slowly so I wanted to check the number of innodb pages read to fulfill one of ...
DoubleT28's user avatar
0 votes
1 answer
162 views

I need to reduce the size of Database file -- I am confused between DBCC Shrinkfile and Data compression options

I need to reduce the size of Database file. I am confused between DBCC Shrinkfile and Data compression option. DBCC shrinkfile will reduce the actual database file size as far as I know but Data ...
Puskar's user avatar
  • 171
0 votes
1 answer
69 views

multiple indexing issue [closed]

I used Database Engine tuning advisor for indexing a table and when I am checking indexing , it showing multiple indexing on same table . as shown in fig I am wondering for these question as ...
speedyraz's user avatar
  • 185
1 vote
1 answer
5k views

Mysql innodb_log_buffer_size change

I'm new to Mysql, and i'm reading this in a book: "A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big ...
andrea's user avatar
  • 341
1 vote
0 answers
1k views

Necessary tuning settings for SQLite databases

I am working on a SQLite DB containing ~30 .db files. Each of these .db files corresponds to one table, e.g., the "table1.db" file contains a "table1" table, "table2.db" contains "table2" table and so ...
monamona's user avatar
  • 285
0 votes
1 answer
622 views

What is the purpose of Database Tuning? [closed]

This sounds like a stupid question but when posed with it on an exam yesterday, I wasn't entirely sure how to answer...I mean I know you have to put indexes on certain fields to speed up slow queries, ...
leeand00's user avatar
  • 1,702
2 votes
2 answers
357 views

Innodb using key cache?

As Innodb is my default engine, all my tables are innodb. But while monitoring with the help of a tool, I found a constant spike in key_write_requests and key_read_requests. These two variables are ...
Aravind's user avatar
  • 45
13 votes
2 answers
2k views

SELECT TOP 1 harms query performance; is there a dba-accessible way to overcome this?

In a production application (C# talking to SQL Server 2014 Standard) there's a query that looks like this, below. Most of the time it runs in milliseconds. But occasionally (for certain values of @Id),...
O. Jones's user avatar
  • 531
0 votes
1 answer
811 views

What happens when the buffer pool size is insufficient in mysql

Let us assume I run a query for a table whose size is very huge. My buffer pool size is as low as possible. What happens now? whether I will get an error regarding the insufficient space in buffer ...
Aravind's user avatar
  • 45
2 votes
3 answers
28k views

MySQL database uses too much CPU

I have a MySQL database which uses too much CPU. I am actually performing benchmarking to the web servers which queries the database. Right now the database is being the bottleneck, it uses too much ...
user3580316's user avatar
3 votes
1 answer
5k views

What indexing to be used over string columns(text type) with string length around 3000 characters

I am using open source columnar dbms (postgresql+Cstore). I am trying to speed up the response time of queries. My database has tables with strings mostly. String columns contain huge data (up to ...
user86583's user avatar
7 votes
1 answer
8k views

PostgreSQL doesn't use all the RAM it could

I'm having performance issues with my PostgreSQL 9.4.1 server. I have tuned the server using the usual best practices (pgtune + google). Here's the relevant config: # <snip> the default config ...
panta82's user avatar
  • 173
1 vote
0 answers
105 views

Tuning MySQL MyISAM for large joined updates

I have a database with two tables that I would consider small. I need to update values on the larger of the two tables (1,100,000 rows, 75M MYD, 120M MYI) from the join of the two tables. The smaller ...
sirlark's user avatar
  • 131
5 votes
1 answer
2k views

Postgres 4x Slower Than It Was

Our Postgres performance has gone down to 1/4 of what it was, and we can't figure out why. We have two machines with identical hardware (let's call them A and B): Intel(R) Xeon(R) CPU E5-4640 0 @ 2....
user1517922's user avatar
2 votes
0 answers
53 views

Tuning Issue / Temporary Tables

I've got a MySQL database (MyISAM) that handles a lot of read/writes and uses temporary tables quite frequently. Recently it's started having massive load spikes at peak load, and noticed that there ...
Stephen91's user avatar
  • 113
0 votes
1 answer
64 views

Feasibility of Automated and Continuous Performance Tuning in the future [closed]

First a quick background: I'm a software developer, not a DBA and have been developing for more than 10 years. Recently I had to optimize a stored procedure and had to run execution plans, create/...
Adolfo Perez's user avatar
0 votes
1 answer
231 views

Creating a flexible table containing prices and periodes

I'm creating a system of availability for rooms and with a calendar according to the days, with every day have it's own price. Room Type | 01 | 02 | 03 | 04 | 05 | ... | 31 | -------------...
kali's user avatar
  • 1
0 votes
1 answer
56 views

Big Size Database and increasing number of users [closed]

Me and my colleagues are using quite big size normalized database. Basically it's used for warehouse management and daily minimum 100 of the user perform data manipulation operations. 80% of the ...
Hassan's user avatar
  • 101
1 vote
1 answer
1k views

Large sequence cache in Oracle

There was a situation of 108,368,168 executions along with 7,373s elapsed time for "update seq$ set increment$=:........" statement while loading massive(i.e. populating million rows) data in the ...
Suresh Gautam's user avatar
1 vote
1 answer
180 views

75% of RAM for Innodb_buffer_pool size

I have read many documentation including percona and MariaDB documentation where they recommend setting innodb_buffer_pool_size to 70-80% of RAM. My question is, is that total RAM or available RAM? ...
The Georgia's user avatar
1 vote
1 answer
530 views

Does InnoDB Log Files take RAM space or Disk Space

When I set InnoDB Log File size to 512M, does it take the space (512M * 2) from RAM or the SSD Disk space? When I explored, I can see that the log files are located inside /var/lib/mysql folder. So ...
Neel's user avatar
  • 113
0 votes
1 answer
68 views

Effects of monolothic databases on performance and security?

Please note: although I'm specifically calling out MySQL here, I would also appreciate/entertain generic answers that apply to all/most relational systems. As a developer I don't see the design ...
smeeb's user avatar
  • 115
1 vote
2 answers
2k views

Very high load avg for MySQL database [closed]

I have a backend server which response to my clients app, it has more than 20k~25k hit per second and this make my server load average very high, I believe that my MySQL config is not appropriate. In ...
Ali's user avatar
  • 111