3
votes
1answer
746 views

Pgpool-II: sequences advanced by 1 after PITR recovery

I have tested pgpool-II 3.0.1 and pgpool 3.0.4, PostgreSQL 8.4 In both cases I'm getting the same result: after online recovery on primary and target nodes all 'serial' values got "+1". Other nodes ...
22
votes
2answers
7k 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 ...
5
votes
2answers
3k views

“phpMyAdmin” for Cassandra

Is there a tool like phpMyAdmin for RDBMS-MySQL for a Cassandra DB? I know that there is less "runtime" config possible (column families can not be edited at runtime etc.). Nevertheless, it would be ...
5
votes
1answer
909 views

What are your use-cases for HandlerSocket?

There's a bit of hype around: HandlerSocket It seems like faster, nosql access to MySQL data is becoming more desirable. Percona Server is now shipping with HandlerSocket built in. My question is, ...
3
votes
1answer
907 views

Groups, Users, and Inbox

We're working on a PM system where a user can send an email to other users or groups. We currently have the following tables: Users Groups Users can be part of many groups. A user doesn't have to ...
5
votes
2answers
6k views

How to list all postgres tables in one particular schema

Using the psql command line tool, how do I list all postgres tables in one particular schema
3
votes
2answers
585 views

Degrading PostgreSQL 8.4 write performance

Load testing of postgresql 8.4 for OLTP application suitability resulted in unpleasant situation when throughput of the database significantly degraded over time from thousands of write transactions ...
5
votes
1answer
2k views

PostgreSQL on Ubuntu - Using conf files for recovery

I am using PostgreSQL 8.4 on Ubuntu LTS 10.04.02 server version. I use pg_standby to restore the data and for database recovery created a recovery.conf and placed it on /etc/postgresql/8.4/main where ...
3
votes
2answers
6k views

Postgresql - Recovery using recovery.conf

I am trying to recover a database using recovery.conf file for making point-in-time copy. On recovery.conf file, I added a recovery target time as below. recovery_target_time = '2011-06-16 04:10:00 ...
6
votes
1answer
3k views

What is the difference between XtraBackup and rsync?

Yes, I'm going to run strace on them both... However, I would like to know ... What is XtraBackup is doing over and above rsync -ac src dest ? What's so special about XtraBbackup ? How is XtraBackup ...
3
votes
1answer
610 views

Changing SQL Server 2000 usernames

We're running a SQL Server 2000 instance with users tied to Windows accounts. Many of the usernames have spaces in them and that is causing us some problems now. We would like to be able to rename ...
5
votes
2answers
2k views

Removing Archive Logs after PostgreSQL PITR online-recovery

I'm using pgpool-II + PostgreSQL 8.4 on three servers: primary + standby1 + standby2. Replication mode is "on" Load balance mode is "on" (between primary and standby1) I followed the official ...
5
votes
1answer
61 views

How to link Similar-but-distinct Models of Medical Imaging Data?

I'm tracking a set of medical images in a logbook-type application, and am having a problem with how to model my records. The problem is that for every group of images collected, there are two ...
9
votes
1answer
9k views

Auto Update Statistics in SQL Server 2008R2: Why do some statistics remain stale despite a large number of row inserts?

During investigation of a slow query it appeared that the execution plan was exceptionally suboptimal (A nested loop performing 9 million executions of a seek where the estimated number of executions ...
6
votes
3answers
936 views

Would it be sensible to create a MySQL user for each user account of a web application?

The thought occurred to me recently, but I have a suspicion the idea might be insane. A couple benefits I'm perceiving are: facilitate automated logging via triggers control mysql connections on a ...
10
votes
2answers
5k views

Trimming down a big SQL Server database

I have a large SQL Server 2008 test database (1.9TB) and I want to reduced its size on the disk. I have been deleting unused tables, and purging significant amounts of data, but its size on disk it no ...
5
votes
1answer
704 views

Speeding SQL queries for a large(?) WordPress database?

I am using a WordPress plugin called feedwordpress in order to run a planet like website (it takes content from other websites, with their permission) on WordPress (See it here). The plugin is great ...
11
votes
1answer
13k views

How is INFORMATION_SCHEMA implemented in MySQL?

The INFORMATION_SCHEMA is, in theory, a set of views specified in the SQL standard that allow the user to inspect the system's metadata. How is it this implemented in MySQL? When I connect to a fresh ...
11
votes
1answer
3k views

Source several files at once with psql

I want to execute several sql scripts sequentially with psql as a single transaction to set up my database schema. What is the best way to do this? In the past I know I had a master script that I ran ...
5
votes
1answer
832 views

Need help with ASYNC_NETWORK_IO

We have a SQL Server 2005 server that is serving up 20 websites, one of which is our custom CMS, and a few other micro sites. I am new to this and just started looking at the databases here. The ...
4
votes
1answer
894 views

Help with DB2 Procedure

I have the following query: CREATE PROCEDURE MYPROC( IN @TABLES VARCHAR(500)) DYNAMIC RESULT SETS 1 LANGUAGE SQL SPECIFIC MYPROC NOT DETERMINISTIC MODIFIES SQL DATA CALLED ON NULL INPUT BEGIN ...
20
votes
3answers
46k views

Recommended page file size for SQL 2008R2 on Windows 2008R2

This Microsoft article - How to determine the appropriate page file size for 64-bit versions of Windows Server 2008 and or Windows 2008 R2 provides guidance for calculating the page file size for 64 ...
4
votes
5answers
2k views

Setting up a Redundant Sql Server 2008 Configuration While Retaining High Performance and Minimal Downtime

We have a situation where we are moving to a new hosting facility and are attempting to configure our new database environment. However, none of us are formal DBA's so a lot of what we are doing is ...
3
votes
4answers
3k views

Connect to a remote MySQL Server from a dynamic IP address

My ISP at home uses a dynamic IP address. I'm trying to access a MySQL database at work. How can I connect to it (using MySQL Workbench)? I could grant all privileges to % so that any IP address will ...
7
votes
1answer
1k views

Can someone explain the magic of Opendatasource/Openrowset?

When pulling information from an excel file (or ms-access DB) I commonly use something like this: SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\test.xls;Extended ...
6
votes
1answer
718 views

Indications from obscenely high Operator Costs in Query Plan?

I have a monthly ETL process that's too complicated to explain here, but basically we get a large amount of customer data, bulk load it into various tables, then run a series of build processes. The ...
12
votes
3answers
10k views

Emulate a TSQL sequence via a stored procedure

I have a requirement to create a stored procedure which emulates a TSQL sequence. That is it always gives an increasing distinct integer value on every call. In addition, if an integer is passed in ...
14
votes
2answers
6k 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 ...
5
votes
1answer
735 views

How to perform the dreaded double hop with linked servers?

The situation: I have SERVER1, SERVER2, ACCESSDB on SERVER3. I have successfully created a linked server connection from SERVER1 to SERVER3's ACCESSDB. I want to be able to access the linked server ...
3
votes
3answers
2k views

SQL Server Permission Denied Error When I'm DBOwner?

So I created an account, gave him dbcreator and securityadmin privileges and then through that account tried to run CREATE TABLE on a database that I had created and now owned, but was getting a ...
9
votes
4answers
5k views

Are there tools to perform data comparison between two different schemas?

We are migrating our database to a new schema but want to validate the data has been moved correctly. Traditional data comparison tools can compare two databases for differences if the schemas are ...
5
votes
1answer
2k views

sqlplus Setting the exit code of a script

How can I set the exit code of a script? The following doesn't seem to work WHENEVER SQLERROR EXIT 5 DECLARE retCode number := 0; BEGIN retCode := 30; END; / EXIT :retCode ; Gives the ...
3
votes
1answer
2k views

How do I install pgcrypto on Windows?

I would like to install pgcrypto in Windows. I have a file C:\Program\PostgreSQL\9.0\share\contrib\pgcrypto.sql but when I try to run it in psql I get an error: C:: Permission denied How do I solve ...
6
votes
2answers
659 views

SQL Server configuration / specification recommendations and advice!

I am about to deploy a new server at work as follows: Dell T610 2 x Xeon 2.53 Quad core 24 GB RAM 1 x Perc H700 RAID controller with 1GB Non Volatile memory 8 x 300GB 15k SAS Hard drives I am looking ...
30
votes
5answers
61k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
4
votes
1answer
725 views

Login problem to SQL Server

I attempt to connect to SQL Server 2008,I receive the following error. Login failed for user <>. Reason: Failed to open the explicitly specified database. By the way, I have a SQL Server 2008 ...
15
votes
2answers
16k views

How can I specify the position for a new column in PostgreSQL?

If I have a table with the columns: id | name | created_date and would like to add a column, I use: alter table my_table add column email varchar(255) Then the column is added after the ...
5
votes
1answer
440 views

Temporal database design, with a twist (live vs draft rows)

Not sure if/how I should be cross-posting this... But in case some follow DBA but not SO... Is there a generally accepted means of managing live vs non-live rows in a row-version controlled ...
7
votes
4answers
1k views

How can I tell if my SQL Server DB performance is hardware-limited?

Testing an app currently under single-user load - as the test data has increased to production sizes (400k-2M rows per table), some SELECT sp's are not quite fast enough anymore (with limited test ...
4
votes
2answers
4k views

Creating and editing my.cnf by MySQL Workbench?

I just installed MySQL Server and MySQL Workbench on a clean Snow Leopard MacBook. I hooked up Workbench with the server and was able to start the MySQL Server from within the Workbench. I enter the ...
4
votes
4answers
505 views

How to get a column A which has the smaller column B grouping by column C?

I'd like to get the ID of a dataset for each category (my grouping column) which has the smallest "order" column. Here a set of data to explain my thoughts: CREATE TABLE DATAS ( ID INT(2) , ...
3
votes
1answer
637 views

SQL Return Count of Answers with 0's for Choices from another table

My question went dead after one unhelpful answer on SO here I need this to be optimized because it will be called often. It is going into a stored procedure. I am given @slide (tblILDSlide.id) and ...
6
votes
2answers
596 views

Estimating distribution of row access in mysql

I am trying to get an idea of the size of the "hot data" part of a rather large table, and I was wondering if this could be done directly in mysql. I know that with the percona version of mysql, I can ...
4
votes
1answer
602 views

Sql Server Performance Degradation in moving from 2005 to 2008

I have sql server 2005 setup for my performance bench marking exercise and I have put same database on another server which has sql server 2008 and exact replica in terms of hardware configuration. ...
4
votes
1answer
418 views

Mysql fulltext boolean ignore phrase

I'm trying to come up with a mysql boolean search that matches "monkey" but ignores / doesn't take into account any matches of "monkey business". Something like the following is the general idea but ...
6
votes
1answer
1k views

SQL Server 2008 Containstable generate negative rank with weighted_term

I have a table with full text search enabled on Title column. I try to make a weighted search with a containstable but i get an Arithmetic overflow for the Rank value. The query is as follow SELECT ...
8
votes
2answers
3k views

Limit redo for materialized view complete refresh or manual equivalent

A materialized view(MV) log can be used to allow a MV to do a fast refresh which only modifies the data that has changed. However, various conditions prevent the MV from using the log and therefore ...
5
votes
3answers
6k views

Copy encrypted database views

I need to take a copy of a SQL Server database. The database has approx 40 views that link to other databases, and I need sample data from those views too. Also, the views are encrypted so when I try ...
3
votes
2answers
1k views

SQL 2008, how does it link Full Text Catalog to a filegroup

While creating Full Text Catalog, if "on filegroup" is specified - it creates the catalog on this file, but on MSDN I read "On Filegroup" clause has no affect?
5
votes
1answer
5k views

select permission was denied on the object 'sysjobs'

The select permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'. I have already tried to grant sqlagentoperatorrole and it didn't work. How do I resolve this error?

15 30 50 per page