MySQL : Open-Source, Relational Database Management System
5
votes
2answers
388 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
3answers
6k views
Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql
I have mysql DB on server S1 (mysql version 5.1.41-3ubuntu12.7-log).
I have created master-slave for this DB on server S2 (mysql version 5.1.54-1ubuntu4-log).
The DB on S1 was using one data file ...
4
votes
2answers
376 views
Implementing a Circular Buffer (Sliding window) in MySQL
I intend to store some Java objects in a MySQL database, accompanied by a timestamp. These objects should be kept in a Sliding Window fashion (also known as Circular Buffer), meaning that only the ...
4
votes
3answers
593 views
MySQL tables/databases into different disks
I am aware that there is a possibility to have a single database server with many tables/databases, and you can separate some of those tables/databases into separate disks.
Lets say you have 5 ...
4
votes
2answers
398 views
Is it a good idea to split MySQL DB into two servers
I have a MySQL DB server that accepts a lot of mobile application data for analysis. I would like to know if for optimization reasons the following architecture makes sense and is appropriate:
I ...
3
votes
2answers
387 views
Cannot update certain rows in innodb tables
I found the following on http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html
When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are ...
3
votes
1answer
705 views
MySQL Server Performance
I have a few questions regarding MySQL server running under Windows Server 2008 R2:
How do I check the number of allowed connections?
How do I turn on query logging to see performance of queries ...
3
votes
2answers
739 views
What happens when InnoDB hits its tablespace autoextend max?
The latest version of High Performance MySQL (3rd ed.) recommends that one:
...place an upper limit on the tablespace's size to keep it from growing
very large, because once it grows, it doesn't ...
2
votes
1answer
432 views
When should I think about upgrading our RDS MySQL instance based on memory usage?
It seems like our DB server is doing garbage collection at a increasingly faster rate, which seem normal since it's growing. What's a good rule of thumb of when to switch to a bigger instance, I'm not ...
2
votes
2answers
157 views
MySql - find redundant indexes
is there a tool that spots redundant indexes in MySql?
e.g. if I have the following indexes:
index1(col1)
index2(col1,col2)
then index1 should be flagged as redundant.
any ideas?
2
votes
1answer
2k views
In a MySQL Master/Slave replication, what would happen if I write to the Slave?
Several Questions
Would the Slave entry remain until there is an insert/update/delete to the table on the Master that could affect the Slave?
If not, how do I ensure that the Slave is in-sync with ...
1
vote
1answer
2k 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 ...
1
vote
3answers
3k views
I've got 16GB of ram, how should I configure MySQL Server?
I've got a dedicated production server with multiple cpus and 16GB of ram.
I would like to know how can I configure it better to take advantage of the server resources for better performance because ...
1
vote
3answers
1k views
Monitoring replication on mysql
I have managed to setup master and slave replication.
It is working fine. What are the possibilities that it might go down?
Is there any alerting tool to monitor that?
Another thing: Can I run a ...
1
vote
1answer
724 views
How to make MySQL replication reliable?
Master version: 5.5.13-1
Slave version: 5.5.14-1
Binary log format: MIXED
My Slave database (~ 40GB) has been out of sync from the Master. I cannot find anything interesting in the error log. ...
1
vote
2answers
2k views
Set up a MySQL slave using mysqldump to get the initial data
I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave ...
1
vote
2answers
418 views
How to enable all the possible backup options of MySQL
I normally use MySQL Administrator to backup my database. I want to enable all the backup options for the available backup types in MySQL. I noticed that Binary Logging is not running on my PC. How to ...
0
votes
1answer
125 views
MySQL performance tuning
I would like to optimize my MySQL DB. I noticed some important points as follows:
Enabling Query cache.
Using memory storage. (I know pluses and minuses of this. Being I am having static tables, I ...
0
votes
3answers
156 views
MySQL data view
I have a table that looks like this:
+----------------------------------------+
|Name | kode | jum |
+----------------------------------------+
| aman |kode1 ...
9
votes
1answer
152 views
Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE
I am trying to have a score for entire section of SELECT statements
SELECT *,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword')
UNION
SELECT ...
9
votes
5answers
1k views
Is it unwise to run replication on same physical server?
I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into ...
6
votes
2answers
1k views
MySQL deadlock - cannot restart normally?
MySQL version 5.5.13-1
A snippet from the SHOW ENGINE INNODB STATUS\G:
LATEST DETECTED DEADLOCK
------------------------
111218 10:22:34
*** (1) TRANSACTION:
TRANSACTION 1318D95B, ACTIVE 0 sec ...
5
votes
2answers
492 views
Single Drive vs Multiple Drives
Generally bottleneck of RDBMS (I am a MySQL user) performance is disk access. SSD provides great performance compared with conventional spindle drives.
Question : Is it possible to improve ...
5
votes
1answer
2k views
Mail alert from MySQL when databases are edited
How do I get mail from MySQL when root or someone else edits or updates a particular database? It should trigger sending a mail and log the edits.
I have tried Triggers but its not working.
Server ...
5
votes
3answers
3k views
MySQL gives “Out of Memory” error when inserting a large file. From where is this filesize limitation arising?
I'm using a MySQL database to store files.
The table I'm using is structured as follows:
+--------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default ...
4
votes
1answer
408 views
Replicate MySQL 5.0 Master to 5.5 Slave?
We're planning out a series of server/software upgrades and were curious.
We have a RHEL5 install running MySQL 5.0 that we'd like to replicate to a new (virtualized) server running CentOS 6 and ...
4
votes
1answer
100 views
Installing and enabling both master and slave semisynchronous plugins on same server
I want to run a semisynchronous replication setup, and for simplicity I'd like to not have to INSTALL and UNINSTALL plugins when a slave is promoted to a master. So I'm wondering if I'll experience ...
4
votes
2answers
4k views
Why is MySQL is creating so many temporary tables on disk?
Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows
Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 17158 temp tables, 30% were ...
4
votes
2answers
561 views
Why does MySQL choose this execution plan?
I have two queries,
select some_other_column
from `table`
order by primary_index_column asc
limit 4000000, 10;
and
select some_other_column
from `table`
order by secondary_index_column asc
...
4
votes
1answer
343 views
will replication from 5.5.20 to 5.0.XX server work?
I gave two machines which run on two entire different versions of MySQL server. I want to do the replication from 5.5.20 to 5.0 server. Will that be possible?
Because I remember when I transferred ...
4
votes
2answers
1k views
Which version of MySQL should I use?
For ages I have been using MySQL 5.1.37.
Recently I noticed that this version is no longer supported in repository, so I need to upgrade mysql.
I assume that changing the version should be ...
4
votes
3answers
536 views
Making a column immutable in MySQL
I want to make a column in a relation unmodifyable for consistency reasons.
The backstory is that I have a n:n relationship where the "connecting" relation has additional values. I don't want that ...
3
votes
2answers
479 views
Is it bad to create many mysql temporary tables simultaneously?
I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better ...
3
votes
2answers
208 views
Save history editable data RDBMS
I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
3
votes
1answer
150 views
Resolving MySQL Process ID to page that ran it?
I asked this question on stackoverflow and was directed to ask this question here.
I have a couple of scripts in PHP that run MySQL queries. On occasion they cause the server to seize up or become ...
3
votes
1answer
157 views
Clarification about master slave configuration in mysql
We are planning to migrate an existing MySQL database to a new server using the MySQL replication master slave procedure.
Unfortunately this will take a lot of time so we were thinking of doing a ...
3
votes
2answers
149 views
MySQL InnoDB Index in swap
I can't find any info regarding my question. This is more theoretical question.
For example, i have table keywords.
CREATE TABLE IF NOT EXISTS `keywords` (
`kid` int(11) unsigned NOT NULL ...
3
votes
2answers
870 views
Mysql load from infile stuck waiting on hard drive
I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk ...
3
votes
2answers
929 views
Trying to understand SHOW SLAVE STATUS\G
I have slave setup and looks like it is running fine. Below is a snapshot of it.
show slave STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: ...
3
votes
2answers
637 views
Can I have one table be an “alias”, or “symlink”, to another?
I have two tables with the same structure, A and B. A certain application is written so that it always writes the same data to both tables.
After a discussion with a colleague about the potential to ...
3
votes
2answers
208 views
Sorting the Table and getting the position
I am able to sort a table using the following code:SELECT * FROM Persons
ORDER BY LastName
But I wanted to get the ranking/position of a person using that order.
For example when I sort Person table ...
3
votes
1answer
1k views
When are InnoDB table index statistics updated?
In a vanilla build of MySQL 5.5.20, when are InnoDB table index statistics updated? What events trigger such updates? I've seen comments suggesting that the following might trigger it:
table is ...
3
votes
2answers
205 views
Getting recovered db files to work on rebuilt server
I've had to rebuild my server and have tried to copy the database files from the old drive to /var/lib/mysql/. I have restarted apache and run 'show databases', this list all the databases. I have ...
3
votes
2answers
826 views
How to handle the My SQL DB Master to Master replication when there was a disk space outage
We had master to master replication with two node nodes A & B, both are in virtual environment. Initially there was an outage (diskspace issue) in node A and stopped replication. Application level ...
3
votes
4answers
733 views
Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?
I am considering scaling up instead of scaling out. Therefore, I am interested to hear whether it is feasible to utilize the MySQL in-memory storage engine for a database that is 500+ GB, given that ...
3
votes
2answers
928 views
Trying to get multiple mysql instances running on Ubuntu dev box
I'm trying to get multiple instances of mysql running on a dev box for some testing. This guy is running ubuntu so the setup's a little different than our production setups.
I'd like to find the ...
3
votes
2answers
2k views
Internal reason for killing process taking up long time in mysql
I copied a big table's structure with (it is an InnoDB table btw)
CREATE TABLE tempTbl LIKE realTbl
Then I changed an index, and filled it up so I could run some test.
Filling it was done using:
...
3
votes
1answer
543 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
225 views
Character Set Encoding in a Table
I have the following data:Trollhättan
If I print out and setting up the header to utf-8, then this is the output in the browser.
If I'm not setting up the utf-8 in the header, Trollhättan. ...
3
votes
3answers
439 views
MySQL very slow query when changing one WHERE field despite no index/key
This is quite a confusing issue for me. I have a database full of baseball statistics. Running this query:
SELECT * FROM hits
JOIN stadiums ON stadiums.gameName = hits.gameName
JOIN players ON ...
