Questions tagged [database-tuning]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
174 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
72 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
181 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
55 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
564 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
126 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
63 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
432 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 ...
0
votes
1answer
877 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
137 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
85 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
54 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
268 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
44 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
915 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
413 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
151 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
293 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
12k 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 ...
4
votes
1answer
3k 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
41 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
160 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
73 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
195 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
941 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
114 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
126 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
56 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
837 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
887 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
844 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
482 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
207 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
613 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
643 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 ...
1
vote
1answer
574 views

Postgres: my “data/backup” directory is way too large

In my postgres 9.3 data directory, there exists a subdir called "backup" that is 6.1G compared to my pg_xlog which is only 130M. I understand that pg_xlog is the WAL logs, so what is in the backup? ...