MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.
5
votes
1answer
221 views
effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried
I am looking for advice on table/index design for the following situation:
i have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
5
votes
1answer
173 views
MySQL varchar(255) limitation & Anomaly
I have defined a column in my MySQL database as :
ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 )
CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL
However, when I enter text ...
3
votes
1answer
74 views
In MySQL, does the order of the columns in a WHERE clause affect query performance,why?
I have a query that doesn't use any indexes:
SELECT 32,
guid,
1,
1,
1,
0,
5
FROM test
WHERE level >= 20
AND ( ( fun_GetIndexValue(data, 354) ...
5
votes
0answers
214 views
Migrating MySQL to new server
We have a MySQL 5.1 instance running on Windows Server 2008 R2 x64 (Server A) replicated to another MySQL 5.1 instance on Windows Server 2003 R2 (Server B).
We have a new MySQL 5.5 instance on ...
3
votes
0answers
273 views
Convert Oracle Query for Date to Mysql queries
Need to convert the following oracle queries to MYSQL queries.
1)
select to_char(to_date(?,'DD-MM-YY'))
from dual;
2)
select to_char(next_day(to_date(?,'DD-MM-YY'),'FRIDAY'))
from dual;
3)
...
1
vote
0answers
28 views
Repeated values in group_concat
I have two tables, first the table food and Second is Activity:
INSERT INTO food
(`id`, `foodName`)
VALUES
(1, 'food1'),
(2, 'food2'),
(3, 'food3'),
(4, 'food4'),
(5, ...
1
vote
0answers
23 views
Error in alter table reorganize partition for MySQL Cluster
My cluster contains 48 million rows and cluster contains 4 storage nodes . After that I add online storage node and executed alter table 'customer_tbl' reorganize partition.
Some parameters of ...
1
vote
0answers
300 views
“Lost connection to MySQL server during query” error
I have MySQL 5.5.20 and this table:
mysql> desc country;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
...
1
vote
0answers
33 views
Is there a canonical list of MySQL defaults which should be changed
I'm setting up a new mysql server as we're building a new app at work.
Most of the developers/operations guys are familiar with mysql.
Many of mysql's settings (eg strict mode) are set badly by ...
1
vote
0answers
205 views
Trouble with triggers and insert .. on duplicate key update
I have two tables with identical schemas. one is a "main" table that is pruned periodically and kept small. The other is a "reporting" table that is an archive of everything that ever was. For ...
1
vote
0answers
145 views
MySQL cluster stuck in start phase 4, how to proceed?
Over the weekend our cluster we set up for evaluation had one of its data nodes lose network connectivity briefly. This was while another data node was in the middle of a restart (which had been going ...
1
vote
0answers
57 views
How to create multi volume file backup using symantec backup.exec
I am using symantec backup .exec for backup and restore in mysql server . Now I am in need to take larger database backup in tape .But it exeeds the tape storage and I am trying to create a multi ...
1
vote
0answers
55 views
How to profile individual queries using PERFORMANCE_SCHEMA
I've been looking at SHOW PROFILES, which seems ideal for what I am doing.
However, I've noticed a warning that SHOW PROFILES is being deprecated and that PERFORMANCE_SCHEMA should be used instead. ...
1
vote
0answers
73 views
MySQL replication backup
How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu.
What happens if,
I issue or schedule ...
1
vote
0answers
140 views
Finding differences between a master and slave when pt-table-sync fails
I have a table that's showing some differences on a master and slave according to pt-table-checksum (PTC). I've found some verifying differences beyond that.
Certain tables in the mix have composite ...
1
vote
0answers
142 views
How to speed up this query
MySQL doesn't have nearest neighbor search so. So I try to come up with a "distance" that's reasonable so the number of returned query is around 20-200. That way innodb doesn't have to compute ...
1
vote
0answers
143 views
mysql server is not sending result set back after query execution to client and staying in sending data state
When I pass a query from my Java application to mysql server, it stays in Sending Data state and not returning result set back to application.
When I execute the same query in SQLYog directly, it ...
0
votes
0answers
13 views
Will Partitions and Indexes on the same table help in performace of Inserts and Selects?
I have a table containing the list of visitors and this table has the following information.
Visitor Browser Information
Visitor Location Information
Visitor Time Information
No of Visits
I have a ...
0
votes
0answers
20 views
Commit count while using LOAD statements in MYSQL
I have a very big text delimited file, I am using Mysql LOAD statement but i want to put some commit count, for example if i put commit count 1000 then after inserting every 1000 rows it should commit ...
0
votes
0answers
19 views
Should store procedures be cached?
Should store procedures be cached in MySQL? If so, How long it is stay in cache?
In my case, When I call one stored procedure first time, it is giving me results in 1 second, after that it gives me ...
0
votes
0answers
31 views
MM Replication and Zabbix in slave, Error code 1032
I have multi-master replication and using Zabbix for server monitoring. Since the replication is row based, I get this error in slave everytime which also breaks my replication. The error I get is:
...
0
votes
0answers
29 views
MySQL replication is not happening when piping sql into mysql client
Today I noticed a very strange issue where if I cat a SQL file and then pipe it to MySQL client, replication doesn't happen. These MySQL statements aren't also written to the binlog. But replication ...
0
votes
0answers
23 views
ERROR while doing coalesce partition Mysql Cluster
My table contains 3299000 rows. My Mysql Cluster contains 2 storage nodes with NoOfReplicas=2. By default it contains 2 partitions and after that i added 3 partitions more one by one using command ...
0
votes
0answers
24 views
After optimize table statement my data usage increased Mysql Cluster
I deleted 500000 rows in my mysql cluster table having 12468000 rows and then i tried to OPTIMIZE TABLE so that all the freed up data memory can be used by another table also, but after optimizing ...
0
votes
0answers
18 views
Re-use disk data extents after delete Mysql Cluster
In my mysql cluster i have 1 million rows and my disk data tables are using all the disk usage available to them ;i.e; free extents are 0 . Now i deleted 50K rows from ap_perf_tbl but still it show ...
0
votes
0answers
20 views
Why data usage increases when deleting rows Mysql Cluster?
My mysql cluster data nodes data usage increased to 95%. Then i tried to delete 500000 rows and my data usage of nodes started increasing initially and reached to 99% and above and TABLE FULL error is ...
0
votes
0answers
46 views
Mysql query is very slow
I am developing a website like any other social networking site using mysql.
I wish it give people suggestion to my users, and I have implemented this functionality in my application, but It is ...
0
votes
0answers
27 views
mysqlbinlog (mysql5.5) output errors out (char length)
wondering why an invalid insert (exceeds char length varchar(100)) breaks when piping mysqlbinlog output to RDS-- when pasted into a client and run separately-- it inserts, truncates, and warns ...
0
votes
0answers
479 views
1286 - Unknown storage engine 'InnoDB'
I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a ...
0
votes
0answers
20 views
View is not dropping
I tried removing a view in HeidiSQL, and it froze bunch of times. So I connected to a database with Putty, ran a command
DROP VIEW item_view;
and nothing happened, its still running and command ...
0
votes
0answers
66 views
Migrating database from OSX to Linux
I followed the setup in below link, to setup mysql. Basically, I am have a master on OS X server and want to setup a linux slave.
Create a mysql slave database from master database
After I copied ...
0
votes
0answers
17 views
How to get details of changes in point in time recovery using mysqlbinlog
I have written a shell script for point in time recovery using binary logs( Point-in-Time Recovery Using Event Times). I'm using the start time as the previous full-backup start time and end time will ...
0
votes
0answers
48 views
Returning multiple resultset from the same table
i am working with mysql procedure. final result set i have got in to one table.
now i need to return for each set i need to return the value.
example:
like this is contents in my the table.
i need ...
0
votes
0answers
185 views
What to do to reduce first byte time after removing slow queries?
I've set long_query_time to 1 sec and found only 2 query which took 1 sec to execute at 2 different points of time since yesterday. So I don't suppose such 2 queries cause my first byte time to be 3 ...
0
votes
0answers
81 views
Please help configure my Mysql for 12GB 4cores server
I use a dedicated server with 12GB memory and 4 core CPU with Ubuntu 12.04 LTS (x64). This not just a database server. MySQL appears to be restarting frequently, I think I can tune the config to ...
0
votes
0answers
110 views
how to improve performance of my query it was taking more than one hour in my environment
How can I improve performance of below query? It was running more than one hour to execute in my production environment. Query plan:
mysql> explain
SELECT transaction_detail.trans_id,
...
0
votes
0answers
142 views
Optimizing MySQL for a shared hosting setup
This is my current setup:
[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
max_connections = 500
max_user_connections = 25
key_buffer = 64M
myisam_sort_buffer_size = 32M
...
0
votes
0answers
164 views
Error connecting to DB
In regards to:
http://serverfault.com/questions/442218/remounting-mysql-under-a-new-mount-point
Everything is running. MySQL started but for websites they can not connect to the mysql database at ...
0
votes
0answers
142 views
MySQL import file with no column delimiter
Is there a way to use the mysqlimport command on a file that has no column delimiters?
I would like to import the ctl file used with the sqlloader for Oracle
0
votes
0answers
54 views
memc_delete called from a MySQL trigger loop fails randomly
I'm calling memc_delete from a MySQL trigger's code. The call is inside a loop. Logging the results, I see that memc_delete sometimes returns 1 (ok, memcached entry deleted) and sometimes returns 0 ...
0
votes
0answers
58 views
How to perform several operations at DB level
I need to insert 25 000 or 50 000 test rows for make some performance tests in several tables in my DB. I'm using PHP but the script takes to long to run so in shared hosting is a pain! I'm asking if ...
0
votes
0answers
261 views
Potential cause for memory leak
I have a replication setup where one of those nodes started experiences a slow but steady memory leak.
The setup is kind of like this
M---->S
|
\/
FilteringSlave
| | | |
R1 R2 R3 ...
0
votes
0answers
51 views
Partition manager utility/script?
Does anyone know of any Utilities or scripts out there for partition management. Most of our partitioning is range based and I have a set of home grown scripts to expand as needed as well as maintain ...
0
votes
0answers
40 views
How can I make MySQL rework its foreign keys?
I somehow tricked MySQL into believing I have a foreign key that does not exist.
I had a table with both an Id column and a Number column. I wanted to merge the two, so I cleared the few tables ...
0
votes
0answers
98 views
slow response from MySql server
We just moved our database from an old server to a new one with more processing power and 4G RAM. However, for some unknown reason, the execution of our store procedures became even slower. An ...
0
votes
0answers
108 views
Does full text search allow partial world
Say I want all restaurants that contain sushi
My user search for sus, ush, and shi
Now I want mysql to efficiently find rows whose title or description (or whatever) contain sus, ush, and shi, ...
0
votes
0answers
140 views
Nearest neighbor search for 1.6 million points in myisam mysql
Say I want to find 20 closest business near me. Latter I want to see the next 20 closest, etc. Yes I have myisam. I have spatial indexes on the point. I am looking for actual command.
Note:
I am ...
0
votes
0answers
186 views
Mysql 5.5 hangs from time to time with ubuntu 12.04
I am using Mysql 5.5 from ubuntu 12.04 64 Bits with both MyIsam and Innodb engines : From time to time, when I do some 'big' requests like dropping a database, altering a table, adding an index on a ...
0
votes
0answers
153 views
Are there any caveats to install mysql 5.5 in CentOS 5?
I need to install mysql 5.5 in cent OS 5. As Cent OS 5 is a little bit old is there any odd behavior when you want to install mysql 5.5 on Cent OS 5?
0
votes
0answers
204 views
Making chain of Master-Slave servers in mysql
I have three servers, Server X, Y (Version 5.1.54-1ubuntu4-log), and Z (version 5.5.18-2build1~ppa1~oneiric-log).
I have the main mysql DB on X (innodb DB).
now I have created Master-Slave from X to ...
