MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.
2
votes
2answers
3k views
innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition
In one of my production environment, we have two instances running on a RedHat cluster, with one production instance associated with the cluster.
We have 125G main memory with 24G InnoDB buffer pool ...
6
votes
1answer
2k 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 ...
2
votes
1answer
76 views
Count total where owner_user_id = some_value and deleted_by_owner = 0 or to_user_id = some_value and deleted_by_to = 0
I didn't know if this is possible using just a query or if I need some procedural logic on this one.
I have this table:
CREATE TABLE `default_messages_users` (
`id` int(11) NOT NULL ...
1
vote
1answer
874 views
MySQL Workbench ERROR 1064
I have a problem with my database. I have created the model and now I want to sync it with my database on my WAMP Server (local) but it keeps giving me an error. I searched for the cause of the ...
4
votes
1answer
812 views
Setting up MySQL Cluster on Two Systems
I am attempting to setup a MySQL Cluster.
I have the folllowing setup
one management node
two data nodes
another machine on which two more nodes reside
Below is the config.ini file:
[ndbd ...
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 ...
3
votes
1answer
835 views
Slow insert performance in mysql innodb table for solr indexing
I have a Slave server (MySQL 5.5) having innodb tables. This server is being used for SOLR indexing. Few procedures used to fill 12 million data into 9 tables. The insertion speed is too slow.
...
2
votes
1answer
189 views
Assertion failure from InnoDB while restoring using Innobackupex
I thought I would give back to all those people who help me when I have a problem. This might be something old, but I did not find it anywhere so here goes:
We recently upgraded our production DBs ...
-2
votes
1answer
149 views
Compare MongoDB and MySQL
Can anyone help me to make right decision for choosing scalable and reliable database?
I have to create 3-4 tables with around 200 columns each and these tables will have around 5000 data records. As ...
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 ...
5
votes
2answers
741 views
how to enable temporal database capability in mysql.?
How to enable the temporal database compatibility in mysql database?
Is there any library or a plug-in to enable the temporal upward compatibility? I want to keep the past records whenever a current ...
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
1answer
127 views
Get and count all blog posts and all users that fits a LIKE condition (two sepparated queries)
I've this tables:
--
-- Table structure for table `default_cmvblog`
--
CREATE TABLE IF NOT EXISTS `default_cmvblog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) COLLATE ...
4
votes
2answers
5k 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.
...
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 ...
1
vote
1answer
598 views
I am getting an error while trying to download binary for mysql 5.5.11
I am trying to upgrade MySQL 5.1 to 5.5.11 on Ubuntu 10.10. Everytime I try the wget command I get this error:
wget ...
2
votes
2answers
144 views
MySQL - How much physical space will be reduced by changing the column type?
Lets say I have the following table:
CREATE TABLE `my_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`relationship_status` varchar(48) CHARACTER SET utf8 COLLATE ...
0
votes
1answer
89 views
How to set unique columns combination
see this table:
CREATE TABLE IF NOT EXISTS `default_messages_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`message_id` int(11) NOT NULL,
`owner_user_id` int(11) NOT NULL,
`to_user_id` ...
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 ...
3
votes
1answer
1k 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 ...
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 ...
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 ...
4
votes
1answer
63 views
Will these queries return the same results?
I have this simple query:
SELECT
friend_id
FROM
default_friend
WHERE
user_id = 1
And the results are below:
+-----------+
| friend_id |
+-----------+
| 2250 |
| 4901 |
| ...
2
votes
1answer
128 views
Need some help with this query get all status and their associated comments of me and all my friends
I've several days dealing with this query but can't get it to works as I want. This post is related to this one Several values from different tables in which I tough found the solution but I was ...
1
vote
1answer
103 views
Several values from different tables
I'm totally lost here and not know how to get this to work and hope any can help me here. I have several tables: default_users, default_profiles, default_status, default_comment and latest ...
0
votes
1answer
138 views
Is possible to optimize this query or it's already optimized?
I've three tables: default_users, default_profiles, default_friend. This are the SQL for each one:
--
-- Table structure for table `default_users`
--
CREATE TABLE IF NOT EXISTS `default_users` (
...
1
vote
1answer
2k views
Function returning “No data - zero rows fetched, selected, or processed” in MySQL 5.5
I have upgraded MySQL version to 5.5.16
After upgrading, some of the functions are giving warning as No data - zero rows fetched, selected, or processed.
In those functions, cursors are there, but ...
2
votes
2answers
222 views
Tool to match mysql password encryption
I've created a user ... but forgotten the password
mysql> create user 'blayo'@'%' identified by 'right';
Which Linux command line tool can encrypt the password the same way mysql does ?
mysql> ...
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 ...
5
votes
2answers
174 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. ...
1
vote
2answers
350 views
Warnings: Why are statements with limit clause unsafe even if there isn't a limit?
I'm a bit irritated because of some warnings in mysql errorlog.
Example 1:
[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
The statement ...
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 ...
2
votes
1answer
338 views
Extract some columns data from one table and insert into multiple tables
I have a table with 16,000 records and need to get out some columns and then insert values in multiples tables. For example, suppose I have the following tables
tbl_1: id, name, lastname
tbl_2: id, ...
0
votes
0answers
137 views
I need a good mySQL 5.5 Enterprise Administration Book [closed]
Beside the one online from oracle
Anyone know any good book about this subject
I have found many of mySQL 5.5
But i need it for Enterprise Administration
Thanks for the help
4
votes
2answers
5k views
way to prevent queries from waiting for table level lock
We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
1
vote
1answer
179 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 ...
4
votes
2answers
101 views
DELETE LOW_PRIORITY row visibility
I have a large MyISAM table on MySQL 5.5(Windows XP x64) on which I will have to run DELETE LOW_PRIORITY queries. Does DELETE LOW_PRIORITY make the rows invisible to SELECT statements immediately and ...
3
votes
3answers
473 views
What are the limits on the amount of slaves per master?
I currently have a Master with 2 slaves, all running MySql 5.5.
What are the limitations on the amount of slaves I can connect to a single master? what parameters should be taken into affect?
1
vote
2answers
464 views
Need suggestions for best way to archive MySQL (InnoDB) tables
Problem:
I have two fairly large tables. A 'wall' table for messages between users with 9 million rows at 2 GB and a 'chapter' table at 2 million rows and 18 GB. I want to keep the number of active ...
2
votes
2answers
1k 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 ...
2
votes
3answers
731 views
MySQL backup with no downtime
I'm running a M/S replication of MySQL 5.5 / innoDB (over ubuntu).
My DB is pretty large: ~250GB.
I'm looking for a backup tool/service that will do the following:
full backup
no downtime / locks ...
1
vote
1answer
273 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
3answers
1k views
innodb_buffer_pool_size not changed
I use MySQL 5.5.23-1~dotdeb.0 (Debian) and here is the relevant part of my.cnf
default-storage-engine = innodb
innodb_buffer_pool_size = 3G
innodb_log_file_size = 256M
innodb_thread_concurrency = 8
...
3
votes
1answer
55 views
where varcharcol = 0 returns all rows
Suppose I have a table like this:
create table mytable(cola int primary key, colb varchar(10));
Let's put some data in it:
insert into mytable values(1, 'abc');
insert into mytable values(2, ...
3
votes
1answer
124 views
Get profiles values where message.sender_user_id = profile.user_id
I have this tables:
default_messages
CREATE TABLE IF NOT EXISTS `default_messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
`message` text ...
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
1answer
88 views
slave issue with load data
When I use "load data infile" on master, the data is properly transferred to slave.
But when I use "load data local infile" from remote server to master, the data does not pass on to slave.
Why does ...
3
votes
1answer
3k 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 ...
9
votes
1answer
841 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 ...
-1
votes
1answer
164 views
What exactly is SOLR? [closed]
It seems like a magical technology that can
Do spatial search
do full text search
The problem is,
It doesn't say where does it store all the data. Does it have it's own proprietary database
If it ...
