Performance tuning done by adjusting database parameters or physical layout of the database.
0
votes
0answers
33 views
Tuning advisor with Extended events?
With SQL traces I was able to analyze them with Database Engine Tuning Advisor to obtain basic recommendations for perf. tuning(missing indexes, statistics,...).
Now, with SQL 2012 and Extended ...
3
votes
1answer
70 views
Slow SSRS Report in production
I have an SSRS report which gets its data by firing a series of stored procedures.
Now the report is timing out big time when run in production, yet when I pull down the prod database and restore to ...
3
votes
2answers
356 views
Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec?
My Hardware Spec
Technology: Ivy Bridge
CPU: Intel Xeon E3 1245v2
Intel Smart Cache: 8MB
Cores / Threads: 4 / 8
Frequency: 3.4GHz+ / 3.8GHz Turbo Boost
RAM: 32 GB DDR3
Hard disk: 2x 2TB SATA3
Raid 1 ...
2
votes
1answer
137 views
What causes InnoDB to write 100% more pages while slowing down answering queries?
I have a somewhat big database server: 4 processors, 32 cores, 288GB RAM, 2 ethernet cards bounded together, 2 independent RAID controller cards with 1GB cache each, 24 2.5" disks, being 8 SAS, ...
5
votes
1answer
54 views
How to tell if database tuning is fruitful (PostgreSQL)
As a DBA, I regularly observe my database, tune up some queries (either getting the programmers to re-write it or add index or update statistics if that's what is needed, etc). I also tune up the ...
0
votes
0answers
45 views
PostgreSQL can't start after tuning some options
I'm having some issues with "too many clients" and I tried to change the max_connections param (currently 100). I tried to set some value like 300, but when I restart the service, I got this
...
1
vote
1answer
71 views
sp_setapprole taking 100 - 200 ms [closed]
SQL Server 2008 SP1 here.
We have a machine where sp_setapprole calls take more than 100ms and they are done many times per user operation on an application.
The developer says that in his setup, ...
3
votes
1answer
227 views
Need help with long running query
UPDATE: Adding a clustered index to each work table covering all fields reduced the query run times to su-bsecond. I consider this question closed.
Thanks for taking the time to read this post - ...
2
votes
1answer
69 views
Slow Query Gets Even Slower After Indexing
I have a MySQL database with just one table containing 8 million records. I need to query the table to find a single record that matches a specific number:
select * from my_table
where
...
0
votes
0answers
31 views
Oracle User Profile for REST API
What sort of profile would you recommend for an Oracle user that is manifested through a RESTful API; particularly where there will be a lot of asynchronous requests? So we're talking about a ...
0
votes
1answer
202 views
How to change page size in an existing sybase database
I have an existing database with 1 kb page size i want to increase that to 16kb and increase cache to take advantage of my memory for best performance. sybase documentations said you can't change page ...
1
vote
0answers
853 views
Could Oracle ORA-08103 'object no longer exists' error be caused by size of undo_retention parameter?
Had a once-a-day long running data warehousing procedure that I had written that was failing about 50% of the time. Logs were showing ORA-08103 'object no longer exists'.
ORA-12012: error on auto ...
2
votes
2answers
262 views
PostgreSQL performance degradation over time on a write intensive db
I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the ...
2
votes
1answer
69 views
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 ...
1
vote
0answers
269 views
SQL Server Database Engine Tuning Advisor vs DMV queries
What is the best way to tune the indexes on my production database? I normally use the following query :
SELECT
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
...
1
vote
1answer
408 views
Planning simple CMS: How much relation?
I'm currently in the process of designing the database for a rather minimalistic Content Management System I'm working on alone. It should allow scalability while remaining speed, especially on boxes ...
4
votes
3answers
321 views
Could running ANALYZE on all databases on large InnoDB tables show some performance increase?
So we have an old database that was fragmented until we ran OPTIMIZE on all tables.
The database/tables are about 100GB in size. Tables are InnoDB with heavy read/write activity.
There are so many ...
3
votes
1answer
405 views
Designing a user authenication (Roles & Rights) module
I am trying to model a User Authentication module for a MS SQL Server database that will be the back end to a Delphi UI Application. Basically, I want to have user accounts where the user belongs to ...
2
votes
1answer
111 views
How do you figure out what planner cost constants to use in Postgres?
I'm using Postgres 8.4 right now. Performance has started to become an issue as our tables have grown in size and our queries in complexity, so I've started to look into some performance tuning, but I ...
3
votes
1answer
340 views
NuoDB with Amazon S3
I think about developing my new mobile and web app against NuoDB. I believe in their technology. The thing that I care the most is backup and restore in case of data corruption and read / write speed ...
0
votes
3answers
199 views
Custom SQL Server Computer Build
I hope this is the right place to ask this question. I'm a user at StackOverflow, and when I perused the StackExchange websites, this was the best match I could find. So, if I've posted in the wrong ...
2
votes
1answer
798 views
Can I safely kill OPTIMIZE TABLE on an InnoDB table?
MySQL's documentation for kill warns:
Warning
Killing a REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM table results in a table that is corrupted and unusable. Any reads or writes to ...
-2
votes
1answer
179 views
when to use technology like hadoop?
I understand that MySQL and others support large enough data for storing and retrieving. I am also aware of the open source projects such as hadoop and mapreduce etc.. (only their purpose and what ...
1
vote
2answers
432 views
Restore data from remote server using “.sql” file
I have a ".SQL" file which contains data at a remote server. I could download that file to my local machine and import data in my local DB, but because of network issues, and the size of ".sql" file ...
1
vote
1answer
170 views
MySQL suddenly running very very slowly
I run a PHP/MySQL website on a fairly large server with 16GB RAM and 3 vCPUs.
Everything was flying along until this morning when I noticed MySQL down.
I tried to restart MySQL and first got the ...
2
votes
1answer
165 views
what does SHOW /*!50000 GLOBAL */ STATUS do ?
What is the difference between
SHOW /*!50000 GLOBAL */ STATUS
and
SHOW GLOBAL STATUS
I saw that in few scripts and tuning presentations the first command is being used. Is there a difference, ...
3
votes
1answer
77 views
What are some key configuration settings for using an InnoDb table for session storage?
I want to start clustering my PHP app servers but don't want to go for a sticky-sessions load balancing setup at this point. However, I want sessions that are persisted to disk and implement proper ...
1
vote
1answer
177 views
Database Tuning Advisor can't find tables across two databases
I've got a Python script which accesses two SQLServer 2008 databases. If I run it and take a trace to capture all events that occur as a result of the script, and then put that trace through the ...
4
votes
3answers
782 views
Dropped tables are not going to Recycle Bin
To start, I am a student and just getting my feet wet with Oracle.
Quick history
Virtual Box XP running Oracle express with SQL Developer.
Created a user and tablespace for myself. (Key here is ...
3
votes
2answers
215 views
Is it important to release memory back to the server?
I have a SQL Server 2008 R2 instance running on a production system. I'm not a DBA; I'm more a developer, but my client is currently asking me: How important is it to release memory from SQL Server ...
1
vote
1answer
101 views
Is it possible to allow write-through computed columns for legacy code that expects a column to be read/write?
I have this existing table for IP storage:
CREATE TABLE [dbo].[IPAddresses](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IPv4Address] [varchar](15) NULL,
[IPv6Address] [varchar](45) NULL,
...
0
votes
3answers
309 views
SQL Server : primary keys advice to my whitepaper needed
I've tired to explain to every new junior developer in our r&d team why he should use Primary Keys and how to do that. So I decided to write small whitepaper, which every new developer should ...
4
votes
1answer
131 views
Is there a way to make SQL Server's Database Tuning Advisor use more than one core?
I'm using SQL Server's Database Engine Tuning Advisor to generate recommendations for performance improvements on my database server.
For the "consuming workload" step, it appears to use all ...
7
votes
2answers
5k views
Could not continue scan with NOLOCK due to data movement
We run SQL Server 2000 and we get a few of these errors every night.
Could not continue scan with NOLOCK due to data movement
The query that throws this error is a large complex query that joins ...
5
votes
1answer
241 views
Tuning queries and indexes using index analysis tool
I've been pointed to this script from several places which apparently helps with designing indexes in Microsoft SQL Server: http://indexanalysis.codeplex.com/
The problem is I'm using Sybase ASA 11, ...
3
votes
2answers
517 views
Website is too slow when number of records increases?
I have developed a web application called Referral Program, It it developed by using PHP, MySQL. Users can joining the website and when they introduce new members, he/she gets a commission. The system ...
4
votes
1answer
289 views
Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
There's a production database I'm working on, whose 4 largest tables contain between 4 million and 10 million rows each and about 15 fields each, with indexes on different field types (numbers, ...
9
votes
3answers
6k views
MySQL table_cache and Opened_tables
I have seen people use the comparison of Open_tables and Opened_tables to assess whether the table_cache is too small in MySQL. However, I believe that Opened_tables is cumulative across uptime, so ...
10
votes
2answers
643 views
SQL Server: Has anyone used the large-page allocations tuning option?
Has anyone used the tuning option of using TF834 large page allocations. I was just reading an MS article on it and was wondering if anybody had used it and seen performance gains. Are there any ...
5
votes
1answer
3k views
How do you increase MySQL performance using current server resources?
I have a MySQL database and some of my queries became slow. Query time is not stable. Most of the queries are fast, but some of them (may be that reads and returns less data) take long time.
I know ...
4
votes
1answer
249 views
Proper Database Partitioning
I had asked a previous question "Proper technique for storing users event data" and the correct answer in my opinion was to create a database partition. Now from what I have read on it there are ...
4
votes
2answers
401 views
Creating partitions on a production database
I have a production MySQL 5.1 database that is running well but I want to improve query performances. I have never used Partitions and just going through the manuals.
I have two tables that involve a ...
0
votes
2answers
647 views
How fast should I expect to load data on a high performance computer?
I am considering a model where I use PostgreSQL COPY to copy data from a file into a table. I was wondering what kind of performance to expect on high-end hardware. An interval in MB/s would be nice ...
1
vote
2answers
444 views
Performance tuning OLTP query
I have following sqls executed million times a day, single execution isnt an issue but executing million times causing some performance issues..Thanks in advance for your help!
NOTE: data can change ...
5
votes
2answers
931 views
I get ORA-13605 when trying to run SQL Tuning Advisor from SQL Developer
When trying to use the SQL Tuning Advisor from SQL Developer I get this error.
Tuning advisor was working some days before.
10
votes
3answers
431 views
Executing same request from C# VS SSMS give different execution time
I have a request like this one
SELECT
[EstimateId],
[CreationUserId],
[EstimateStatusValueId],
[LanguageId],
[LocationId],
[EstimatorUserId],
[FilterUnitSystemTypeId],
[EstimateNumber],
...
2
votes
2answers
6k views
SGA_MAX_SIZE and SGA_TARGET
We recently increased sga_max_size to 4g from 2g but didnt increase sga_target, is it ok not to increase sga_target?? reason we wanted to increase sga is to reduce wait time on sequential reads in our ...
3
votes
3answers
3k views
AWR Report - Tuning Database
We have an OLTP system, following are our AWR reports taken during peak hour window for three days once each day. We see a lot of direct path reads; we want to add more PGA in-order to reduce wait ...
3
votes
2answers
1k views
Connection Pooling for PHP based website using Oracle 11g database
I am using Oracle 11g database server and now I am getting to see that OCI's persistent connection isn't much of a use, and a new connection is created anyway when a new user accesses the page. I ...
22
votes
7answers
4k views
Is it better to store images in a BLOB or just the URL? [duplicate]
Possible Duplicate:
Files - in the database or not?
I was wondering if there's any good reason to still use blob fields in a database. A couple of years ago I worked with a DB with a bunch ...
