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.
16
votes
3answers
16k views
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 then DBA for MySQL so need some help
Setup
The servers are quite hefty with an ...
10
votes
1answer
10k views
Multi cores and MySQL Performance
Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the ...
16
votes
6answers
10k views
About single threaded versus multithreaded databases performance
H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded.
My question is: when does a multi-thread database become more interesting than an ...
8
votes
1answer
2k views
Reasons for occasionally slow queries?
We are running MySQL 5.1 on Windows Server 2008 R2.
We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to ...
43
votes
7answers
23k views
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 ...
3
votes
3answers
3k views
Best of MyISAM and InnoDB
Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
12
votes
2answers
3k views
How do I properly perform a MySQL bake-off?
I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better ...
17
votes
5answers
12k views
How do you tune MySQL for a heavy InnoDB workload?
Assuming a production OLTP system with predominantly InnoDB tables
What are the common symptoms of a mistuned/misconfigured system?
What configuration parameters do you most commonly change from ...
39
votes
6answers
8k views
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 ...
4
votes
3answers
6k 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 ...
8
votes
1answer
877 views
Optimising BCP performance for BLOB data
I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between ...
1
vote
1answer
270 views
decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
i recently testing the move of our db from one server to another.
as part of this process i copied all the data to the new db and started testing it.
as a comparison i run a store procedure whose ...
6
votes
4answers
3k views
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
I had to write a simple query where I go looking for people's name that start with a B or a D :
SELECT s.name from
spelers s
WHERE s.name LIKE 'B%' or s.name like 'D%'
order by 1
I was wondering ...
5
votes
1answer
609 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 ...
2
votes
1answer
170 views
optimal table design mysql with primay key and varchar value
I have a table:
id (PRIMARY KEY AUTO_INCREMENT)
value (UNIQUE KEY)
which currently holds over 50 millions rows (and growing fast) and is hit hard:
process 1 queries this table (>200/sec) with a ...
5
votes
3answers
3k 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
3k views
How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
7
votes
4answers
1k views
Help me choose a RAID level combination for a SQL Server 2008 instance
I am going to rebuild one IBM 3400 server from scratch. This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2.
I am going to make new RAID configuration. I have 6 SCSI ...
6
votes
3answers
808 views
Testing stored procedure scalability
I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level ...
4
votes
1answer
1k views
Capabilities of InnoDB INSERT Performance
Hi I am running the most recent version of Percona Server.
Server version: 5.5.24-55 Percona Server (GPL), Release 26.0
I have a 10 cpu box of these characteristics.
processor : 0
vendor_id ...
7
votes
3answers
2k views
SQL Server performance sudden degradation
I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full ...
6
votes
3answers
2k views
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 ...
11
votes
5answers
3k views
In MySQL, does the order of the columns in a WHERE clause affect query performance?
I am having performance issues on certain database queries that have large possible result sets.
The query in question, I have three ANDs in the WHERE clause
Does the order of the clauses matter?
...
8
votes
2answers
2k views
How to know when/if I have too many indexes?
Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement...").
From my understanding every added ...
8
votes
3answers
682 views
How is LIKE implemented?
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?
The naive approach would be to inspect ...
2
votes
1answer
263 views
Select * from statement execute very slowly, innodb io read speed is low
I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: ...
2
votes
1answer
972 views
Is SQL Server Backup single or multi-threaded?
If you perform a...
BACKUP DATABASE [DB_Name] TO DISK=N'c:\backups\db_name.bak' WITH STATS=10
Will that perform a single or multi-threaded write?
Our Server support company advised us that if we ...
152
votes
14answers
35k views
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 ...
20
votes
1answer
941 views
USING construct in JOIN clause can introduce optimization barriers in certain cases?
It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.
I mean this key word:
SELECT *
...
13
votes
2answers
653 views
Why does DELETE leave a lingering effect on performance?
At the end is a test script for comparing the performance between a @table variable and a #temp table. I think I've set it up correctly - the performance timings are taken outside of the ...
7
votes
2answers
546 views
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?
...
6
votes
2answers
2k views
Performance of a Trigger vs Stored Procedure in MySQL
A post here on DBA.StackExchange (What are the best practices for triggers to maintain a revision number on records?) has spawned an interesting question (at least, interesting to me) regarding ...
4
votes
4answers
2k 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 ...
11
votes
4answers
587 views
When using an ORM, what are some things to watch out for in your database design
What are some database design gotchas to watch out for when you know the database will be accessed using a ORM?
As an example, I'll note the 2100 parameter limit on SqlServer's RPC call. This is a ...
8
votes
3answers
2k views
How do indices impact query performance?
Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the ...
6
votes
2answers
1k views
SQL Server Join/where processing order
After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
6
votes
5answers
550 views
Inclusion of ORDER BY on query that returns no rows drastically affects performance
Given a simple three table join, query performance changes drastically when ORDER BY is included even with no rows returned. Actual problem scenario take 30 seconds to return zero rows but is instant ...
5
votes
2answers
574 views
SQL Server gets slow over time until we have to restart it
We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite ...
9
votes
4answers
789 views
Is it possible to increase query performance on a narrow table with millions of rows?
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
4
votes
1answer
120 views
How should we handle rows which won't be queried once they are old in PostgreSQL?
We have a table in a PostgreSQL database which is growing on the order of millions of rows per day.
Each row consists of:
ID
Foreign user ID
Date and time
Other data
The date and time aren't ...
3
votes
2answers
302 views
How can I save a severely fragmented database/table?
I've recently made a discovery that might explain my slow SQL run times.
I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
2
votes
2answers
295 views
Performance gain by moving a large table from a database and referencing with a synonym
We have SQL Server 2008 R2 database which is currently about 80Gb.
About 10Gb of this is down to one table, and I wondered if there would be a performance gain if I moved this table into it's own ...
2
votes
2answers
2k views
Using MySQL triggers or transactions?
I want to ask your opinion in using MySQL triggers or transactions in a website.
Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
1
vote
3answers
499 views
whether a big (but not used) ibdata1 slow anything down
Hi I have an ibdata1 that is around 10 gig. I have gradually migrated to 'file per table' using the innodb_file_per_table=1 option such that there are no big tables in ibdata1 anymore.
I recently ...
1
vote
2answers
534 views
Solving high page load time using SQL Profiler
I'm looking into a performance of a login page taking 10-13 seconds to load. I'm using a DEMO database to test the performance and tweak to see any improvement. I'm using SQL Profiler to capture what ...
0
votes
3answers
172 views
INNER JOIN Giving time out on large database
Getting time out on this script
UPDATE
uk_data AS ud
INNER JOIN
uk_pc AS up
ON ud.cat10 = up.WardCode
SET
ud.cat8 = up.Latitude,
ud.cat9 = up.Longitude;
uk_pc Table ...
0
votes
3answers
2k views
sql server instance using maximum cpu usage
I have a SQL server 2005 instance installed on the server machine with two vm
Server Specification are
*Processor:* Xeon
Ram : 6GB
OS: window server on vm ware
It is running asp.net application ...
32
votes
3answers
5k views
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 ...
17
votes
3answers
2k views
Efficency of stored procedures vs raw queries
I have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries? I am specifically interested in SQL Server but would ...
9
votes
1answer
555 views
SQL Server Cache Flush and Disk I/O
We're busy load testing an OLTP system we've developed in .NET 4.0 and runs SQL Server 2008 R2 in the back. The system uses SQL Server Service Broker queues, which are very performant, but we are ...
