The Prodromus Group has announced a plan to perform Pro Bono MySQL DBA work for qualified organizations. As part of our mission, we feel that it is important to give back to our community, both locally and globally. If your organization believes that it may qualify, please contact us at [email protected] While we specialize in MySQL, our organization has extensive experience in IT operations and building high quality public facing web sites at an affordable price. We recently helped a local charitable firm develop a web site to solicit on-line donations, and they now receive over 50% of their donations via their website.
Examples of qualified organizations would be those focusing on Environmental and Human Rights initiatives, local schools, fire and police agencies, etc. Our goal is to help these organization receive quality technical advice and service that they may otherwise be unable to afford.
Contact us today at [email protected]
Filed in MySQL, Pro Bono
|
Tagged community, DBA, Environmental, free, human rights, MySQL, Organizations, police agencies, Pro Bono, pro bono work, Prodromus
|
If you have seen this error, don’t worry, you are not alone. This error is often accompanied by a message that a table or file could not be created, and usually happens when creating a foreign key. In my experience, 99% of the time this is due to an incompatibility between the two fields in the foreign key. Usually it is something simple like unsigned integer to signed integer. The trickiest I have seen is when trying to create a foreign key between two CHAR fields and they do not share the same CHARSET and COLLATE. ALTER the table so that the CHARSET and COLLATE are the same, and try to add the foreign key again.
Let me know if you have other examples of how you have worked around errno: 150 when adding foreign keys.
Filed in Errors, MySQL
|
Tagged ALTER, CHARSET, COLLATE, Foreign, foreign key, incompatibility, Innodb, integer, key, MySQL, table, UNSIGNED, unsigned integer
|
The number one thing that you can do to speed up Slave Replication is to set innodb_flush_log_at_trx_commit=0 in your my.cnf file. This will make the transactions less recoverable on your Slave in case of a crash, however with a Slave this is usually an acceptable risk. This setting prevents MySQL from forcing a fsync after every transaction, allowing transactions to be batched up and all fsynced in one operation. When using slower HD RAID’s, this is a huge performance benefit.
Setting sync_binlog=0 will also prove to be beneficial, but also at some level of additional risk.
Filed in MySQL, Replication
|
Tagged binlog, fsync, Innodb, innodb_flush_log_at_trx_commit, MySQL, performance, RAID, Replication, Slave, transaction
|
Using ALTER IGNORE TABLE [TABLENAME] ADD UNIQUE INDEX `UNIQUE_INDEX` ([FIELDNAME]) to remove duplicate rows in a table is a fast an efficient process, however on large tables where the physical size is larger than server memory, the ALTER statement can take a long time to run in a production environment.
If you need to remove duplicates on a very large table (we recently used this on a table of 77 million rows), try this method :
delete t1 from table t1, table t2
where t1.duplicate_field= t2.duplicate_field (add more if need ie. and t1.duplicate_field2=t2.duplicate_field2)
and t1.unique_field > t2.unique_field
and breakup into ranges to run faster
If you use an auto-incrementing ID field as the primary key, use this as your unique field, and in the Where clause to run on a range of records to break into smaller operations.
Filed in MySQL
|
Tagged ALTER, ALTER IGNORE, index, key, long time, MySQL, production environment, Removing, server, Statement, table
|
I recently ran into a situation where a Delete across a large time period was taking an extensive amount of time to run when replicated to the Slave, although the query ran very quickly when running on the Master. On the Master, the query took <2 minutes, while on the Slave, we finally killed the slave replication thread after 4 hours of run time, and issued a Stop Slave. As an added challenge, the table we were updating had 77 million rows and was 10G in size.
After much head scratching and verifying that indexes existed on both Master and Slave, we took a few minutes to compare the query execution plan using EXPLAIN. It was quickly obvious that the Slave was using a different index than the Master when executing the query. We quickly update the table statistics using Analyze Table, and re-ran EXPLAIN. After the Analyze Table, the problem was solved, and after restarting replication using Start Slave, the offending query quickly executed in 2 minutes.
Moral of the Story: Running Analyze Table on larger tables every couple days is probably a good idea.
Filed in MySQL, Replication
|
Tagged Analyze, DELETE, execution plan, EXPLAIN, index, master and slave, query, query execution, Replication, Slave
|
The fastest and easiest way to delete duplicate records is my issuing a very simple command.
alter ignore table [tablename] add unique index `unique_index` ([fieldname])
What this does is create a unique index on the field that you do not want to have any duplicates. The ignore syntax instructs MySQL to not stop and display an error when it hits a duplicate. This is much easier than dumping and reloading a table.
This also will work, but is not as elegant:
delete from [tablename] where fieldname in (select a.[fieldname] from
(select [fieldname] from [tablename] group by [fieldname] having count(*) > 1 ) a )