Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [performance]

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.

Normally performance refers to the speed with which a system completes operations. This general consists of response time and throughput either of which could be more critical in a given situation. A system will have to meet certain performance criteria to be judged useful, and from this perspective has a subjective aspect.

Database systems often have issues with performance arising from queries with suboptimal plans that generate excessive I/O traffic. Where this happens it may be necessary to tune the system by modifying queries, adding indexes to the database or adjusting the memory allocation policies of the system. In more extreme cases the database structure may need to be modified or moved to an appropriate hardware platform.

6356 questions
300
votes
15 answers

Can MySQL reasonably perform queries on billions of rows?

I am planning on storing scans from a mass spectrometer in a MySQL database and would like to know whether storing and analyzing this amount of data is remotely feasible. I know performance varies wildly depending on the environment, but I'm looking…
haxney
  • 2,463
  • 2
  • 13
  • 7
203
votes
7 answers

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to backup the database nightly. Due to my comcast…
Patrick
  • 4,179
  • 7
  • 27
  • 27
163
votes
6 answers

Guid vs INT - Which is better as a primary key?

I've being reading around reasons to use or not Guid and int. int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case a Guid would be better than and…
BrunoLM
  • 3,313
  • 7
  • 26
  • 22
142
votes
5 answers

Possible to make MySQL use more than one core?

I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help Setup The servers are quite hefty with an OLAP/DataWarehouse (DW) type load: Primary: 96GB RAM, 8…
gbn
  • 69,343
  • 8
  • 160
  • 240
130
votes
5 answers

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some other factor. I need all of the fields (or nearly…
Joe
  • 1,549
  • 2
  • 10
  • 11
127
votes
7 answers

How to determine if an Index is required or necessary

I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now have a list of recommendations for indexes that…
misterjaytee
  • 1,373
  • 3
  • 11
  • 8
119
votes
3 answers

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? Additionally, I created an index on A, but Postgres still…
Luciano
  • 1,621
  • 3
  • 12
  • 8
109
votes
3 answers

What is faster, one big query or many small queries?

I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, we only need to use only 1 column. So would it be faster…
sudo.ie
  • 1,191
  • 2
  • 8
  • 5
91
votes
4 answers

Authoritative source that <> and != are identical in performance in SQL Server

Consider this answer on SO that reassures the asker about the <> operator that: <> is ... the same as !=. But then a commenter pipes up and says: It's true that they are, functionally, the same. However, how the SQL optimizer uses them is very…
ErikE
  • 4,235
  • 4
  • 27
  • 39
85
votes
4 answers

Why is my query suddenly slower than it was yesterday?

[Salutations] (check one) [ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer, I have a (check all that apply) [ ] query [ ] stored procedure [ ] database thing maybe that was running fine (if applicable) [ ] yesterday [ ] in…
Erik Darling
  • 36,388
  • 14
  • 127
  • 357
80
votes
6 answers

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign keys referencing the main schema and not the other…
jd.
  • 902
  • 1
  • 6
  • 6
77
votes
4 answers

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column? Edit: Not a dupe of: Would index lookup be noticeably faster with char vs…
Daniel Serodio
  • 1,109
  • 2
  • 11
  • 13
76
votes
6 answers

How do I efficiently get "the most recent corresponding row"?

I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the rows of another table. I have a table, inventory…
Tom Ellis
  • 1,499
  • 3
  • 13
  • 13
70
votes
4 answers

Can a single PostgreSQL query use multiple cores?

In recent versions of PostgreSQL (as of Dec 2013), can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
Alireza
  • 3,586
  • 10
  • 35
  • 43
69
votes
4 answers

Index Seek vs Index Scan

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan. What is the difference between and index seek and an index scan? Which performs better? How does SQL choose…
Greg
  • 3,202
  • 5
  • 29
  • 55
1
2 3
…
99 100