System that handles large amounts of data in an organised fashion

learn more… | top users | synonyms (2)

59
votes
7answers
8k views

What are the drawbacks of running a database inside a virtual machine? How do I overcome them? [closed]

Running anything inside a virtual machine will have some level of performance hit, but how much does it really impact the performance of a database system? I found this academic reference paper with ...
53
votes
4answers
60k views

How do I load a sql.gz file to my database? (importing)

I'm trying to import a gzipped SQL file into mysql directly. Is this the right way? mysql -uroot -ppassword mydb > myfile.sql.gz
43
votes
7answers
37k views

What is the best Linux filesystem for MySQL (InnoDB)?

I tried to look for benchmark on the performances of various filesystems with MySQL InnoDB but couldn't find any. My database workload is the typical web-based OLTP, about 90% read, 10% write. Random ...
39
votes
7answers
31k views

Is it safe to have SQL Server auto-shrink turned on?

There are many SQL Server options that can be enabled for databases, and one of the most misunderstood ones is auto-shrink. Is it safe? If not, why not?
35
votes
7answers
11k views

Mysql: Working With 192 Trillion Records… (Yes, 192 Trillion)

Here's the question... Considering 192 trillion records, what should my considerations be? My main concern is speed. Here's the table... CREATE TABLE `ref` ( `id` INTEGER(13) AUTO_INCREMENT ...
34
votes
7answers
2k views

Database server: Small quick RAM or large slow RAM?

We are currently designing our new database servers, and have come up with a trade off I'm not entirely sure of how to answer. These are our options: 48GB 1333MHz, or 96GB 1066MHz. My thinking is ...
27
votes
1answer
5k views

How do you do load testing and capacity planning for databases?

This is a canonical question about capacity planning for databases. Related: Can you help me with my capacity planning? How do you do Load Testing and Capacity Planning for Web ...
24
votes
6answers
3k views

Scaling databases with cheap SSD hard drives

I hope that many of you are working with high traffic database-driven websites, and chances are that your main scalability issues are in the database. I noticed a couple of things lately: Most large ...
21
votes
3answers
30k views

Export and import a PostgreSQL database with a different name?

Is there a way to export a PostgreSQL database and later import it with another name? I'm using PostgreSQL with Rails and I often export the data from production, where the database is called ...
21
votes
4answers
2k views

Can MySQL effectively take advantage of 64 GB RAM?

We have been running into a problem where querying a table that has approximately 50 million rows, and has an index size of 4 GB (table size of about 6 GB) results in the database server swapping ...
21
votes
1answer
277 views

Is it possible that just one bit switches so my file shows me a letter “Q” instead of a “S”

In our application we use Hibernate and PostgreSQL to store data. In one of our database tables we have a discriminator column which says for example "TIPPSPIEL". It is a fixed string and can not be ...
20
votes
5answers
1k views

Is MySQL supposed to be installed alone

I often hear people making statements such as "our MySQL server machine failed", which gives me the impression that they dedicate a single machine as their MySQL server (I guess they just install the ...
18
votes
8answers
15k views

What is the best filesystem for insert performance on PostgreSQL?

I'm curious if anyone out there has done any experimentation or comparisons between file systems and database performance. On Linux, I'm wondering what is the optimal file system for a postgres ...
18
votes
2answers
20k views

How can I do a dump of only the table structure in PostgreSQL?

In a similar vein to this question, how would I do a schema-only dump in PostgreSQL?
18
votes
1answer
30k views

What can user do with VIEW SERVER STATE permissions?

In SQL Server 2008 there is a permissions VIEW SERVER STATE. What rights this permission give to user? What SQL Server mean by SERVER STATE?
17
votes
3answers
8k views

Is there a difference between RAID 10 (1+0) and RAID 01 (0+1)? [duplicate]

I've seen both of these listed, both being striped and mirrored across multiple drives, but is the a difference between them that I'm not picking up on?
17
votes
2answers
22k views

MySQL: creating a user that can connect from multiple hosts

I'm using MySQL and I need to create an account that can connect from either the localhost or from another server, i.e. 10.1.1.1. So I am doing: CREATE USER 'bob'@'localhost' IDENTIFIED BY ...
16
votes
6answers
22k views

From a shell script, how can I check whether a MySQL database exists?

mysqladmin -uroot create foo returns an exit status of 1 if foo exists, and 0 otherwise, but of course it will also create the database if it doesn't already exist. Is there some easy way to simply ...
16
votes
3answers
25k views

Third-party SSD solutions in ProLiant Gen8 servers

I was wondering if anyone had any specific experience using Intel DC3700 SSDs (or similar) in the HP (DL380p) Gen8 servers? I'm upgrading a set of database servers that use direct-attached storage. ...
14
votes
2answers
29k views

Best Mysql cache settings for 8gb RAM dedicated mysql server using only Innodb (5gb database)

I'm a pretty big noob when it comes to setting up mysql for performance. And honestly I'm not worried about the fine tuning to squeeze every last bit of performance out of mysql, but I do know that ...
14
votes
8answers
9k views

How do I backup a mysql database, but at low priority?

I want to backup a database, but during the day when there is load on the server. It's vital that the backup doesn't impact apache and other databases running on the same server. It should be ...
13
votes
5answers
19k views

Should I install 32-bit database or 64-bit database?

(I've read a lot about 64-bit versus 32-bit OS/Apps, but this question is specifically in regards to databases.) I'm trying to understand the pros and cons of 32-bit versus 64-bit databases, and ...
13
votes
7answers
6k views

SQL Server - Force DB in memory?

We have a beefy Windows 2008 x64 server (4 x 4 core CPU, 32GB RAM) running SQL Server 2005 64-bit. We have a small (6GB) but very important database that is somewhat slow to access until the pages are ...
13
votes
6answers
22k views

How can I diff two Oracle 10g Schemas?

I have the feeling that there are some difference between two large complicated Oracle schemas which should be identical, so decided to dump and diff them to investigate. I found an article ...
13
votes
2answers
4k views

Postgres: Non zero exit code when executing a sql file?

I am writing a shell script which makes calls to psql using 2 forms... one is by command (-c), the other is by file (-f). e.g. psql -c "create table foo (bar integer)" psql -f foobar.sql One ...
13
votes
3answers
24k views

Postgres: How can I see all SQL statements being executed by the database server?

I'm in the process of reviewing every SQL statement that an application makes against the database, for performance reasons. Is there an easy way to log all statements that are executed by the ...
12
votes
11answers
10k views

Should I run my database off of a RAID 5 configuration?

I've heard that the write performance of RAID 5 can be appalling at times. While I want the redundancy that it provides I don't want to sacrifice my database insert/update times. Is this something I ...
12
votes
8answers
9k views

What hardware makes a good MongoDB Server ? Where to get it?

Suppose you're on dell.com right now and you're buying a server to run your MongoDB database for your small startup. You will have to handle literally tens of thousands of writes and reads per minute ...
12
votes
5answers
14k views

How to drop all tables in a MySQL database without dropping the database?

I need to drop all the tables in a database without knowing their names beforehand. The typical procedure is to drop and then recreate the database but this is not an option. What is the best way to ...
12
votes
1answer
15k views

Where does Active Directory-integrated DNS store its data?

This has been bugging me for a while. We all know Active Directory is a LDAP database. We also know that the Windows DNS service, when running on a domain controller, can store its data in AD ...
11
votes
4answers
5k views

Should I backup and restore the `mysql` database?

In the process of creating an automated solution for backing up and restoring an entire MySQL server, I've come across the mysql database which seems to contain user accounts, permissions, metadata, ...
11
votes
3answers
610 views

What types of systems have to “scale up” rather than “scale out”?

I have been wondering for a long time if there are systems that have to "scale up" (onto a more powerful, more expensive server) rather than "scale out" by being split across many smaller servers. ...
11
votes
3answers
14k views

How does one list warnings from the 'mysqlimport' utility?

To start off, this is not about loading data from within mysql itself, but using the command-line tool "mysqlimport". I am using it to load a CSV directly into a table and need to see the warnings ...
10
votes
9answers
6k views

How to store 3 million records in key value format?

We have to store basic information about 3 million products. Currently the info is one 180 mb CSV which gets updated quarterly. There will be about 30,000 queries per day, but the queries are just a ...
10
votes
2answers
12k views

Command to create MySQL database with Character set UTF-8

I use create database dbname; to create database. but I want it to created with Character set UTF-8 Anyone know what is the command to use?
10
votes
4answers
675 views

How do I most efficiently store and serve 1,000,000+ small gziped files on a Linux web server?

I have large static content that I have to deliver via a Linux-based webserver. It is a set of over one million small, gzip files. 90% of the files are less than 1K and the remaining files are at most ...
9
votes
8answers
944 views

What is a relational database? [closed]

I'm mostly a system administrator and I don't directly work with databases other than installing them, setting up accounts, granting privileges, and so on. I realized that if The Boss walked up to me ...
9
votes
3answers
3k views

OpenSSH with public keys from database

Is it possible to fetch the public keys from a database instead of the authorized_keys file? I would like to use such a setup to manage ssh access to things like git repositories for multiple users ...
9
votes
5answers
8k views

Modifying columns of very large mysql tables with little or no downtime

I periodically need to make changes to tables in mysql 5.1, mostly adding columns. Very simple with the alter table command. But my tables have up to 40 million rows now and they are growing fast... ...
9
votes
1answer
17k views

how to connect to mongodb server via ssh tunnel

It was easy for me to connect to my remote mysql server on AWS using a sequelpro, however I'm struggling with doing the same thing with mongodb. I tried setting up an ssh tunnel via command line ...
9
votes
3answers
2k views

Best practice for 24 Disk Array

we have just migrated for our old fibre SAN storage to an IBM v3700 storwize with 24 SAS 600GB disks. This storage is connected directly to two IBM ESXi 5.5 servers each with two 6Gbps multipath SAS ...
8
votes
7answers
705 views

How to avoid being scraped?

We have a searchable DB, and we limit the results to 15 per page. And only 100 results and yet still get people trying to scrape the site. We ban sites that hit it fast enough. I was wondering if ...
8
votes
9answers
3k views

How do you usually back up your database?

I am using mysql How often do you back up your database? How do you normally backup your database? Export all data into sql or cvs format and keep it in a folder??
8
votes
3answers
10k views

What's difference between “varchar” and “text” type in PostgreSQL?

All I know about differences of them is varchar has limit, and text is not. The documentation does not mention about this. Is that really the only difference? No consideration about performance or ...
8
votes
2answers
2k views

Open source DNS server with database backend

I'm looking for free opensource DNS server that can keep all the data in DB backend. Currently I found next solutions: MyDNS-ng (looks OK - fork from MyDNS) PowerDNS (looks like OK) Bind9 with ...
8
votes
5answers
2k views

Is it risky to have a database server and a web server on the same machine?

It seems like life would be simple to run the database server on the same machine as the web server, but are we taking a big secuirty risk by doing this? The environment will be Windows 2008 server, ...
8
votes
2answers
3k views

With MySQL, how long does an “ALTER TABLE … DISABLE KEYS;” statement last?

If you disable the keys (suspending indexing) on a mysql INNODB table, how long does that setting last? For a query like: ALTER TABLE users DISABLE KEYS; Do the keys get re-enabled at the end of ...
8
votes
1answer
166 views

Can I specify where my database files are stored *per database* in MySQL?

Is it possible to have two databases hosted on a single MySQL instance, but have the files stored in two different physical locations? For example: db1 stored in: C:/ProgramData/MySQL/MySQL Server ...
8
votes
2answers
9k views

How to calculate max_connections for PostgreSQL and default_pool_size for pgbouncer?

Is there a rule or something I can use to calculate a good number for max_connections, default_pool_size and max_client_conn? The defaults are odd. PostgreSQL defaults to max_connections=100 while ...
8
votes
2answers
4k views

Is there a secure way to allow IIS 7 in a DMZ to access a DB server behind the firewall?

Our network admins are adamant that it is insecure for our web servers, which are hosted in the DMZ, to access the DB server behind our firewall. To get round the problem, we access the data via web ...