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

6
votes
2answers
476 views

Transaction speed benchmarks for mySQL v5.6 replication - seems very slow

I'm trying to select the best configuration for our new infrastructure but got a bit confused about the results. I used sysbench v0.5 for the tests: prepare data sysbench ...
6
votes
2answers
497 views

Major Discrepancy Between SQLIO IOPS/Throughput Stats and DBCC CHECKDB Operations

I'm conducting time trials on a new SSD array running both SQLIO tests and a real-world workload of DB restores and DBCC CHECKDB calls. I'm seeing a major discrepancy between the IOPS and throughput ...
6
votes
1answer
680 views

Extremely Slow StatMan query issue with #SP2010 farm

The issue I’m experiencing is explained in a blog-post I created for this, (easy to refer to and to manage the responses / solutions in 1 location) which can be found here: ...
6
votes
1answer
316 views

Sudden increase in log_file_sync waits

I'm on Oracle 11gR2 with a 2 node RAC system. It's shared fiber storage to an EMC Clariion. Last friday things went bad..fast. All of the sudden processes that normally ran fine for years became ...
6
votes
1answer
167 views

Start/Stop MySQL

I'm seeking help to understand what happens when the following command line is executed: root@prodn$ service mysqld stop Yes, it shuts down the MySQL server so access to it is no longer available ...
6
votes
1answer
309 views

Landed as BI, but databases are a big WTF, what to do?

Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
6
votes
1answer
160 views

What is a good way to determine the performance I should expect from a PostgreSQL server?

Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
6
votes
2answers
312 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 ...
6
votes
1answer
419 views

Backup to three drives suddenly slow

The situation: I have a 1.3 TB Database (SQL 2005) that I am currently backing up to 2 network shares on separate machines and on one local USB. (Don't ask why - long story involving a DBA that quit ...
6
votes
3answers
591 views

Single query sending and retreiving duplicate data on MS SQL Server?

Very odd thing happening. It would appear that all queries against a particular database in our system are periodically "running slow". Ie "normal speed" for 5 minutes then slow for 5 minutes ...
6
votes
1answer
510 views

Using an RDBMS for querying tens of Terabytes of time-series data?

I stumbled over an algorithmic / data-structures question over on SO, which I'll short quote: (...) opinions regarding best of breed data structures to be used for indexing time-series (aka ...
6
votes
1answer
187 views

What is the expected performance impact when dropping a large table

I have a 100G+ innodb table that needs to be dropped in a production environment. I am trying to do this without interruption to service. The table is no longer being written to, and needs to be ...
5
votes
3answers
375 views

Reducing the size of a database

SQL Server newbie here. I'm a MySQL guy. I'm having a look at something for a client in their 2008 SQL Server, and need some advice. Whoever designed the database chose to log insane amounts of stuff ...
5
votes
3answers
245 views

SQL Server clustered index, index balancing and insert performance using NewID

I have a large (6db) trace table. It has a clustered key (DateTime) which is created through GETDATE(). The connection pool for connections to this database/table rises as high as 50 on average ...
5
votes
5answers
4k views

Improve sort performance in PostgreSQL?

I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
5
votes
2answers
2k views

Compare Microsoft SQL server performance between virtual and dedicated server

I have a dedicated windows 2008 r2 server at the moment and I am thinking about migrating to a virtual server because of the costs. Now I know that SQL should be on a dedicated server, but the ...
5
votes
2answers
386 views

How to design index for faster deletes

I want to speed up following piece of code delete from ssn_sdo where (art_id=@art_id and skl_id=@skl_id and @level=0 ) or (art_id=@art_id and skl_id=@skl_id and @level=1 and ...
5
votes
3answers
6k views

How can I configure MySQL Innodb to handle 1000s of inserts per hour?

I have a very high traffic website where it is possible that 1000s of new records are inserted every hour. This one error is crippling the site: PDOException: SQLSTATE[40001]: Serialization failure: ...
5
votes
3answers
590 views

SQL Server 2005 uses 1GB of RAM

Is this normal? To be exact it uses 1,068,000 KB. It occasionally shoots up to 1,400,000 if query takes too long. My total system memory is 3GB and I am running WinXP. Is there a specific amount of ...
5
votes
3answers
1k views

Is the ADO.NET Entity Framework known for generating poorly performing queries?

This question is inspired by the comment posted to the latest ServerFault blog post: Are you guys still using LINQ to SQL? I know I can use SQL Server Profiler and/or the ToTraceString method to ...
5
votes
3answers
128 views

Dynamic temporary index creation

In my undergrad databases course, my professor mentioned that some DBMS software is so advanced that it can detect when a long-running query would benefit from an index. The DBMS can then create that ...
5
votes
3answers
10k views

Optimizing InnoDB default settings

I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no ...
5
votes
3answers
395 views

Good, Bad or Indifferent: WHERE 1=1

Given this question on reddit, I cleaned up the query to point out where the issue was in the query. I use comma first and WHERE 1=1 to make modifying queries easier, so my queries generally end up ...
5
votes
3answers
3k views

Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack?

I've been wondering lately what (if any) are the improvements available in MariaDB over 'conventional' MySQL? I understand that where platform interoperability and/or backwards compatibility may be ...
5
votes
5answers
4k views

Does SQL profiler affect server performance?

I am facing a problem where the sql server 2008 fails for some high load. I need to find that load case and need to optimize the code so that it can handle the load. I have found on the internet that ...
5
votes
2answers
2k views

Index on primary key not used in simple join

I have the following table and index definitions: CREATE TABLE munkalap ( munkalap_id serial PRIMARY KEY, ... ); CREATE TABLE munkalap_lepes ( munkalap_lepes_id serial PRIMARY KEY, ...
5
votes
2answers
333 views

Create function in central database or repeat in each database?

One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database ...
5
votes
5answers
845 views

Advice with date column SQL Server 2005

I have two datetime columns in SQL Server 2005 that I need to query without the time portion of the datetime. Currently, my query looks something resembling this (just an example): WITH Dates AS ( ...
5
votes
1answer
745 views

Does MySQL still handle indexes in this way?

Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index. If a ...
5
votes
3answers
576 views

InnoDB import performance

I'm struggling with bulk importing a quite big InnoDB-Table consisting of roughly 10 Million rows (or 7GB) (which for me is the biggest table I've worked with so far). I did some research how to ...
5
votes
4answers
2k views

T-SQL INNER JOIN Optimisation

Well, I really have read so many articles, used different techniques and done some random things to do this. My problem was that a have big tables (over one million rows) and some small (with few ...
5
votes
2answers
133 views

Are existing entries immediately included into a newly created index?

Suppose I have a table Stuff with a column City and no indices including that column. I populate the table. Then I decide to create an index: CREATE INDEX [StuffOnCityIndex] ON [dbo].[Stuff](City ...
5
votes
3answers
4k views

MySQL general query log performance effects

One of our customers who has a somewhat problematic web application would like us to log all the queries for a period of 24 hours. I wouldn't know how much data it will actually write. The amount ...
5
votes
3answers
4k views

Investigating high SQL waits, especically LCK_M_U

Recently I have been struggling with SQL server performance and although we have fixed a huge multitude of basic errors in the config it is still performing badly. To clarify it is not overall ...
5
votes
1answer
2k views

In a CASE Statement, which is faster: “WHEN [field] in (0)” or “WHEN [field] = 0”?

I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc. For example: CASE WHEN ...
5
votes
2answers
2k views

The order of INNER JOIN's

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000. I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
5
votes
3answers
231 views

When you select from a table on an indexed column and order by that column in the opposite direction of the index, does the index help

Let's say I have a large table of transactions with a date field and an index on that field in ascending order. If I select that field from the table and order by in reverse order is it able to ...
5
votes
1answer
702 views

Optimum configuration for 4 SSD drives

I would like to move a SQL Server database from a shared configuration with the web-server to it's own dedicated box. My current budget will allow me to put 4 disks together in an array with a single ...
5
votes
1answer
3k views

Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes

I just started dabbling with the query cache for our CMS. Can anyone tell me (or at least give a good guess) why I get a lot of Qcache_lowmem_prunes when more than half of Qcache_free_memory is free? ...
5
votes
3answers
281 views

SQL Server how to test filegroup IO

We have a customer who is switching from SAN storage to directly attached storage. Our SQL Server database has several filegroups all of which has been deployed on the same LUN. In order to determine ...
5
votes
2answers
667 views

SQL Server performing slow

I have a Microsoft SQL Server running on an IBM server with 10 cores and 64 GB RAM. I have from 100 to 300 users working concurrently on the server. Users complain about slowness of the application. ...
5
votes
2answers
2k views

Rebuilding index has impact on mdf/ldf files SQL Server 2008

While rebuilding index have used option 'SORT_IN_TEMPDB = ON' in order to avoid unnecessary growing your user database files. What does it exactly means? Is complete process done on TempDB and does ...
5
votes
2answers
1k views

Slow ORDER BY with LIMIT

I have this query: SELECT * FROM location WHERE to_tsvector('simple',unaccent2("city")) @@ to_tsquery('simple',unaccent2('wroclaw')) order by displaycount I'm happy with it: "Sort ...
5
votes
4answers
6k views

Optimising join on large table

I am trying to coax some more performance out of a query that is accessing a table with ~250-million records. From my reading of the actual (not estimated) execution plan, the first bottleneck is a ...
5
votes
1answer
177 views

Is my function nondeterministic one

I have two functions: fn_Without_Param and fn_With_Param CREATE FUNCTION [dbo].[fn_Without_Param] ( ) ... INNER JOIN .. ON .. AND SubmitDate = CONVERT( varchar(10), GETUTCDATE(), 101 ) and /* ...
5
votes
2answers
2k views

Does a TRIGGER improve the performance?

I understand that TRIGGERs are useful, as they take care of subsequent queries, but do they have an advantage from performance point of view too? Replacing two or three queries as INSERT INTO table1 ...
5
votes
3answers
8k views

MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine

I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done. I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file ...
5
votes
2answers
1k views

Performance impact of waiting on a service broker message

I have a situation where multiple client apps send messages via the Service Broker (utilizing stored procs). These messages are picked up by yet another client app and then processed. The way the ...
5
votes
1answer
408 views

how to improve performance by using or not using table variables

I have a table variable: DECLARE @to_process TABLE ( [Id] [bigint] NOT NULL, [SequenceId] [bigint] NOT NULL, ... ) INSERT INTO @to_process ( Id , SequenceId ... ) SELECT ...
5
votes
4answers
4k views

MySQL: Index when joining to tables not being used (Performance optimizing question)

I need to optimize the following query: SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid FROM blogposts JOIN articles ON articles.blogpost_id = blogposts.id WHERE ...