Questions tagged [database-tuning]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
54 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 ...
user avatar
0 votes
0 answers
171 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 ...
user avatar
0 votes
1 answer
32 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 ...
user avatar
  • 103
1 vote
1 answer
182 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 ...
user avatar
  • 283
1 vote
2 answers
577 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 ...
user avatar
1 vote
1 answer
945 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 ...
user avatar
2 votes
2 answers
2k 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 ...
user avatar
2 votes
1 answer
681 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 ...
user avatar
0 votes
2 answers
210 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 ...
user avatar
  • 103
1 vote
1 answer
221 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 ...
user avatar
0 votes
0 answers
59 views

Tuning SQL Server 2012

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 ...
user avatar
2 votes
1 answer
928 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 ...
user avatar
  • 323
0 votes
1 answer
285 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. ...
user avatar
1 vote
2 answers
1k 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 ...
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 ...
user avatar
  • 41
0 votes
1 answer
68 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 ...
user avatar
2 votes
1 answer
209 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 ); ...
user avatar
  • 225
0 votes
0 answers
68 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 ...
user avatar
  • 141
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 ...
user avatar
  • 121
0 votes
2 answers
983 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 ...
user avatar
  • 201
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?
user avatar
  • 431
0 votes
1 answer
125 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
261 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 ...
user avatar
0 votes
2 answers
762 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 ...
user avatar
0 votes
1 answer
104 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 ...
user avatar
  • 171
0 votes
1 answer
65 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 ...
user avatar
  • 185
1 vote
1 answer
4k 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 ...
user avatar
  • 331
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 ...
user avatar
  • 275
0 votes
1 answer
601 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, ...
user avatar
  • 1,652
2 votes
2 answers
305 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 ...
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),...
user avatar
  • 489
0 votes
1 answer
486 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 ...
user avatar
  • 45
2 votes
3 answers
25k 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 ...
user avatar
2 votes
1 answer
3k 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 ...
user avatar
7 votes
1 answer
6k 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 ...
user avatar
  • 173
1 vote
0 answers
94 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 ...
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....
user avatar
2 votes
0 answers
49 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 ...
user avatar
  • 113
0 votes
1 answer
59 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/...
user avatar
0 votes
1 answer
189 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 | -------------...
user avatar
  • 1
0 votes
1 answer
52 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 ...
user avatar
  • 101
0 votes
0 answers
93 views

Mysql - too many Deletion - Insertion

I'm creating cron jobs for my OLAP system. I have a cron job that deletes the records from the summary table first and then insert the records in it again: Say Deleting 1 month records, and ...
user avatar
  • 199
0 votes
0 answers
78 views

MySQL 5.5 only use 1 thread and 30% cpu

i have a big issue, every day i need to update or insert like 3.3 milion rows, i have trying to do what ever i can but it still only run like 605 query/s i use VirtualBox so my setup are testing on 1, ...
user avatar
0 votes
0 answers
246 views

Performance issue after reorganizing table in MYSQL

There is MYSQL(5.6.22-log Community Edition) database environment which has databases size varies between 1-40GB and using INNODB storage engine, it's serves for the several number of OLTP based ...
user avatar
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 ...
user avatar
1 vote
1 answer
146 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? ...
user avatar
1 vote
1 answer
304 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 ...
user avatar
  • 113
0 votes
1 answer
61 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 ...
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 ...
user avatar
3 votes
0 answers
966 views

Postgres query plans and tuning parameters

I have a query for which the explain plan indicates that the cost for using a seq scan is lower than the cost for using an index that I have set up. However the actual time for the query is 20 times ...
user avatar
  • 31