Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [tuning]

92 questions
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
  • 70,237
  • 8
  • 167
  • 244
50
votes
4 answers

Are individual queries faster than joins?

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? TL;DR: If my joined query takes longer than running…
Martin
  • 2,420
  • 4
  • 26
  • 35
17
votes
5 answers

How to determine the optimal sort_buffer_size?

I read from a sample configuration file which says the following: # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a disk # based merge sort is used instead - See the…
Question Overflow
  • 1,009
  • 6
  • 16
  • 21
12
votes
2 answers

Sql Server, Is "Reorganize Files Before Releasing Unused Space" better or worse?

I could not find a satisfactory answer. I fully understand that "Shrink" will cause index defragmentation, and that can worsen performance. But, is not this option to reduce defragmentation? So, to activate this option, is it better or worse? Please…
Diego Soto
  • 223
  • 1
  • 2
  • 6
10
votes
2 answers

DTA Recommends to CREATE STATISTICS

I just ran a T-SQL query through DTA and one of the recommendations is to CREATE STATISTICS on one of the columns that is part of many of the queries in the SQL code file. My question is, how exactly do statistics aid in performance?
anon
9
votes
1 answer

How do you tune innodb_read_io_threads?

The default value of innodb_read_io_threads and innodb_write_io_threads is 4. How do you check if your server load needs a higher number of threads? As I regularly check show engine innodb status \G, there is no traceable activity: -------- FILE…
Googlebot
  • 4,551
  • 26
  • 70
  • 96
9
votes
2 answers

Learning to Optimize SQL Queries and Understand Execution Plans - Resources?

I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team. They mostly all run quite well, but some of them not so…
Tommy O'Dell
  • 295
  • 1
  • 3
  • 7
7
votes
1 answer

MySQL 5.5 - determining correct write[read]_io_threads on high-end system?

Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD . Ubuntu 64 server. Heavy Innodb load at times - ~ 2000+ queries per sec. Heavy read and write. Currently we are running on the default write_io_threads of 4. Are we "nerfing" the power of our…
Tom Geee
  • 327
  • 2
  • 6
  • 10
7
votes
4 answers

Hash Match inner join in simple query with in statement

I am running the execution plan for the following query: select m_uid from EmpTaxAudit where clientid = 91682 and empuid = 42100176452603 and newvalue in('Deleted','DB-Deleted','Added') Here is the execution plan: I have a non clustered index…
Adolfo Perez
  • 171
  • 1
  • 5
6
votes
2 answers

Backup tuning for large databases

I am tuning SQL Server backups using compression, writing to multiple files, and changing the BufferCount and MaxTransferSize options. I have a SQL Server instance with a 4 TB database and several smaller databases from a few MB to 150 GB. My…
Dave M.
  • 103
  • 6
6
votes
5 answers

Performance tuning a Great Circle distance calculation

I have a query that I am using to find locations that are within 1km of a known point. To do this, I am using the Spherical Law of Cosines formula with my latitude and longitudes. Currently, the query runs over roughly 5000 records in about 5…
Steve
  • 163
  • 7
6
votes
1 answer

Tuning dedicated Percona Server with XtraDB for OLTP

Hardware specs for an OLTP DB dual quad core xeon with hyperthreading 24GB ram Hardware RAID10 BBU Software specs Standard install of Ubuntu 10.04LTS with default LVM partitioning Installed Percona Server with XtraDB My priority is data…
Jessie Evangelista
  • 61
  • 2
6
votes
1 answer

Query tuning issues

I'm in trouble trying to tune this following query. I used to be 100% focused in the infrastructure side of the stuff, but since August this year I'm working for a processing card company that is demanding just my "dark side" - query tuning…
Marcelo Moraes
  • 61
  • 3
5
votes
1 answer

MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

When reading online about innodb_log_file_size, it says The combined size of the log files must be less than 4GB So what happens if it needs to be larger? In books and online, it says to set the innodb_log_file_size to at least an hour's worth of…
Tom Geee
  • 327
  • 2
  • 6
  • 10
5
votes
2 answers

What can I do to speed up this SQL Query?

I've created this SQL query with the help of @Dems :-) Here is some detail, I tried to make a SQLFiddle but I kept getting errors with my variables... This works in Sql Server 2008... My question is, how can I make my query faster? I know I'm doing…
Faraday
  • 151
  • 1
  • 3
1
2 3 4 5 6 7