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

learn more… | top users | synonyms

1
vote
1answer
743 views

How performance schema in mysql 5.6 improve performance?

Since MySQL 5.5 performance_schema database has been added likewise information_schema. As we know that tables in information_schema contains statistical information like tables, plugins, partitions, ...
2
votes
2answers
425 views

Are there benefits to using more than 4 CPUs for MySQL?

We are running MySQL 5.5 on linux under VMWare, running on 2 CPUs. We are planning on increasing this to 4, but we have un-used CPUs available, and I'm wondering if there is any benefit to increasing ...
0
votes
1answer
1k views

Setup mySQL in a chroot in Debian Wheezy

Sounds like a simple question, but the documentation is rare about what mySQL needs to run inside a chroot. I think I'm almost there, but I'm running into a permission problem that I can't solve, and ...
0
votes
0answers
144 views

MySQL 5.5, Xubuntu 14.04 - INSERT and UPDATE bottleneck

DBAs: I have been developing and maintaining plugins for Moodle for the last ~2 years, and have recently decided to try using my local desktop again, rather than my institutions ESX VMs, as a ...
2
votes
2answers
541 views

How to monitor InnoDB Disk I/O using Server Status Variables?

This question is extension of my previous question "How to monitor InnoDB Disk I/O Required for MySQL? " As mention in my previous question i want to count total number of disk i/o per query, but ...
1
vote
1answer
296 views

Safely changing innodb_buffer_pool_size on MySQL replication master?

We have a MySQL server which is a replication master. We need to change the innodb_buffer_pool_size and innodb_flush_method to (hopefully) improve performance as the database has grown. What is the ...
1
vote
1answer
109 views

How to log within a stored procedure that is encapsulated within a transaction

I have a stored procedure, let us call it sp_A which calls other stored procedures, let us call them sp_B and sp_C. For the sake of consistency an XA TRANSATCION is started within sp_A. What I ...
0
votes
1answer
408 views

Dedicated mysql server slow response [closed]

I have server in Kimsufi with 2gb RAM but my server is very slow , with simple hosting in ovh it responds quickly ... With pingdom.com test i have for server: 10 sec and simple hosting: 2sec is ...
1
vote
1answer
926 views

How to Reclaim Data Free in InnoDB Tables?

I came up with an idea on how to shrink ibdata1 in mysql database then i got this solution on stackoverflow STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql) STEP 02) Drop ...
0
votes
1answer
142 views

Two MySQL requests at the same time - Performance issue [closed]

I have a MySQL server with many innodb tables. I have a background script that does A LOT a delete/insert with one request : it deletes many millions of rows from table 2, then insert many millions of ...
1
vote
0answers
686 views

What to do with unused RAM on a MySQL database server?

For years I have been running dedicated servers with more or less limited ressources, resulting in unsatisfying performance of the web application (Drupal). Tools like Matthew Montgomery's MySQL ...
-1
votes
1answer
14 views

What should I do if i forget password in MySQL?

I am using MySQL5.5. I trying to login in mysql from terminal by using "sudo -u root -p" But i could not remember password. How can i fix this problem?
1
vote
2answers
394 views

Mysql improve Self join performance large table?

I have a task to help identify conflicting IP ranges , I want to highlight when one companies IP range falls within another's ... below is the sample table, I have converted the IP ranges from .dot ...
0
votes
2answers
617 views

MySQL Community Edition Capacity

I am using MySQL Community Edition on Amazon RDS service. It is a very large database, 2/3 tables has about 10 millions records and 4-5 tables has 7-8 millions records. Questions How much capacity ...
0
votes
0answers
94 views

tables don't exist after unmounting and remounting external drive where database was installed

I want to create a database that's too big to keep on my own machine, so I'm trying to create one on an external hard drive. It works up until I unmount and remount the drive, at which point show ...
2
votes
2answers
395 views

MySQL user without SUPER privilege

This may be a simple question and may not be the right place to ask but I couldn't find any useful information from Google. Here is my question. When creating users in mysql using grant all ...
0
votes
1answer
46 views

ON DELETE CASCADE pseudo delete

For data integrity ON DELETE CASCADE is necessary but it's really dangerous when a delete affects like a Chain Reaction! I know one way to prevent this is to make a pseudo delete by putting a ...
0
votes
1answer
224 views

Select post and concatenating all categories it belongs to

I have 3 DB tables POSTS +----------+----------+----------+ | id | title | content | +----------+----------+----------+ CATEGORIES +----------+----------+ | id | title | ...
2
votes
4answers
279 views

Aggregate query optimization

Could you please help me to optimize this query - currently it takes ~4 minutes to run this query. It looks like keys: ip and timestamp are non-unique so shall I add some indexes? mysql> EXPLAIN ...
1
vote
1answer
940 views

Master-Master Replication Duplicate Key

I have set two servers master-master replication mysql , i manage to set successfully but the problem is the duplicate key i got MASTER 1 Configuration /etc/my.cnf [mysqld] server-id=1 bind-address ...
0
votes
1answer
130 views

want to download 3GB database table from my development mysql server [duplicate]

I need to download database structure with data. Total size of the DB is more than 3GB. If i use phpmyadmin it is not working. I have also tried by increasing the execution time in php.ini. I need ...
0
votes
1answer
527 views

Whether to use or not to use: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

In one Database table, created_at & updated_at are defined as: `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP `updated_at` timestamp NOT NULL DEFAULT ...
-1
votes
1answer
66 views

Query to retrieve data based on several where conditions ( Not off Topic , Answer provided below is right ) [closed]

Database : Mysql 5.5 i have a table T1 containing 4 columns : No. , Height , start_time , end_time In column height i have 3 distinct values only : Small Medium High Now i ...
1
vote
0answers
86 views

Refactoring IN Subquery, Avoiding Query in a Loop

I've got a table which was designed as both the current data set for participants, as well as being the historical changeset for those participants. This table has been in use for approximately three ...
1
vote
1answer
398 views

Is there a product to auto-build indexes based on slow queries?

I'm looking to either find, purchase, or write a script to identify slow queries, identify potential missing indexes, and when needed create the appropriate index within a reasonable amount of time of ...
0
votes
0answers
293 views

Create Drop Temporary Table very slow vs Truncate

I've got this stored Procedure that is called by an event that runs every 1 minute. The stored procedure selects some data from one device and puts it in a temporary table. Than it picks up other ...
2
votes
1answer
415 views

Unable to run update_mysql in MySQL 5.5

I have recently taken over managing a MySQL database running on Windows Server 2008 R2 . It is running 5.5.37 Community Edition. When it is started, it includes the following lines: Thread, Type, ...
0
votes
0answers
148 views

Upgrade from mysql 4.1 to mysql 5.5

I was going through upgrade process of mysql server. It is mentioned that to upgrade from 4.1 to 5.5 we need to upgrade step by step like first to 5.0,5.1 ...5.4 and then 5.5. I have some questions : ...
2
votes
0answers
21 views

MySQL slow query log not logging all slow queries [duplicate]

I am trying to analyze the hibernate query performance in our application. I tried logging all hibernate queries which take more than 100ms execution time from application side as well as using mysql ...
0
votes
1answer
40 views

MySQL database configuration for fastening the select and insert queries

There are several DBEngines are available for MySQL DB. Currently I'm using InnoDB. My actual problem is, I have a table that consist of millions of data. Therefore the select queries that given to ...
0
votes
2answers
393 views

Slow INSERT/UPDATE on InnoDB

I have a problem with the performance of my MySQL server, let me explain: I instaled a MySQL server, version 5.5.20 Server version: 5.5.20-log Source distribution SELECT queries runs fast on ...
0
votes
2answers
18 views

Joining on 3 different keys as they're set

I have these tables: TABLE vessels ID int PK organization_id int FK fleet_id int FK name VARCHAR TABLE settings ID int PK organization_id int NULL FK fleet_id int NULL FK vessel_id int NULL FK ...
2
votes
1answer
46 views

Will composite indexes or unique indexes help with query performance?

I need help with identifying indexes to increase performance of the following query. I'm currently using v5.5 with InnoDB. I cannot modify the query. However, I can add indexes as needed. The ...
0
votes
0answers
28 views

How can I determine the performance (rows/second) for a certain query on one of my tables?

I want to find the performance (rows/second) for an UPDATE query on one of my tables so I can adjust my WHERE clause to select an appropriate number of records. The table has around 10 million rows. ...
1
vote
1answer
138 views

Slow queries InnoDB occasionaly

I have the next table: CREATE TABLE `sessions` ( `session_id` char(32) COLLATE utf8_unicode_ci NOT NULL, `session_expires` int(11) NOT NULL DEFAULT '0', `session_data` varchar(10000) COLLATE ...
1
vote
1answer
63 views

How can I make multiple joins on the same table?

I have a table (signal) which contains references to two other tables (ee and eeav). I want to perform an SQL Query which combines the data from ee and eeav tables. The situation is as follows: The ...
1
vote
2answers
6k views

How to outer join multiple tables to retrieve uncommon records

I'm trying to do an outer join in MySQL to identify records not common to 3 tables. Here's my current effort http://sqlfiddle.com/#!2/02b92/30 I'm trying to retrieve the records not common to all ...
1
vote
1answer
30 views

Is it possible to find the user whose aggregated score reaches X value first?

Given this sample fiddle containing sales value and date for two users: http://sqlfiddle.com/#!2/8148be/3 How can I find the first user who has reached a total saleValue of 30 since the beginning of ...
0
votes
1answer
207 views

Is there a way to truncate the microseconds of a CSV value when importing data using LOAD DATA INFILE?

I'm loading data using LOAD DATA INFILE in 5.5 where the value includes microseconds. I must use DATETIME data type. Currently, I'm using regular expressions to remove them from the CSV prior to ...
0
votes
2answers
143 views

Buffer pool above 90% utilized causes MySQL to crash

Without getting deep into the architecture and setup, is it common behavior for MySQL to act in this manner? EDIT I'm using Rolando's script to identify how full the innodb_buffer_pool is. SELECT ...
1
vote
1answer
210 views

database design for dynamic menu bar [closed]

I am trying to create a menu bar which will load dynamically on user log in, based on the user roles. Say if the user is an admin he can see all the items listed under the header and if a user is a ...
0
votes
1answer
1k views

How to resolve “Error log file size is increasing in mysql 5.5” issue w/o restarting mysql

I have an Ubuntu server with MySQL 5.5 installed. In /var/lib/mysql a .err file is kept on updating with these warnings only: [Warning] IP address '234.22.11.221' could not be resolved: Temporary ...
0
votes
1answer
47 views

Will MySQL redirect a read request to the master after a certain time? [closed]

I can't find what variable controls this or any information about the time limit. Thanks!
1
vote
1answer
107 views

How to find the length of multiple column index in MySQL

Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command: CREATE TABLE test_ ( testId bigint(20) NOT NULL, name varchar(75) ...
3
votes
2answers
3k views

MySQL COUNT Query taking too long time to execute

I have below query that takes 7 secs to run SELECT COUNT(*) AS `count` FROM `yuldi`.`businesses` AS `Business` LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory` ON ...
1
vote
2answers
86 views

Are relay logs necessary for replication?

I'm trying to understand when/where using relay log is beneficial. According the documentation, mysql captures and writes events from the master's binary log as the result of the I/O thread. Then, the ...
1
vote
2answers
298 views

Nested query efficiency in MySQL

I have a table with several attributes like this: id id2 attribute -------------------------------- 1 100 blue 2 100 red 3 100 green 4 100 white 5 102 blue 6 102 green 7 102 ...
1
vote
1answer
31 views

Is there any value in adding an already indexed column to a composite index?

The title says it all. Does the query optimizer see composite indexes where each column is already indexed as redundant? Or, is it used when each of the composite index's columns are in the WHERE ...
1
vote
1answer
111 views

Migrating MyISAM tables from 5.5 => 5.6

I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync? Is this safe? I know we need to do a full ...
1
vote
2answers
48 views

How can I store least used table files to a slower drive?

Is it possible to separate the least used and most frequently used data files on to separate disks when using InnoDB?