MySQL : Open-Source, Relational Database Management System
3
votes
1answer
2k views
Mysql fulltext search my.cnf optimization
I have open a question on http://serverfault.com/questions/353888/mysql-full-text-search-cause-high-usage-cpu Some user recommended asking here.
We built a news site. Every day we will input tens of ...
5
votes
2answers
1k views
Insert-heavy InnoDB table won't use all my CPU
I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could ...
1
vote
2answers
2k views
Scheduled optimization of tables in MySQL InnoDB
What is the best way to schedule automatic optimization of tables in a MySQL InnoDB database? Can I use events for example? I have recently had a big performance issue (when querying) with one of the ...
1
vote
1answer
1k views
ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table ...
48
votes
5answers
2k views
How can a group track database schema changes?
What version control methodologies help teams of people track database schema changes?
20
votes
8answers
3k views
Tool to generate large datasets of test data
Many times when trying to come up with an efficient database design the best course of action is to build two sample databases, fill them with data, and run some queries against them to see which one ...
2
votes
2answers
3k views
Local database vs Amazon RDS
I am doing large INSERT into a local MySQL database
I am at 2,000,000 rows and am really starting to notice the slowdown now. I always heard that MySQL wasn't very scalable
I expect this database to ...
4
votes
2answers
1k views
Scaling Percona datacenters: setup and replication
Since our startup has taken off nicely we are now meeting with some of those issues that you always assumed would NEVER effect you.
We have already scaled alot of our application stack: we offloaded ...
4
votes
2answers
1k views
What to set innodb_buffer_pool and why..?
I have 170GB of InnoDB index and Data.
I have to readjust the innodb_buffer_pool size for better performance.The Max table size of a InnoDB table(Index+data) is 28GB.
So what should be the optimal ...
7
votes
3answers
5k views
Why is 'LOAD DATA INFILE' faster than normal INSERT statements?
I've read an article that mentioned we can achieve 60,000 inserts per second by LOAD DATA IN FILE statement that read from csv files and insert it into database.
Why should it differ from normal ...
4
votes
3answers
609 views
Log MySQL DB changing queries and users
For security reasons I need a server-side (running Debian 6.0 Squeeze) logging of all queries that may have changed the content of a MySQL DB (v. 5.1) and the user who issued it. I had to rule out
...
1
vote
2answers
2k views
MySql - Clean ibdata1
I have a database that its entire size is ~44GB, out of it ibdata1 is ~35GB that doesnt make sense, the size of the data shouldnt be more then 10GB.
i use the following query to get an esstimation of ...
8
votes
3answers
3k views
Audit logins on MySQL database
Is there a way to audit logins to MySQL? I'd like to be able to create a username for each employee and thereby create an audit trail of logins. However, googling has turned up no good results.
The ...
7
votes
3answers
727 views
I am using the MEMORY storage engine but MySQL still writes to my disk…Why?
I am using the MEMORY Engine for all tables associated with a particular MYSQL query because speed of access is paramount to my project.
For some reason, I have noticed that a large amount of disk ...
6
votes
3answers
8k views
How do you re-sync the Master MySQL DB with Slave DB changes if the Master goes offline?
MySQL Server 1 is running as Master.
MySQL Server 2 is running as Slave.
With both DBs online, they are in "perfect sync". If Slave goes offline, there's no problem if Master still online; they will ...
5
votes
4answers
2k 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
3k 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
1answer
3k 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 ...
2
votes
3answers
2k 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
4answers
6k views
Optimized my.cnf for high-end and busy server
Curretly my main culprit of poor performance is MySQL and I'm not a DB savvy. So far, I've had little luck in optimizing mysql configs on my server, which has a Sandy Bridge processor with 16GB ram ...
4
votes
1answer
232 views
MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
I have a basic table :
create table fullTextTest
(
id INT(11) NOT NULL,
superText CHAR(255) NOT NULL,
superLongText TEXT NOT NULL,
primary key (`id`),
FULLTEXT KEY `superText` ...
2
votes
2answers
268 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?
2
votes
1answer
2k views
Mysql Master-Master Replication Topologies on >2 machines
I have 3 Mysql servers that I would like to replicate: two (including the main server) are local and one is remote. Let's call my main server A, the secondary server B, and a remote, development ...
2
votes
1answer
320 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
2answers
677 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
268 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
2answers
415 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 ...
4
votes
2answers
4k views
How large should be mysql innodb_buffer_pool_size?
I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and grind ...
13
votes
6answers
23k views
Need to migrate SQL Server to MySQL
I have a database on SQL Server 2008 on a Windows server and I want to move all of the data to a MySQL database on a Ubuntu server. I have tried using the SQL Server Import and Export Wizard with the ...
3
votes
1answer
336 views
Proper tuning for 30GB InnoDB table on server with 48GB RAM
I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
6
votes
1answer
1k views
When to switch from MyISAM to InnoDB?
We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM.
My original plan was to upgrade ...
6
votes
4answers
3k 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
1k 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 ...
4
votes
1answer
309 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, ...
4
votes
2answers
2k 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
3answers
466 views
MySql shell command not replicated to slave
I have a shell script that looks something like that:
#!/bin/bash
mysql -udb_user -pdb_password -e "LOAD DATA LOCAL INFILE '/srv/some_file.csv'
INTO TABLE db_name.db_table FIELDS TERMINATED BY ...
2
votes
1answer
175 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
1k 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 ...
9
votes
2answers
2k 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 ...
7
votes
3answers
2k views
check constraint does not work?
I have the following table.
create table test (
id smallint unsigned AUTO_INCREMENT,
age tinyint not null,
primary key(id),
check (age<20)
);
The problem is that the CHECK ...
5
votes
1answer
608 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 ...
3
votes
1answer
170 views
Limiting database security
A number of texts signify that the most important aspects offered by a DBMS are availability, integrity and secrecy. As part of a homework assignment I have been tasked with mentioning attacks which ...
2
votes
1answer
169 views
optimal table design mysql with primay key and varchar value
I have a table:
id (PRIMARY KEY AUTO_INCREMENT)
value (UNIQUE KEY)
which currently holds over 50 millions rows (and growing fast) and is hit hard:
process 1 queries this table (>200/sec) with a ...
2
votes
1answer
1k views
Can I rename the values in a MySQL ENUM column in one query?
Suppose I have a Database table with an ENUM('value_one','value_two').
I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows:
ALTER TABLE `table` MODIFY ...
2
votes
3answers
349 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 ...
5
votes
3answers
1k views
Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB
I need to design database to store information contained in millions of log files generated by devices on trial on network.
I have designed 5 different tables to store log file information. But, as ...
4
votes
1answer
473 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
286 views
Which mysql storage engine to choose?
I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB).
The theories say:
Both work with BTree indexes, but MyISAM can work with FULLTEXT index ...
3
votes
2answers
525 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
331 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 ...
