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
0answers
10 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 ...
1
vote
1answer
212 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 ...
0
votes
1answer
85 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. ...
0
votes
0answers
232 views

Increase select query execution time in mysql database with 15 million data

I have a table in my database which contains about 15 million data. I have to repeatedly perform join operations in my application and while applying joins into the table (containing 15 million data ...
0
votes
2answers
92 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 ...
2
votes
1answer
701 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 ...
0
votes
1answer
57 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 ...
2
votes
1answer
133 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 ); ...
0
votes
0answers
64 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 ...
1
vote
1answer
1k 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 ...
0
votes
2answers
482 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 ...
1
vote
1answer
1k 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?
1
vote
1answer
152 views

POWA doesn't show queries executed

I have successfully installed POWA (http://dalibo.github.io/powa), including all required extensions, see the following Printscreen of its operation of end email. But when executing queries in psql- ...
0
votes
1answer
90 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 ...
0
votes
1answer
63 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 ...
0
votes
2answers
305 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 ...
0
votes
1answer
45 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 ...
0
votes
1answer
61 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 ...
1
vote
1answer
2k 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 ...
1
vote
0answers
974 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 ...
0
votes
1answer
466 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, ...
1
vote
2answers
162 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 ...
13
votes
2answers
1k 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),...
0
votes
1answer
305 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 ...
2
votes
3answers
14k 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 ...
2
votes
1answer
2k 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 ...
5
votes
1answer
4k 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 ...
1
vote
0answers
84 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 ...
5
votes
1answer
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....
2
votes
0answers
42 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 ...
0
votes
1answer
55 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/...
0
votes
1answer
161 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 | -------------...
0
votes
1answer
51 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 ...
0
votes
0answers
83 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 ...
0
votes
0answers
74 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, ...
0
votes
0answers
198 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 ...
1
vote
1answer
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 ...
1
vote
1answer
117 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? ...
1
vote
1answer
134 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 ...
0
votes
1answer
57 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 ...
1
vote
2answers
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 ...
3
votes
0answers
849 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 ...
0
votes
1answer
920 views

MySQL uses more than 500% of the CPU

Everything was going fine, but since a couple of hours ago MySQL is using from 400% to 550% of the CPU. I'm getting a lot of these errors: 1003 14:03:17 [ERROR] /usr/sbin/mysqld: Incorrect key file ...
3
votes
1answer
876 views

Reading a SQL Server Execution plan

I am very new to any type of tuning but have been learning more.. One of my specific databases one of the problems identified with slow queries are a number of Implicit conversions. It is my ...
0
votes
1answer
2k views

MySQL: Adding innodb_log_file_size in my.cnf causes not starting MySQL service [duplicate]

I checked and found that InnoDB pluginis active. By default, Innodb_log_file_size=5MB. As per the MySQL Documentation, its ideal values range from 1MB to 1/N-th of the size of the buffer pool, where ...
1
vote
1answer
502 views

How do i increase the total MyISAM indexes?

I have just made a check for my server with mysqltuner. -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster ...
1
vote
0answers
210 views

Tuning MySQL On Azure Windows Server Setup

I have a MySQL 5.5 running on Azure VM Windows Server 2012. Once per second the log db gets about 2K inserts through LOAD DATA INFILE... once a day I need to run stats and when the stats are crunching ...
2
votes
2answers
623 views

Need Help maintaining multiple databases for a multi-tenant application?

i need some information about multiple databases for a multi-tenant application ? I am working on a project in which we are maintaining separate database for every company? The Companies Database ...
4
votes
2answers
646 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning. Actually the tuning of my.cnf is like below: My database engine is MyISAM | Mysql 5.1.73 key_buffer = 500M ...
1
vote
0answers
1k views

Optimization Tips For MYSQL Tuner/Primer

So I have a site that has a crap ton of traffic. I see a decent amount of latency when the site has over 1,000 active visitors on it. I have new relic installed and it seems Im only using 10% of the ...