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

learn more… | top users | synonyms

1
vote
1answer
3k views

Optimize PostgreSQL for a lot of INSERTS and bytea updates

What we have (software): PostrgeSQL 9.3 with base configuration (no changes in postgresql.conf) Windows 7 64 bit Hardware: Intel Core i7-3770 3.9 Ghz 32 Gb RAM WDC WD10EZRX-00L4HBAta Drive ...
1
vote
2answers
165 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 ...
1
vote
2answers
1k views

Restore data from remote server using “.sql” file

I have a ".SQL" file which contains data at a remote server. I could download that file to my local machine and import data in my local DB, but because of network issues, and the size of ".sql" file ...
1
vote
1answer
135 views

Is it possible to allow write-through computed columns for legacy code that expects a column to be read/write?

I have this existing table for IP storage: CREATE TABLE [dbo].[IPAddresses]( [ID] [int] IDENTITY(1,1) NOT NULL, [IPv4Address] [varchar](15) NULL, [IPv6Address] [varchar](45) NULL, ...
1
vote
1answer
84 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? ...
1
vote
1answer
2k views

Planning simple CMS: How much relation?

I'm currently in the process of designing the database for a rather minimalistic Content Management System I'm working on alone. It should allow scalability while remaining speed, especially on boxes ...
1
vote
1answer
20 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 ...
1
vote
1answer
109 views

Maintenance plan using external tool to execute query and stored procedures giving off no results

My situation is as follows: I am using a third party tool (VisualCron) to run stored procedures and sql queries on several database servers. The stored procedures are from http://ola.hallengren.com/ ...
1
vote
1answer
277 views

MySQL suddenly running very very slowly

I run a PHP/MySQL website on a fairly large server with 16GB RAM and 3 vCPUs. Everything was flying along until this morning when I noticed MySQL down. I tried to restart MySQL and first got the ...
1
vote
1answer
355 views

Database Tuning Advisor can't find tables across two databases

I've got a Python script which accesses two SQLServer 2008 databases. If I run it and take a trace to capture all events that occur as a result of the script, and then put that trace through the ...
1
vote
2answers
597 views

Performance tuning OLTP query

I have following sqls executed million times a day, single execution isnt an issue but executing million times causing some performance issues..Thanks in advance for your help! NOTE: data can change ...
1
vote
1answer
18 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
68 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 ...
1
vote
1answer
76 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 ...
1
vote
1answer
46 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
38 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 ...
1
vote
0answers
296 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
161 views

sp_setapprole taking 100 - 200 ms [closed]

SQL Server 2008 SP1 here. We have a machine where sp_setapprole calls take more than 100ms and they are done many times per user operation on an application. The developer says that in his setup, ...
1
vote
0answers
4k views

Could Oracle ORA-08103 'object no longer exists' error be caused by size of undo_retention parameter?

Had a once-a-day long running data warehousing procedure that I had written that was failing about 50% of the time. Logs were showing ORA-08103 'object no longer exists'. ORA-12012: error on auto ...
1
vote
0answers
586 views

SQL Server Database Engine Tuning Advisor vs DMV queries

What is the best way to tune the indexes on my production database? I normally use the following query : SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], ...
0
votes
3answers
480 views

SQL Server : primary keys advice to my whitepaper needed

I've tired to explain to every new junior developer in our r&d team why he should use Primary Keys and how to do that. So I decided to write small whitepaper, which every new developer should ...
0
votes
3answers
380 views

Custom SQL Server Computer Build

I hope this is the right place to ask this question. I'm a user at StackOverflow, and when I perused the StackExchange websites, this was the best match I could find. So, if I've posted in the wrong ...
0
votes
4answers
650 views

What can cause Reads to slow down on SQL Server 2008R2?

Our primary DB is 15GB in size. The server has 16GB of RAM. We are using the simple recovery model. Lately we've noticed that our custom apps lookups - just basic reads with adhoc SQL statements - ...
0
votes
2answers
218 views

What is the default size of text field in Postgres?

In the official documentation (http://www.postgresql.org/docs/9.1/static/datatype-character.html), it states text field is variable unlimited length. I wonder what is the default size of this field ...
0
votes
1answer
74 views

Indexes and Optimizations for Logging System

I'm designing tables for a new log system and am having trouble figuring out what indexes I'll need (I'm a developer, not a DBA), and am also open to any suggestions/criticisms/etc. for making the ...
0
votes
2answers
927 views

How fast should I expect to load data on a high performance computer?

I am considering a model where I use PostgreSQL COPY to copy data from a file into a table. I was wondering what kind of performance to expect on high-end hardware. An interval in MB/s would be nice ...
0
votes
1answer
366 views

SQL Server 2012 Tuning process exited unexpectedly. DTAEngine

I am using Windows Server 2012 x64 and SQL Server 2012. I am running the Database Tuning Advisor with these settings : Advisor plan cache. Database workload master. Check my database all the ...
0
votes
1answer
963 views

How to measure a perfomance of SQL Server database? [closed]

I have the task to improve the performance of a SQL Server 2012 database (one of 4 in an instance) by 60% confirmed by corresponding statistics. So, I need to measure "performance" of the RDBMS ...
0
votes
1answer
50 views

Access and filter in oracle plan

I came to a execution plan where oracle choose to access the index but instead of using the columns as predicate it only filter by them. My question is what are the reasons that can cause that(all ...
0
votes
3answers
191 views

Large database - too much time spent opening and closing tables

I have a MySQL server (5.5.31) with 450 databases of some 200 tables each. (Yes, 90,000 tables) When we're under high load, there's over 100 concurrent queries, of which 70% are either opening or ...
0
votes
1answer
2k views

Database partitioning - Horizontal and Vertical sharding - Difference between Normalization and Row Splitting?

I am trying to grasp the different concepts of Database Partitioning and this is what I understood of it: Horizontal Partitioning/Sharding: Splitting a table into different tables that will contain a ...
0
votes
1answer
2k views

Oracle 11g Enterprise downgrade to Standard One

I'm checking possibilty of downgrade Oracle 11g from Enterpise Edition to Standard One edition. I've cross-checked edition comparison with select name from dba_feature_usage_statistics where ...
0
votes
1answer
33 views

Retrieve list of matched words in PostgreSQL

I'm new to PostgreSQL and really more than your more basic queries/inserts. I've created a TSVector column to my table that I'm searching and have set the column to look at the specific column in ...
0
votes
2answers
2k views

How to optimize MySQL for large BLOB updates

I have a table that holds large BLOBs (up to 10M), and is updated frequently. The problem is that UPDATE statements can take up to 1 second to execute, and due to app design this blocks UI. I need to ...
0
votes
0answers
11 views

Which DBMS for huge amount of time-based archive data? [closed]

I'm currently planning the desired rewrite of some components that have been developed with C# and SQL Server. One of the main tasks is the continuous logging of time-based values in an industrial ...
0
votes
2answers
21 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 ...
0
votes
1answer
36 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 ...
0
votes
0answers
14 views

Database Engine Tuning Advisor - is number of queries important or not

By number i don't mean number of unique signature queries I mean raw number For example i have this query select name from my table where id=3 It is a single unique signature Having it only 1 ...
0
votes
2answers
77 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 ...
0
votes
1answer
78 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning ... Actualy the tunning of my.cnf is like below My database is in MyISAM | Mysql 5.1.73 key_buffer = 500M ...
0
votes
0answers
51 views

Performance and maintainablity of a large MySQL table with hundreds of columns

For a project of mine I need to have a table with around 400 hundred columns of type nullable indexed VARCHAR(128), this table is going to store at least 50 million rows and has one column of type ...
0
votes
0answers
186 views

After RAM upgrade on 32-bit Oracle server, ORA-04031: unable to allocate x bytes of shared memory

We are in the process of upgrading our Oracle database server at work, from SUSE Linux Enterprise Server 10.4 / Oracle 10g / 4GB RAM (Intel 32-bit) to Scientific Linux 6.4 / Oracle 12c / 8GB RAM ...
0
votes
0answers
207 views

PostgreSQL can't start after tuning some options

I'm having some issues with "too many clients" and I tried to change the max_connections param (currently 100). I tried to set some value like 300, but when I restart the service, I got this ...
0
votes
0answers
41 views

Oracle User Profile for REST API

What sort of profile would you recommend for an Oracle user that is manifested through a RESTful API; particularly where there will be a lot of asynchronous requests? So we're talking about a ...
0
votes
1answer
1k views

How to change page size in an existing sybase database

I have an existing database with 1 kb page size i want to increase that to 16kb and increase cache to take advantage of my memory for best performance. sybase documentations said you can't change page ...
0
votes
1answer
73 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 ...
-2
votes
1answer
202 views

when to use technology like hadoop?

I understand that MySQL and others support large enough data for storing and retrieving. I am also aware of the open source projects such as hadoop and mapreduce etc.. (only their purpose and what ...