MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

19
votes
1answer
19k views

intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”

Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 ...
14
votes
3answers
16k views

Is it safe to use innodb_flush_log_at_trx_commit = 2

I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
12
votes
7answers
15k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
5
votes
1answer
3k views

Best solution for cross-datacenter MySQL master-slave replication

We are working on a new system architecture for our company. We have a HPC, which runs in our own data-center and we are planning our front-end and a fallback system on Amazon Web Service. System ...
5
votes
1answer
572 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, ...
6
votes
1answer
2k views

How large will a MySQL database be relative to the dump file?

I'm restoring a ~52GB dump to a MySQL database. The ibdata1 file has already exceeded the size of the dump file and the restore is still incomplete. Is there any way to estimate the final size of the ...
6
votes
1answer
2k views

MySQL Workbench Database Sizes

I'm trying to find the total size on the hard disk that all of my MySQL Workbench databases are using. Does anyone know of an easy way to figure this out? If nothing else, the default location ...
6
votes
4answers
3k views

MySQL Replication - Introduce new Slave to replication

I've recently taken on a sys admin position managing a group of 20 (or so) servers. One thing I haven't dealt with before (apart from in a test situation), is introducing a new slave to the MySQL ...
3
votes
1answer
4k views

Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working

I have several tables being reported by pt-table-checksum with CRC_DIFF between my master and slave servers using the following commands: $ pt-table-checksum h=master,u=user,p=password ...
3
votes
3answers
685 views

Restoring an old backup to latest MySQL release

Our old database is MySQL 5.1 server and is four years old. If I uninstall MySQL 5.1 Server and install the latest release and restore the backup, will I gain performance benefits? I guess the new ...
7
votes
4answers
3k views

One slave, multiple masters MySql

Is it possible to set-up MySql Replication to have one slave listening to two different masters?
4
votes
4answers
2k views

Is raid 5 suitable for a mysql installation?

Is raid 5 suitable for a mysql installation? Let me explain my application further. My application is a socket programming which will connect with gps device to receive gps string and there after do ...
4
votes
1answer
5k views

How to setup replication(Master/slave) in MySQL 5.5.20?

I have two servers both running on MySQL 5.5.20 and I want to setup replication, so every update/insert on Master would reflect immediately on Slave machine. I tried following this tutorial (I used ...
3
votes
3answers
4k views

Can I specify a different disk for a specific MySQL table?

I use my home machine as my personal database for my hobby analytics project. This machine has an SSD for the main drive where MySQL stores its data. It also has a secondary hard drive that is ...
2
votes
3answers
552 views

Why query_cache_type is disabled by default start from MySQL 5.6?

We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the query_cache_type is defaulted to off start from 5.6. We enabled it again and ...
2
votes
2answers
614 views

Mysql database replication on different vlan/subnet/another site

Saw the posts for "MySQL high availability, failover and replication with Latency" Is it possible to work it in a DR site when its not in the same vlan?
5
votes
1answer
1k views

MySQL Replication : 1 Slave / Multiple Masters

I have a web app with 2 web servers and 2 database servers. The DBs are setup for multimaster replication. (this is the primary environment) I also have the exact same setup on a different location ...
4
votes
1answer
929 views

How does max_allowed_packet affect the backup and restore of a database?

I have a database based on mysql 5.0.75-0Ubuntu10.2, running under Ubuntu 9.04. I have a table in that database with a medium blob column. I have been using automysqlbackup to do nightly backups/dumps ...
3
votes
2answers
1k views

Quick MySQL Backup

I have a database which is grown to 3GB and it has 3 MyISAM tables. And I have 4GB RAM. What will be the best (quick) way to backup this database without any performance lack. Or mysqldump will work ...
3
votes
3answers
494 views

Dynamically changeable database

We have a database and some data is stored in a Trucrypt folder, my question is how can I include this trucrypt folder. The data type is MyISAM. I saw there is a symbolic link, but not really clear ...
2
votes
1answer
132 views

Combining columns in index

With reference to RolandoMySQLDBA's answer on MySQL: Index when joining to tables not being used (Performance optimizing question) He mentioned Make sure you build an index that involves the ...
2
votes
2answers
725 views

Which first: upgrade mysql version or convert storage engine?

I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets. I am planning on: upgrading to 5.5 converting ...
2
votes
1answer
629 views

Create a custom log table in MySQL

Is it possible to create a custom log table. For example I have 10 schema and I would like to log if somebody is using the "areva" schema. I want to know the last executed command time and the user. ...
2
votes
1answer
672 views

MySQL Cluster — Storing table data on data nodes

I have setup MySQL Cluster with the MySQL server node and the Management node on a single server, and 2 data nodes (each on a different server for 3 servers in all). After installing, I run ndb_mgm ...
2
votes
2answers
1k views

Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE

If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction. This is done in order to "lock ...
1
vote
1answer
537 views

Removing/overwriting sensitive data located in already deleted records in ibdata files

As far as I know, using InnoDB tables with innodb_file_per_table = off does not prevent deleted data from being visible in the ibdata1 file. I know this is by-design, and that I could switch the ...
1
vote
1answer
704 views

decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

i recently testing the move of our db from one server to another. as part of this process i copied all the data to the new db and started testing it. as a comparison i run a store procedure whose ...
1
vote
1answer
713 views

Secure method beside phpMyAdmin for remote mysql

Recently, one of my centos linux servers was hacked and the cause of due to phpMyAdmin. It was said due to this problem PhpMyAdmin vulnerability CVE-2011-2505 ...
0
votes
1answer
629 views

Inserting query result to another table hangs on “Copying to temp table on disk” on MySQL

I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs. This causes 149,000,000 rows to be decreased to 460,000 rows. The query ...
175
votes
15answers
60k views

Can MySQL reasonably perform queries on billions of rows?

I am planning on storing scans from a mass spectrometer in a MySQL database and would like to know whether storing and analyzing this amount of data is remotely feasible. I know performance varies ...
15
votes
3answers
4k views

How Does Table Partitioning Help?

I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
7
votes
2answers
3k views

Performance of a Trigger vs Stored Procedure in MySQL

A post here on DBA.StackExchange (What are the best practices for triggers to maintain a revision number on records?) has spawned an interesting question (at least, interesting to me) regarding ...
3
votes
1answer
6k views

InnoDB - High disk write I/O on ibdata1 file and ib_logfile0

Server Specification: VPS with following info model name : Intel(R) Xeon(R) CPU E5649 @ 2.53GHz MemTotal: 2058776 kB MemFree: 244436 kB We are running IP.Board from Invision ...
9
votes
2answers
5k views

innodb_file_format Barracuda

I have a couple questions for those more familiar. Most of my instances have been running Antelope despite having support for Barracuda. I was looking to play around with some compresses innodb ...
9
votes
3answers
2k views

How is LIKE implemented?

Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it? The naive approach would be to inspect ...
8
votes
3answers
5k views

What is the default order of records for a SELECT statement in MySQL?

Suppose you have the following table and data: create table t(k int, v int,index k(k)) engine=memory; insert into t values(10, 1), (10, 2), (10, 3); When issuing select * from t where k = 10 with ...
7
votes
3answers
6k views

InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)

This server is a virtual machine running CentOS 6.0, MySQL 5.5.21 There is a database named devSystem. There are InnoDB tables within. Running the following commands causes error ERROR 2006 ...
6
votes
4answers
7k views

How to improve InnoDB DELETE performance?

So I have this audit table (tracks actions on any table in my database): CREATE TABLE `track_table` ( `id` int(16) unsigned NOT NULL, `userID` smallint(16) unsigned NOT NULL, `tableName` ...
5
votes
3answers
11k views

How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?

I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
2
votes
2answers
6k views

MySQL Error Reading Communication Packets

In MySQL error logs, I see these quite few a warnings like these: 120611 16:12:30 [Warning] Aborted connection 2619503 to db: 'db_name' user: 'user_name' host: 'webapp_hostname' (Got an error reading ...
13
votes
3answers
4k views

Is it common practice to mix InnoDB and MyISAM tables on same server?

I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
4
votes
2answers
5k views

MySQL Event does not run

i am running mysql 5.1.41 that came bundled with xampp on windows. the problem is that the event doesnt run automatically even when the event scheduler is ON. i have a table named ta_table using ...
2
votes
1answer
2k views

Is there a progress indicator for OPTIMIZE TABLE progress?

MySQL 5.1.4x (Windows) | Innodb I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3; to reduce the ...
2
votes
1answer
255 views

mysql emits BINLOG rows even though binlog_­format=­STATEMENT

We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default) According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in ...
1
vote
1answer
3k views

16 Cores 12 GB RAM server MySql Configuration - my.cnf

The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots ...
6
votes
1answer
5k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found after upgrade to mysql 5.6

I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup I found a possible solution here, but it does not seem official. ...
5
votes
2answers
840 views

Choosing MyISAM over InnoDB for these project requirements; and long term options

Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific. My project's aim is to let users search a (huge) database of variety of products. ...
5
votes
1answer
2k 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 ...
5
votes
1answer
724 views

Does MySQL still handle indexes in this way?

Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index. If a ...
4
votes
3answers
2k views

InnoDB vs MyISAM with many indexes

I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway: I have a ...