Skip to content

Tag Archives: Binary

MySQL Collate and Case Sensitivity – Flexible Compare

Tuesday, August 31, 2010

In a typical MySQL installation, the default character set and collation are latin1 and latin1_swedish_ci.  This means that all string comparisons will be performed in a case insensitive manner.  So, ‘Search String’='search string’.  This is great for most WHERE expressions, however there are occasions where performing a case-sensitive search is required.  Luckily MySQL provides the collate clause which allows you to override whatever the default collation is for a comparison.   As an example :

select * from mytable where first_name collate latin1_general_cs=’damon’

This will only return rows will the first_name matches damon exactly. Damon will not match.

You can also use latin1_bin which performs an exact binary match.

If you are unsure of a field or strings collation, you can use the COLLATION(str) function to return the collation of the string argument.

Filed in MySQL | Also tagged , , , , , , , , , | Comments (0)

Top My.CNF Optimizations for Innodb

Friday, August 20, 2010

These are the four parameters which will have the most affect on the performance of MySQL 5.1x and Innodb.   If you are suffering from poor performance, try changing the following settings.  Remember, each individual situation will vary, and in many cases, the actual design of your queries will have more to do with your overall performance than any system tuning tricks.  The best tool I have found to capture, review, and analyse query performance for MySQL is Jet Profiler for MySQL.  If you need help optimizing your queries, let us know.

innodb_buffer_pool_size =8G
Set this to ~80% of free server memory.  If you have a dedicated MySQL server with 10G, set to 8G

innodb_flush_log_at_trx_commit =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure

sync_binlog =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure

innodb_flush_method=O_DIRECT
On many systems, this will provide a performance improvement.  However, this can actually have a negative affect, so make sure you test appropriately.
Related articles
Filed in MySQL, Optimization | Also tagged , , , , , , , , , , , , , , , , | Comments (0)

MySQL – Disabling Binary Logging for INSERT and UPDATE

Wednesday, June 2, 2010

I have run into many situations where there would be a benefit to not replicate a large INSERT or UPDATE from the MASTER to SLAVE in order to not block replication for a long period of time. Luckily MySQL provides a means to disable binary logging for your current session. Simply execute SET SQL_BIN_LOG=0 before the SQL Statement that you do not want logged to the BINLOG. This is a session variable, meaning it will be re-enabled when you close the session, or you can set it back to 1.

In order to keep the Slave in-sync, you will need to execute the same query on the Slave. You can also use mk-table-sync from the Maatkit toolkit to re-sync your table data at a later time.

Filed in MySQL, Replication | Also tagged , , , , , , , , , , , , , | Comments (1)