The discipline of Operational and Architectural Management of Database Systems.
4
votes
1answer
89 views
How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?
According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause
some ...
4
votes
1answer
78 views
After Rackspace server creation, PostgreSQL query planner doesn't work as expected
We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
4
votes
2answers
85 views
How to properly configure PostgreSQL RAM usage?
Using PostgreSQL, we have several hundred concurrent processes doing regular database access. We have our data spread out through several database servers, each of which houses a connection pool with ...
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 ...
4
votes
1answer
428 views
Mongo Collection `Size` is *larger* than `storageSize`?
I recently compacted my collection using the command:
db.<collectionName>.runCommand( "compact" )
And now my collection size seems to be bigger than size on disk!
SECONDARY> ...
4
votes
2answers
285 views
SQL Server - DBO without Backup
Similar to my previous Question : Prevent Non Copy Only Backups, how do I create a user that can do everything to my database take backups at all. I've granted them every role except ...
4
votes
1answer
198 views
Speed inserts in database with a table with +47 million data per each year, containing more than 10 years?
I'm moving my database to mysql but there is one table that is pretty big (more than 47 million rows for each year) and it takes years to insert all the data.
I already tried everything I found in ...
4
votes
1answer
330 views
Oracle AWR shows missing stats for SQL ID as top elapsed statement
Why SQL ID has null number of executions and elapsed time / execution in AWR report. SQL ID is among top elapsed command on AWR but no details stats whoing time / execution. Would it be because shared ...
4
votes
0answers
68 views
Does PgBouncer create a separate pool for every database config entry?
If I set up a pgbouncer.ini config file with several database entries, does pgBouncer create a separate pool for each one? I would think so, but have not been able to confirm this.
For example, ...
3
votes
2answers
545 views
VACUUM and VACUUM FULL do not return space to OS
I'm running Postgres 8.3.3. I have a postgres database on a Linux filesystem that is quite full. I've run VACUUM and VACUUM FULL, yet none of the space has been returned to the OS. When I observe ...
3
votes
3answers
87 views
Issue after moving the ib_logfile1 and ib_logfile0 files
I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of ...
3
votes
2answers
620 views
Rebuilding index has impact on mdf/ldf files SQL Server 2008
While rebuilding index have used option 'SORT_IN_TEMPDB = ON' in order to avoid unnecessary growing your user database files.
What does it exactly means?
Is complete process done on TempDB and does ...
3
votes
3answers
114 views
Is this database fine for calculation?
I am doing an application called as Invoice Application.For that I have my database is like this.
Now my problem comes when I am taking the tax part in my database.As I have made tax as an option ...
3
votes
2answers
155 views
How can I organize my tables?
My database contains more than 200 tables. When I want to modify or insert a table, I find it difficult to find it among all those tables.
Is there any way to collect related tables in a folder ...
3
votes
1answer
3k views
How do I open a MySQL Database Dump?
I work for a doctor's office, and we are in the process of switching EMR systems. The previous system has provided a physical 'dump' of the database. I however, don't know how to open this database ...
3
votes
2answers
87 views
Reducing Log Impact During Re-Indexing
We use Ola's maintenance solution and its great.
Regardless of method for re-indexing a major friction point with IT is the amount of log generated during the weekly re-indexing process. For a 1TB ...
3
votes
1answer
114 views
SQL Server Restore from one database to another
One of our devs backed up a dev database, and then restored it in production. It's a new database for a new app that was deployed last night intentionally to prod.
Now in the backupset table ...
3
votes
1answer
813 views
How to connect multiple users/schemas in Oracle 11g?
I'm almost new user to databases, so I think I'm doing something wrong.
I have the database created.
I created 3 users, 1 it's an Admin, 2 are just users.
I created 2 roles, 1 for the Admin and 2 ...
3
votes
2answers
254 views
What exactly is “pinning” in relation to indexes?
When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions.
It is part of a test question, where ...
3
votes
1answer
854 views
What memory should be given to the variables of my.cnf ?
Installing a MySQL server is quite easy. However, after installing, configuring the server is an important task for a DBA.
Having 2GB of RAM and 250GB of disk space, how should I tune MySQL server so ...
3
votes
1answer
370 views
What are the concepts that need to be gone through for MySQL DBA?
I am a MySQL Developer, besides I am handling some DBA activities like
backup, restore, mysql installation, etc at a top level.
Now I wanted to shift to DBA, So I wanted to know in depth, like which ...
3
votes
2answers
84 views
Two corrupted indexes found by DBCC CheckTable
I'm wondering if this problem is solvable by dropping/recreating the two indexes. Both are rarely used, nonclustered, non PK, non FK indexes.
Here is the dbcc output:
Table error: table ...
3
votes
1answer
273 views
Moving postgresql data to different drive
I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my ...
3
votes
2answers
101 views
What are sensible/rational character limits in a database e.g. for a persons name
This is a general question about database admin.
Is there some kind of standardization for character limits on fields in a DB? It makes sense to specify character limits on the database end for ...
3
votes
1answer
148 views
Can I check what the allocation unit size is on my drives via a query?
Assuming that I do not have access to windows server, can I run a script from SSMS that would allow me to check whether allocation unit size is formatted with 64K?
3
votes
2answers
811 views
What is the point of the TABLE_CATALOG column in INFORMATION_SCHEMA.TABLES?
In MySQL's table INFORMATION_SCHEMA.TABLES, there's a column named 'TABLE_CATALOG'. The documentation is sparse on this column and I'm wondering what the heck is the purpose of this? Any killer-app ...
3
votes
3answers
812 views
Suggestion required for Large Database Optimization and performance
We are creating a large database with estimated growth rate 200 million+ records per annum. The database is use for analysis purpose and we will generate different tabular and graphical reports from ...
3
votes
2answers
68 views
Load Testing Crash Course
Can someone point me at a good resource to get a crash course in load testing. I have a sql server machine that I need to load test but I havent been thruogh the process before. Also we do not have ...
3
votes
2answers
1k views
pt-table-checksum help required
I am trying to figure out pt-table-checksum, as I am using it for the first time.
It look likes the documentation is complex and not easy to understand stuff.
I have a complex replication topology ...
3
votes
1answer
977 views
Does `Seconds_Behind_Master` show exact Slave Lag from Master?
Does Seconds_Behind_Master show the correct result?
I have also Implemented the mk-heartbeat replication monitoring.
It was showing the other result in comparison with Seconds_Behind_Master.
Which ...
3
votes
1answer
85 views
How can I copy specific tables into one SQL Server Table into a separate table automatically?
I'm not sure if this is possible. Looking for some help with a specific issue. I am building an iPhone application with a data layer written in Ruby that will connect to SQL Server 2008. For various ...
3
votes
3answers
125 views
What is a good guideline for sampling for statistics updates
Scope: A billion record table that is indexed in 10 ways. Rate of change per day is 1/2 a percent.
Some of the indexes are monotonously increasing (datetime/timestamp), yet the common queries most ...
3
votes
1answer
59 views
Administration of MySQL Users
What is the best practice for keeping and maintaining MySQL users? Say for example I have 5 programmers and a PHP application connecting to a database. Should I create 6 different users 1 for each ...
3
votes
1answer
578 views
Master and Slave having same id's
I am working as a remote DBA for some of the production servers.
There was replication from Master to Slave.
There is something changed in the scenario they have made one of the Slaves as master ...
3
votes
2answers
529 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 ...
3
votes
1answer
74 views
Could too many idle connections affect PostgreSQL 9.2 performance?
Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
3
votes
1answer
382 views
What queries is Oracle executing, how frequently, and time-taken?
I have a (Java) web-application, that of course executes many queries (selects, inserts, updates, deletes) to the Oracle Database.
I want to find out the following for last 7 days:
SQLQuery ...
3
votes
1answer
105 views
Easiest Way to Move 100 Databases
I need to move about 150 databases from one server to another server.
SQL Server 2008 Web Edition
SQL Server 2012 Preview (in a different datacenter - East Coast Azure)
I was planning on moving ...
3
votes
1answer
79 views
Inconsistent information from different DMVs
I've a server with 12 GB of physical RAM. I just lowered its 'Max Server Memory (MB)' setting from 10 GB to 8 GB.
If I look at the processes' committed memory in Perfmon, I see:
select *
from ...
3
votes
0answers
23 views
Does avg_query for PgBouncer include update and inserts?
Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage:
avg_query
Average query duration in ...
3
votes
1answer
69 views
Tools and methodologies to keep to DBs aligned
2 DBs having schemas that represent the same semantic objects.
The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres.
Once in a while ...
2
votes
3answers
181 views
How to validate and upgrade my knowledge of SQL Server with MCSA
My story is that I am working now almost 10 years as an administrator, I deal with SQL Server and other IT stuff, most of my knowledge is self taught by reading books, dealings with problems and ...
2
votes
4answers
1k views
Small sample database for practicing database administration?
I'm looking for a portable sample database that
can be created from a script
has a simple ER model
has little data for database administration practicing purposes.
I know that there is e.g. ...
2
votes
2answers
451 views
Generalisation in databases
I am working on a Hospital Management project in DB2. I have one table for staff with a primary key of staff_id. The hospital has to have different staff... e.g doctors,nurses,e.t.c.
Is it better to ...
2
votes
2answers
95 views
which option is better for not slow down the performance of queries?
I am developing a system that records customer payments, when a customer makes a payment, the system must update the account and show the payment made and all previous payments, is here when the ...
2
votes
2answers
61 views
Why doesn't DELETE + REORG free diskspace (DB2)?
In DB2 I have a table containing large binary data. Now i purged the whole table and ran runstats, reorg, runstats, but the amount of disk space taken does not change. What could be wrong here?
The ...
2
votes
1answer
239 views
Solve conflict between affinity mask and I/O affinity mask?
I executed this on one of our SQL Server machines:
EXEC sp_configure 'show advanced options', 1
And then executed RECONFIGURE. This presented me with the following error message:
The affinity ...
2
votes
2answers
207 views
Perl vs Ksh for Unix Database Administration
I realize this is a somewhat more subjective question, but I am looking to the community for guidance.
Our company is fairly new to having DBAs. We used to use DB2 for i on the IBM's iSeries servers, ...
2
votes
2answers
115 views
Database structure for a system with multisite
The system I'm working is structured as below. Given that I'm planning to use Joomla as the base.
a(www.a.com),b(www.b.com),c(www.c.com) are search portals which allows user to to search for ...
2
votes
1answer
78 views
How to get dependencies order
I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem ...
