Questions tagged [database]
System that handles large amounts of data in an organised fashion
1,686
questions
225
votes
13
answers
445k
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
- 8,521
79
votes
8
answers
227k
views
AWS RDS connection limits
RDS server come up with 40 connection max, as in the following documentation
I am using Magento 1.9, and at some points, i reach the max number then website is out of service.
Do you have any ...
- 903
66
votes
7
answers
18k
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 ...
- 713
65
votes
6
answers
173k
views
How to rename a MySQL database?
How to rename a MySQL database?
The MySQL online manual has said about the RENAME DATABASE command (this documentation page has been removed by Oracle some time ago):
This statement was added in ...
- 1,097
53
votes
4
answers
138k
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 '...
- 6,976
49
votes
7
answers
55k
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 ...
- 3,110
46
votes
7
answers
43k
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?
43
votes
3
answers
68k
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 ...
- 7,478
40
votes
2
answers
69k
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.6k
40
votes
6
answers
20k
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 ...
- 403
37
votes
1
answer
98k
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?
- 431
36
votes
2
answers
15k
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 ...
- 973
36
votes
2
answers
11k
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 sites?
I'm looking to ...
- 12k
33
votes
7
answers
4k
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 ...
- 1,350
33
votes
1
answer
47k
views
How can I verify that a SQLite db3 file is valid/consistent
I have some sqlite version3 db3 files I copied off a live running production system (I know bad sysadmin bad sysadmin) for various reasons. Is there some sqlite command I can run that will verify that ...
- 2,182
29
votes
3
answers
30k
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 ...
- 293
28
votes
2
answers
138k
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 ...
- 1,127
27
votes
3
answers
32k
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?
notnot
26
votes
6
answers
62k
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 ...
- 1,634
26
votes
3
answers
76k
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 ...
- 973
26
votes
7
answers
66k
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. ...
- 361
25
votes
6
answers
4k
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 ...
- 391
22
votes
2
answers
68k
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 ...
- 323
22
votes
4
answers
143k
views
How to duplicate MSSQL database on the same or another server?
I am trying to accomplish the following:
Take a snapshot of the original database, create and exact a duplicate on another server?
Take a snapshot of the original database, create a duplicate on the ...
- 782
22
votes
4
answers
5k
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 ...
- 323
22
votes
1
answer
360
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 ...
- 1,451
20
votes
5
answers
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 ...
- 993
20
votes
7
answers
22k
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 ...
- 537
19
votes
5
answers
32k
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... ...
- 355
18
votes
3
answers
31k
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 ...
- 10.6k
17
votes
8
answers
16k
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 ...
- 365
17
votes
3
answers
8k
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 ...
- 475
16
votes
2
answers
40k
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?
Komputer
16
votes
1
answer
15k
views
mariadb.service start stuck at activating
I just installed the mariadb in my ubuntu 19.10 by sudo apt install mariadb-server mariadb-client after when I am trying to start the server by sudo systemctl start mariadb.service shell get freeze I ...
- 263
16
votes
4
answers
8k
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, ...
- 547
16
votes
5
answers
38k
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 ...
- 497
16
votes
4
answers
113k
views
Oracle difference between SID, DB Name, DB Domain, Global Database Name, Service Name, Service Alias and Instance Name
What's the difference of SID, DB Name, DB Domain, Global Database Name, Service Name, Service Alias and Instance Name in Oracle ?
- 2,099
16
votes
2
answers
30k
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 ...
- 422
15
votes
4
answers
52k
views
How to know storage engine used of a database?
Previously, on every database created, I use:
mysql -u root -p
CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL ON dbname.* TO 'dbuser'@'localhost';
and then use the database ...
- 715
15
votes
1
answer
28k
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 ...
- 70.7k
15
votes
2
answers
8k
views
docker swarm database connection reset by peer
I am running a spring boot application with docker swarm and I use postgres for database. When I run both of them as docker service, database connection fails consistently and randomly (as you can see ...
- 183
14
votes
3
answers
9k
views
How can I check if my DB needs more RAM?
How would you check if your postgresql DB instance needs more RAM memory to handle their current working data?
- 653
14
votes
4
answers
39k
views
What do I do when pg_cancel_backend doesn't work?
If I have a long-running Postgres query, and regular "kill [pid]" doesn't work, and pg_cancel_backend doesn't work, what should I do?
mike
14
votes
6
answers
25k
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 (http://...
- 1,343
14
votes
7
answers
8k
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 ...
- 1,512
14
votes
5
answers
18k
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 ...
- 431
14
votes
1
answer
16k
views
Is it possible to recover MongoDB databases from .ns and .0, .1, . ... files?
I have a MongoDB 2.0.4 installation on Ubuntu 12.10. Recently I had some problems connecting to the database from the outside, and figured out there was something which prevented MongoDB from starting ...
- 427
14
votes
1
answer
18k
views
How to disable keys in MySQL InnoDB tables?
Is it possible to disable keys in InnoDB tables?
If yes, how?
If not, why?
- 1,115
13
votes
11
answers
14k
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 ...
- 436
13
votes
2
answers
8k
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 ...
- 547