MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.
1
vote
2answers
345 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 ...
2
votes
1answer
331 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
1k 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 ...
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 ...
1
vote
1answer
178 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 ...
0
votes
1answer
732 views
mysql closing tables taking double the time of process
I have a delete query that is taking a long time. When I check in processlist that status is showing as closing table.
It is taking a long time to close tables.
ex: I run a query and the total time ...
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 ...
1
vote
2answers
460 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 ...
4
votes
3answers
3k 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 ...
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 ...
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
270 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
268 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 ...
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 ...
2
votes
3answers
726 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
2answers
104 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 ...
3
votes
3answers
465 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?
2
votes
1answer
2k 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
830 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
161 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 ...
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
2answers
693 views
Some queries slower in MySQL 5.5 than 5.0?
We have an exceptionally inefficient process (1.3 million selects, 232k updates) that I'm now recoding to be 1 select and 50 updates. In the meantime, though, today's process ran.
We recently updated ...
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
1answer
377 views
How can I trigger this stored procedure on insert?
I have a table of error messages to which errors will be inserted. I also have a table defining interactions between errors. For example, when a "Power Restored" error occurs, it clears any previous ...
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
1answer
51 views
2 Very similar queries return different number of result (one is null)
I want to find businesses near a point that contain a word that start with sea
lat=-6.185068&long=106.773121&keyword=sea&distance=5000&startfrom=0&howmany=20&prominent=5
...
5
votes
2answers
196 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
...
-2
votes
1answer
771 views
Why does MATCH (FullTextSearch) AGAINST ('*' IN BOOLEAN MODE) return nothing?
MATCH (FullTextSearch) AGAINST ('k*' IN BOOLEAN MODE)
return something
Namely anything with a word that start with k
MATCH (FullTextSearch) AGAINST ('ku*' IN BOOLEAN MODE)
return less
MATCH ...
0
votes
1answer
131 views
What database do most mobile start ups use? [closed]
mongodb?
MySQL?
PostgreSQL?
If someone would add pluses and minus, let me know.
Why I ask:
I am a mobile startup. I need a database that supports spatial indexes. The spatial index must be able to ...
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
139 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 ...
3
votes
1answer
1k views
MySQL transaction size - how big is too big?
I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction.
There are many aspects, and the imports may yield anywhere between ...
2
votes
1answer
219 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]
...
1
vote
1answer
95 views
How to Archive Only MySQL Master not the Slave.?
Currently I have a Master and Slave.
Both of them have same set of data(Can Say are in Sync).The Replication is running fine.
What i need to accomplish is that...
Need to Archive the Master(Say I ...
1
vote
2answers
1k 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 ...
0
votes
1answer
70 views
Can MySQL be patched
I just saw a note about a security issue in MySQL so thought that I'd better patch my test server just in case. The MySQL site is not very helpful and a quick google revealed lots of people asking how ...
2
votes
2answers
565 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
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
1answer
275 views
“set-variable = innodb_thread_concurrency=4” or “innodb_thread_concurrency=4”
Based on this question, the asker wrote:
set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = ...
3
votes
1answer
98 views
mysqld-multi with first DB as Slave and second DB as Master
Yesterday, I setup mysqld-multi in my.cnf. It works fantastic, but there is still a further Problem.
The first DB on socket 1 running as Slave from a other server. The second one from socket 2 ...
2
votes
3answers
4k 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 ...
2
votes
1answer
839 views
Can I safely kill OPTIMIZE TABLE on an InnoDB table?
MySQL's documentation for kill warns:
Warning
Killing a REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM table results in a table that is corrupted and unusable. Any reads or writes to ...
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
184 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?
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
...
1
vote
1answer
221 views
Change root password of mysql database
This could be a Rookie problem , but it seems that i am not able to solve it.
I have MySQL server installed on my windows machine(MySQL 5.5) under the Admin username root.
Some How i lost the ...
3
votes
3answers
477 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?
