An evaluation of whether a system works well enough to be fit for purpose. Normally performance refers to the speed with which a system completes an operation or set of operations over time.

learn more… | top users | synonyms

-1
votes
1answer
112 views

Approximate MySQL Query Time For A Where Clause On A 10 Million Records Table

I'm looking forward to designing a database (in MySQL) but before I start I need to have some calculations to make sure that my application will remain efficient even if there are 10 Million+ rows ...
1
vote
0answers
171 views

Poor performance in my pgpool cluster

I've configured a postgres cluster with pgpool2 using the following link. The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for ...
1
vote
1answer
64 views

How can I improve performance of update query in PostgreSQL

I have a table with about 80 million records. I have added a new attribute (heigth_category) which I want to fill based on another attribute (heigth). If the heigth is between 0 and 6, I want to fill ...
0
votes
1answer
121 views

PostgreSQL - Return unique combinations of columns based on where clause

This is quite a long question, please bear with me. So I'd like to first explain I have a database of firewall logs created using the following command: CREATE TABLE firewall_logs_mapped ( ...
0
votes
0answers
40 views

query comes in slow query when log_queries_not_using_indexes is enabled

This query comes in slow query log when log_queries_not_using_indexes is enabled. SELECT a.id, d.id AS city_id, d.city FROM booking a, sub_area_master b, area_master c, city_master d WHERE ...
6
votes
1answer
230 views

Estimating IO Requirements for Bursty Usage

We have an application that queries a SQL database periodically throughout the day. There are periods of zero or only light activity, interspersed with individual requests for relatively large ...
2
votes
1answer
90 views

Avoiding Deadlocks

We currently have a trigger-based system in place that does the following: When records in certain tables are modified, a trigger fires that makes a field NULL in a varying number of records in ...
0
votes
0answers
50 views

TempDB causing high IO writes - dependent on server's max memory setting [duplicate]

I have a mirrored SQL Server 2012 database. The first server has a hugh tempdb write IO load when the maximum memory limit is set above 8192 mb. These 8192 where the original setting for both the ...
0
votes
1answer
53 views

DB2 - how to track IOPS statistics?

DB2 9.7. Is there some easy way in every moment go have statistics about IO operations per second? I am not interested for hit ration on Buffer Pool but really on IOPS? THANK YOU
0
votes
1answer
58 views

RAID0 vs multiple files in file group

I have a server that gets updated and backed up once a day and processes queries the rest of the day (selects). I'm trying to improve performance of the update process and the selects. I'm not worried ...
0
votes
0answers
58 views

H2 database binary file full of @^@^@^@^

I am investigating H2 database performance and was curious to why the database file was several megabytes long when only storing hundreds kilos of text, I therefore opened the database file with vim ...
6
votes
2answers
2k views

What is the fastest way to insert large numbers of rows?

I have a database where I load files into a staging table, from this staging table i have 1-2 joins to resolve some foreign keys and then insert this rows into the final table (which has one partition ...
2
votes
1answer
148 views

Specifying columns in LEFT JOIN through subquery beneficial performance wise?

I'm trying to understand how JOINS are handled, and was wondering about the following: Imagine a big query concerning a lot of tables, I'm really looking for small optimizations as it's a pretty ...
1
vote
1answer
95 views

Oracle views not using indexes [closed]

I am experiencing the unexplainable - at least to me. I have some tables in my production database which have some indexes on. We then have a number of views which merge the data across tables to ...
0
votes
1answer
837 views

How index work in oracle? [duplicate]

I want to understand : How index work in oracle. How they are stored in the disk. How they increase the efficiency of a query? By third point I means how Index contribute to the efficiency of ...
3
votes
1answer
62 views

What inefficiency does sharding & partitioning attempt to address? [closed]

I have never implemented sharding or partitioning, but from what I understand, multiple servers are used to distribute large amounts of data. Is this because of a limited hardware budget or to ...
0
votes
2answers
117 views

Tips on using SQL access advisor in Oracle

Please share how to use oracle SQL access adviser for single or multiple queries. you can assume that we know the sql ids for the queries we are interested in.
1
vote
2answers
104 views

What useful information can I get from ASH report which I cannot get from AWR report?

What useful information can I get from ASH report which I cannot get from AWR report? What are the common scenarios in which its better to get information from ASH than from AWR?
0
votes
1answer
52 views

MySQL: What points should we consider when create composite index

There are multiple views behind the logic of creating composite index in a table. Creating composite index is obvious when we have composite primary key. Beside this, we create composite index on the ...
0
votes
0answers
47 views

Slow exporting SAP Data Services to MySQL (Oracle is faster)

We've a MySQL 5.1 server and we are trying to import data from SAP Data Services from another server. It works but it takes 3h! We tried to setup an Oracle XE server to test the same operation and it ...
0
votes
0answers
46 views

How to optimize subselect join on large tables

There exists a need to migrate from one definition of a column to the next, so a backfill must occur. In order to get a grasp for the task at hand, I needed some background on the data being updated. ...
1
vote
0answers
156 views

Postgres database zero down time production deployment

I need some idea about how I can deploy a financial application in production environment using PostgreSQL DB with zero down time. I have two servers in Master-Slave design. Currently I am giving ...
4
votes
1answer
183 views

SQL Server CPU spikes - possibly non-query related - suggested indicators

I'm looking for suggestions for indicators I could monitor to discover what is causing these cpu "blunted spikes". Some known information: sqlserver.exe is confirmed as the process consuming the ...
2
votes
1answer
96 views

Does Windows Server File Compression slow Oracle DB performance?

I'm starting a new a new contract and I get to troubleshoot their Oracle instance... I'm more apps and sql, but I'll be trying to figure out their crashes and slow performance. Go figure. I can ...
3
votes
0answers
214 views

How to properly monitor the number of PostgreSQL database connections?

I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 ...
0
votes
0answers
109 views

Mysql View Performance on Aggregate Functions

I've a table that contains weekly statistical infos. ex. Society - Week - Year - Data AAA Inc - 1 - 2014 - 10250 AAA Inc - 2 - 2014 - 5235 BBB Inc - 2 - 2014 - 3210 ... Over this table i need to ...
6
votes
2answers
215 views

Should I nest dependent outer joins in SQL Server?

I have heard mixed information on this and am hoping for a canonical or expert opinion. If I have multiple LEFT OUTER JOINs , each dependent on the last, is it better to nest them? For a contrived ...
4
votes
1answer
116 views

Settings for a MySQL backup only slave instance

I have two instances of MySQL running one a server, set up as master and slave. It's all working smoothly together and I'm only using the slave instance for backups to avoid locking up the live ...
2
votes
2answers
342 views

MySQL on SSD - what are the disadvantages? [closed]

Recently, I heard that I should not host my MySQL database on an SSD drive because there is a big chance of disk failure and data lost. Given that 20% of all queries in my database are INSERTs and ...
1
vote
0answers
83 views

Fastest way to get the length of the intersection of two text arrays in PostgreSQL

This is what I am currently using: CREATE OR REPLACE FUNCTION array_intersect(a1 anyarray, a2 anyarray) RETURNS anyarray AS $$ select array ( select unnest($1) intersect select unnest($2) order by 1 ...
4
votes
1answer
383 views

Database under heavier load — all SQL queries start timing out after a period of hours/days

I make a web application which has experienced heavy growth in the past week and it appears my database is seizing up because of it. You can see that everything seemed to be OK until about 3AM this ...
0
votes
1answer
218 views

What is the Difference between IN and Not IN performance wise?

I am new to database.I am having doubt that what is the difference between IN and NOT IN performance wise. When I am using IN, takes less time for the same query(Logically same query) when I use NOT ...
1
vote
1answer
441 views

InnoDB: very bad performance on INSERT/UPDATE

Lately I have really big problems with INSERTs and UPDATEs. On my website 80% of all queries are SELECTs but in query slow log there are only INSERTs and UPDATEs. My dedicated server has 32GB RAM, ...
5
votes
1answer
1k views

MySQL 5.1 to 5.6: massive performance hit

Okay, this is possibly three questions. I want to move my existing MySQL 5.1 database using MyISAM to 5.6 using InnoDB for what I think are a host of obvious -- and possibly even good -- reasons. ...
0
votes
1answer
202 views

Improve Insert performance for 200 Million rows

How can the following insert statement be improved - which varies from 20mins on certain days to 70-140mins on most other days. SELECT DISTINCT od.SourceID, od.OrderID, od.OrderLineNo, ...
0
votes
2answers
71 views

How to change table/indexes structure for a query with two aggregated colums

Do you have any suggestion on how to improve a table structure or its indexes in order to have a single query run faster? The table has this structure: docid | character varying(17) | ...
1
vote
2answers
80 views

Does RAM taken up Shared Memory connection counts against Max Server Memory?

I have an app that's local to the SQL Server and thus has a Shared Memory connection to it. I was wondering whether the RAM taken by the connection (including data transfers) counts against the max ...
0
votes
1answer
309 views

Postgres 8.3 faster than 9.3?

Our software product currently ships on Windows 7 with Postgres 8.3 as its database. On a busy site, there may be 24 automated systems generating 100 rows (x100 columns) per minute, with 3-10 human ...
1
vote
1answer
109 views

Partition : understanding files and file groups

We are trying to partition a large history table. Being new to partitioning, I was trying to understand the concept of files and file groups. File groups can be set up in following three ways : 1. ...
0
votes
2answers
458 views

oracle active sessions waiting for scheduler

Load on my 11g RAC looks like this: and I don't understand how to get insight into what this "scheduler" wait class is. It seems like "scheduler" that it refers to is the process that runs ...
4
votes
1answer
145 views

PostgreSQL does not use all available IOPS on a single operation

Using PostgreSQL 9.3 on Windows 7x64, CoreI7, SSD. Assume a PostgreSQL table with 8 Int32 columns and following C# code to fill the table. using (var connection = new NpgsqlConnection("User ...
5
votes
1answer
3k views

MongoDB performance vs. PostgreSQL with 5.5 million rows / documents

Can someone help me compare these queries and explain why the PostgreSQL query executes in just under 2000ms and the MongoDB aggregate query takes almost 9000ms and sometimes as high as 130K ms? ...
4
votes
1answer
352 views

Is ASYNC_NETWORK_IO wait type anything to worry about?

In looking at the list of sprocs that take long time to execute, one stands out as causing the most wait. However, most of that wait (81%) is ASYNC_NETWORK_IO and I know why: the sproc transfers ...
3
votes
1answer
784 views

How to improve Procedure Cache Hit Ratio?

From what I can tell Procedure Cache Hit Ratio below 95% is a problem. On my box, the values hover from 85 to 95%. How do I fix this issue? The server seems to have plenty of RAM so that ...
2
votes
2answers
144 views

Autogrowth, shrink and database performance

I have already asked some questions about Autogrowth and shrink. But I have some doubts related to it. 1)I have a database which grows about 300 MB every month. So is it best to set Autogrowth of 300 ...
0
votes
1answer
88 views

Poor SQL performance with raid10

Maybe I'll try my luck here. Server Fault site was quite hostile for non-professional server user. I recently bought HP ProLiant ML310e Gen8 v2 server with 8G's RAM and 4 HP 1TB 6G 7.2k rpm SATA ...
1
vote
2answers
141 views

Improving a search query

I have a query for search function. Basically search function allowed user to define what they "have" and "want". Then this query will filter out all the possible result which created by other user. ...
3
votes
1answer
738 views

Why is SQL Server using a clustered index scan for a self referencing FK cascade delete

I have made an example to allow me to show what my issue is: Setup: CREATE TABLE [dbo].[Test]( [TestId] [bigint] IDENTITY(1,1) NOT NULL, [ParentTestId] [bigint] NULL, CONSTRAINT [PK_Test] ...
0
votes
1answer
102 views

DB disk space requirements comparision between MySQL versions

I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used. In one lab, MySQL version 5.0.77 is installed. The DB ...
2
votes
1answer
208 views

Non-Clustered Index key columns to cover a variable where clause

To whoever reads this, sorry for the long question and thanks so much for your time. I'm needing some help on understanding what columns should be key/non key in the following situation, and what ...