Stack Overflow
Stack Overflow

Questions tagged [database-tuning]

deals with optimizing the performance of a database. It can be about file design, and selection of the DBMS, OS and platform.

Database tuning is about maximizing the performance of a database. It can include hardware, software, file system, OS and DBMS selection. See the wikipedia article for more.

158 questions
1
vote
1 answer

Why this Statement Show Index Scan in Exec Plan?

I have two statements that return the same result, but produce different execution plans. First >>> Index Seek Second >>> Index Scan Can anyone explain why? Example CREATE TABLE OrderDetails (intOrderId int, intItemId int, dtOrderDate Datetime,…
Mostafa Elmasry Abo Yara
  • 11
  • 1
1
vote
1 answer

how to distinguish which variables are used for innodb engine or for MyIsam engine?

When I searched some status variable by using below command and got: mysql> show global status like '%key%'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | Com_assign_to_keycache | 0 …
user1342336
  • 967
  • 2
  • 16
  • 28
1
vote
4 answers

I want to extract a random id from a MYSQL database

I am trying to extract a random article who has a picture from a database. SELECT FLOOR(MAX(id) * RAND()) FROM `table` WHERE `picture` IS NOT NULL My table is 33 MB big and has 1,006,394 articles but just 816 with pictures. My problem is this query…
user1986815
1
vote
0 answers

Blackhole engine optimization / table locking issue

I am adding a lot of rows to a table with BLACKHOLE engine. Processlist shows that a lot of queries are waiting for table lock. MySQL profiler shows that INSERTs to that table are the biggest cause of why my DB is overloaded. I have already…
Andrew
  • 11
  • 1
1
vote
1 answer

Find least recently used mysql table index

I am cleaning up duplicate indices from (innodb)tables in a mysql database. The database has about 50 tables. While I can check for duplicate keys using pt-duplicate-key-checker, I was wondering if there is a tool that could help me find out least…
A Null Pointer
  • 2,261
  • 3
  • 26
  • 28
1
vote
1 answer

postgresql.conf optimization

My postgresql server is extremely slow. Especially when I execute several queries at the same time in different threads, which somtimes causes the postgresql server to be unresponsive for 5 to 15 seconds. I wonder if I made a mistake in the…
Joel
  • 3,427
  • 5
  • 38
  • 60
0
votes
1 answer

SQL tuning -- high number of "Bytes" in explain plan

Could you give any thoughts on what are possibly causing 8.2 is having high number of "Bytes"? Thanks in advance. 8.2 INDEX UNIQUE SCAN S_ORG_EXT_P1 -T146208 (Cost = 1 Rows = 9390061 Bytes = 112680732…
iwan
  • 7,269
  • 18
  • 48
  • 66
0
votes
0 answers

How can I fix MySQL replication lag on Windows when slave_io and slave_sql are both running with 'copy to tmp table' in Slave_SQL_Running_State?

i have a master - slave replication setup on windows OS, everything was working fine until few hours ago, i restarted my slave, slave_io is running, slave_sql is running, no error in the show slave, yet my slave lagging behind my master. In the…
Shevy
  • 21
  • 2
0
votes
1 answer

Oracle Database performance tuning (on database level)

I have a big table in oracle database (it's almost 400 million rows) and a program which connects to that table and executes a query. I don't have any possibility to modify the program neither the database structure. The problem is that the query…
canonier
  • 1
  • 4
0
votes
1 answer

what should be minimum ratio of dead tuple for a table to be considered for VACUUM FULL in Postgres

I am a developer and looking for an advise on optimisation or maintenance of Postgres database. I am currently investigating on commands which helps in clean up/defragmentation of DB and release some memory to filesystem as DB disk storage space is…
Rob Wilkinson
  • 1,131
  • 5
  • 18
  • 34
0
votes
1 answer

Real Time issues: Oracle Performance tuning (types / indexes / plsql / queries)

I am looking for a real time solution... Below are my DB columns. I am using Oracle10g. Please help me in defining table types / indexes and tuned PLSQL / query (both) for the updates and insertion Insert and Update queries are simple but here we…
VJS
  • 2,891
  • 7
  • 38
  • 70
0
votes
1 answer

What values need to be least selected to optimize indexes with database engine tuning advisor

For MSSQL 2008 R2 database what values need to be selected minimum to find required indexes when profiler trace given to the database engine tuning advisor ? These are the values that can be selected at…
Furkan Gözükara
  • 22,964
  • 77
  • 205
  • 342
0
votes
0 answers

how to tune postgres for IO waits

I have a java application writing and reading some data on postgress database. On running long duration test i see failures after a continues buildup of IO waits. Any suggestions to further dig down on what could be causing this IO buildup
Anmol Dubey
  • 115
  • 2
  • 13
0
votes
0 answers

I need to change the temp table to a CTE in part of my Stored Procedure

Create table #temp_user (group_id int) insert #temp_user Exec sp_user_acct XXXX,XX,X It is working but not a good performance. How can I change it with something below? (CTE) Create table #temp_user (group_id int) ;with My_CTE as (select * from…
BDEZ
  • 27
  • 6
1 2 3
…
10 11