MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.

learn more… | top users | synonyms

19
votes
3answers
21k views

Why does InnoDB store all databases in one file?

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
4
votes
3answers
9k views

Optimizing InnoDB default settings

I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no ...
6
votes
2answers
11k views

How do you remove fragmentation from InnoDB tables?

I have a Database having number of tables. I want to delete some records from the tables say the no of records are more than 20K or 50K. The All the Tables are InnoDB. And file_per_table is off. ...
10
votes
3answers
4k views

MySQL InnoDB crash post-mortem

MySQL crashed on me this morning. With the exception of the standard MySQL included databases everything I use is InnoDB. I attempted to restart the MySQL daemon but it failed twice. I then ...
6
votes
2answers
2k 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 ...
5
votes
2answers
2k 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 ...
11
votes
1answer
12k 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 ...
6
votes
1answer
3k 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 ...
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 ...
1
vote
1answer
559 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 ...
2
votes
1answer
231 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 ...
3
votes
3answers
1k views

How costly is opening and closing of a DB connection?

How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL ... when the DB software is on localhost? ... when the DB software is on another machine?
5
votes
3answers
3k 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
2answers
1k 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 ...
10
votes
1answer
2k views

MySQL table with 100,000 records queried often

I have a single database of about 100 tables to store various kinds of information. The most important table is our order table which is used to store customers orders and is over 100000 records as ...
3
votes
1answer
573 views

Configured MySQL replication but its not working

I have configured MySQL master and slave replication setup on 5.5.x. But for some reason data is not getting replicated properly. What might be the reason? At Master: /etc/my.cnf [mysqld] ...
6
votes
2answers
274 views

Less restrictive query return less result due to simple removing one additional constraint

Look at this query SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance FROM ...
4
votes
1answer
6k views

What can cause TRUNCATE TABLE to take a really long time?

I'm running MySQL5.5 with Master/Slave replication (1 master, 2 slaves). I have a process that runs once a week and truncate a specific table. The table is not large and only has a few thousands ...
2
votes
1answer
1k 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. ...
2
votes
2answers
442 views

Any problem will all InnoDB and one MyISAM table?

I have a MySQL 5.5 DB with all InnoDB tables. For some reason, one of the developers decided to create a MyISAM table. It is a log table for user sessions. I don't see why we should mix in a MyISAM ...
2
votes
1answer
3k views

Fixing MySQL errors recorded in error logs

Below is the extract of the most common lines of error being recorded in error logs on my PC: 110905 16:16:54 [Note] Plugin 'FEDERATED' is disabled. 110905 16:16:54 InnoDB: The InnoDB memory heap is ...
5
votes
1answer
6k views

Should I increase max_connections in AWS RDS t1-micro for MySQL?

I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max ...
4
votes
5answers
7k views

How to properly kill MySQL?

I have CentOS 64bit with CPanel installed and I use service mysql stop However, it keeps doing ..... for minutes and it never stops. It used to be instant. Any idea why it does that and how to fix? ...
3
votes
2answers
995 views

Can we assign mysql Key_buffer_size value for particular table?

In some documents I found that we can assign key_buffer_size to myisam table. This also includes the following steps: SET GLOBAL myisam_table.key_buffer_size = 4194304; CACHE INDEX test.myisam_table ...
2
votes
1answer
3k views

MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

When reading online about innodb_log_file_size, it says The combined size of the log files must be less than 4GB So what happens if it needs to be larger? In books and online, it says to set ...
4
votes
2answers
823 views

Does a TRIGGER improve the performance?

I understand that TRIGGERs are useful, as they take care of subsequent queries, but do they have an advantage from performance point of view too? Replacing two or three queries as INSERT INTO table1 ...
5
votes
1answer
569 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 ...
3
votes
2answers
493 views

What is the meaning of tinyint(N)?

When we use an argument length with numerical data types, as far as I know this specifies the display width. I tried the following: mysql> create table boolean_test (var1 boolean, var2 tinyint); ...
2
votes
2answers
153 views

Tinyint structure is confusing

I am confused to see this tinyint(3) structure. If tinyint can store only 1 byte then what is the purpose of giving the size in brackets? Is there something special with this parameter in MySQL?
2
votes
1answer
685 views

MySQL Slave Relay Logging but not logging Binary Log

I have a master slave setup both mysql 5.5, the setup is fine except that the slave won't Binary log to the standard mysql-binlog only to the relay logs. I fear this may be due to my lack of ...
2
votes
1answer
381 views

Database Design - Creating Multiple databases to avoid the headache of limit on table size

I need to design database to store information contained in millions of log files generated by devices on trial on network. My first approach was storing all the information in tables contained in a ...
1
vote
1answer
163 views

Create a mysql slave database from master database

I have a mysql master database running. I want to have a slave database. So, I stopped the master mysql database and copied it to the slave machine. I had bin log enabled on master so even those files ...
6
votes
3answers
7k views

mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
4
votes
3answers
6k views

How to change a MySQL previous slave to be a master and remove slave status information?

I have a master -> slave configuration where the master failed. I've been able to reset the old-slave to be a master and the old-master to slave from it. Fine. What I can't seem to do is to remove ...
2
votes
2answers
2k views

How to improve MySQL Server Performance..??

As a MySQL DBA most of the times we are supposed to optimize a poorly performing MySQL Servers. Now my question is where to start from like we will need to find out many things as 1.Find the ...
7
votes
2answers
1k views

How do Application Like Yelp Retrieve distance information from data base efficiently

For example, say I have a table: Business(BusinessID, Lattitude, Longitude) All are indexed of course. Also there are 1 million records Say I want to find businesses closest to 106,5, for example, ...
3
votes
1answer
668 views

Keeping MySQL staging database on same server updated from production

I just migrated my MySQL 5.5.20 to Amazon RDS from MySQL on EC2. The one thing I'm having difficulty with is how to keep my staging database updated from production. Before, I just ran a nightly ...
2
votes
2answers
4k 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 ...
5
votes
1answer
375 views

Possibilities to speed up InnoDB INSERTs and UPDATEs

My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower. I'm monitoring when, and it ...
4
votes
1answer
3k views

Deleting bin logs in replication environment

I have a question about deleting binary logs in the replication environment: We have an environment with 1 master and 2 slaves (running mysql 5.5). Sometimes, we run into a space issues during heavy ...
4
votes
2answers
16k views

Grant table privileges to a user connecting from any host

I have a user account - let's call it 'wordpress' - that I need to allow to access a few catalog tables in another e-commerce database on the same server. I've configured the user with three host ...
3
votes
2answers
444 views

How to split a string to and get info from another table in mysql

I have a system to assign score for each sentence stored in a column: TABLE 1: word_scores word score this 3 is 4 a 5 test 1 another 0 sentence 8 TABLE 2: ...
3
votes
1answer
281 views

How to Update same table on deletion in MYSQL?

In my Database I have a table: Employee with recursive association, an employee can be boss of other employee. Following is query I used to create table: CREATE TABLE IF NOT EXISTS `Employee` ...
3
votes
1answer
1k views

What does the base64 BINLOG statements in mysqlbinlog output mean?

I have looked over the mysqlbinlog command as a candidate for rollback method from MySQL 5.5 to MySQL 5.0. When I ran the command on one of the bin-log files in the MySQL 5.5 server, I've noticed ...
2
votes
2answers
2k views

MySQL 5.5 High CPU Usage

I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was ...
2
votes
1answer
213 views

Restriction on self referencing on insert in MYSQL?

How to restrict insert on adding self referencing rows in a recursive relation table (a table contains foreign key points itself). mysql> SELECT * FROM Employee; ...
2
votes
1answer
978 views

How to upgrade MySQL to a New version..?

I have installed mysql using the package management system.I am using ubuntu machine. After that i have very well configured the server for InnoDB and other server related parameters. I am using the ...
1
vote
2answers
143 views

Is there a way to get slow-query-log-like profiling information on a single query?

Specifically, I'm looking for this information: # Query_time: 0.011922 Lock_time: 0.000088 Rows_sent: 107 Rows_examined: 663 Rows_affected: 0 Rows_read: 107 # Bytes_sent: 4152 Tmp_tables: 3 ...
5
votes
2answers
201 views

Is there a reason/benefit I am not aware of for this kind setup?

I now manage a MySQL database that was designed and created by another. I am confused about a certain setup that the original developer created.. He did not use auto increments for table IDs. ...
3
votes
3answers
1k views

Issue after moving the ib_logfile1 and ib_logfile0 files

I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of ...