Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 61 to 90 of 439 Next 30 Older Entries

Displaying posts with tag: Professional (reset)

SQL_MODE and storage engines
+2 Vote Up -0Vote Down

I was again reminded why setting SQL_MODE is so important in any new MySQL environment. While performing benchmark tests on parallel backup features with a common InnoDB tablespace and per file tablespace, I inadvertently missed an important step in the data migration. The result was the subsequent test that performed data population worked without any issues however there was no data in any InnoDB tables.

These are the steps used in the migration of InnoDB tables from a common tablespace model to a per-table tablespace model.

  • Dump all InnoDB tables
  • Drop all InnoDB tables
  • Shutdown MySQL
  • Change the my.cnf to include innodb-file-per-table
  • Remove the InnoDB ibdata1 tablespace file
  • Remove the InnoDB transactional log files
  • Start MySQL
  • Verify the error log
  • Create and load new InnoDB
  •   [Read more...]
    Why SQL_MODE is essential even when not perfect
    +2 Vote Up -0Vote Down

    In a recent rant on Why I think SQL_MODE is useless…, I wanted to counteract this statement with why we MUST all use SQL_MODE, even with the inherit flaws.

    The fundamental principle of a database is to restore and retrieve data. When I can insert data into the database and then I select this data it is different, this is fundamentally wrong. This is a loss of essential data integrity, something a database should NEVER do.

    SQL_MODE solves the problem of “silent truncation” in most instances, and produces an all important error. As pointed out, the SQL_MODE has several limitations, however the benefits do out way the risks. Quality control on source code can reduce the limitations, but no amount of coding can stop the CRUD that

      [Read more...]
    Colorado MySQL Users Group Presentation
    +1 Vote Up -0Vote Down

    In addition to speaking at RMOUG event in Denver, I also spoke today in Broomfield on “Improving MySQL Performance with Better Indexes”.

    This presentation included details on :

    • Effective examples of capture SQL via application logging and TCP/IP analysis necessary for identifying the best candidates. Slow is not always the best query to start with.
    • All the commands necessary to identify why you may need an index, how to create varying types of indexes, and how to confirm there true effectiveness.
    • How the number of table rows and different storage engines can greatly effect the optimization choice and query performance
    • The presentation shows how to determine/create and verify covering indexes for a single table example, a
      [Read more...]
    MySQL Security Essentials Presentation
    +2 Vote Up -0Vote Down

    Today at the RMOUG Training Days 2012 event I gave an introduction presentation on MySQL Security Essentials covering the following topics:

    • MySQL Security defaults
    • MySQL Security Improvements
    • OS Security
    • User Privileges
    • Data Integrity
    • Installation Practices
    • Auditing Options
    • Better Security
    • Further References

    Download slides for MySQL Security Essentials.

    Exciting upcoming MySQL events
    +3 Vote Up -0Vote Down

    At the IOUC leaders’ summit in San Francisco this week, key leaders from Oracle, Java and MySQL user groups world wide have been meeting. This has included the key Oracle MySQL resources from the community, marketing and product teams. The Java User Groups and MySQL User Groups have been well represented and there has been very welcoming discussion with the IOUC about how we can become active within the Oracle Community.

    There has been key discussions of upcoming and proposed MySQL events including the great outreach by the Oracle MySQL team with existing Open Source conferences this year including Scale, FOSDEM and South East Linuxfest just to name a few.

    You can see the upcoming

      [Read more...]
    Why is searching the manual so hard
    +5 Vote Up -0Vote Down

    As a consultant I often use the MySQL Reference Manual to provide additional information for clients. I am very happy to recognize the quality of the content in the MySQL documentation, but why is the searching of the manual so, so bad?

    While reading the General Security Issues section of the MySQL 5.5 manual, I performed a search for “CREATE USER”. I was not asking for anything abstract, this is an actual SQL command. I was rather horrified to find that the results could not even list the appropriate manual page in the first page of results.

    I am not an expert in full-text search, however it does not take a rocket scientist to realize that a SQL keyword, the title of a page, in the language of the current page (English) and

      [Read more...]
    The most important MySQL Reference Manual page
    +5 Vote Up -0Vote Down

    In my opinion, The Server Option and Variable Reference at http://dev.mysql.com/doc/refman/5.5/en/mysqld-option-tables.html rates as my most important page. This is a consolidated index that enables a drill down to the Server Command Options, System Variables, Startup and replication specifics, as well as important information on default values and differences between versions including point releases.

    However, there is another page not in the actual manual, but at http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-optvar.html which is an Options/Variables reference akin to the Reference Manual, but includes a 5.x version matrix.

    Recently

      [Read more...]
    Binary Log Replayer
    +2 Vote Up -0Vote Down

    When using the replication slave stream, or mysql command line client and mysqlbinlog output from a binary/relay log, all statements are executed in a single thread as quickly as possible.

    I am seeking a tool to simulate the replay of the binary/relay log for a benchmark at a pace that is more representative to original statements. For a simple example, if the Binary Log has 3 transactions in the first second, 2 transactions in the second second, and 5 transactions in the third second, I am wanting to simulate the replay to take roughly 3 seconds, not as fast as possible (which would be sub-second). The tool should try to wait the remainder of a second before processing SQL statements in the incoming stream.

    Does anybody know of a tool that currently provides this type of functionality? Any input appreciated before I create my own.

    Speaking in Denver
    +3 Vote Up -0Vote Down

    Following a heavy schedule in the last month speaking in Tokyo, Beijing, Manila and Auckland it is nice to be on home soil for upcoming speaking. I will be in Denver, Colorado for RMOUG 2012 from February 14-16, 2012 where I will be speaking about the essentials of MySQL security.

    I hope to also organize another presentation in the area for the local MySQL users group. More to follow.

    And a friendly reminder, the annual MySQL conference is on again, same

      [Read more...]
    NoSQL from a RDBMS company
    +1 Vote Up -2Vote Down

    Oracle has announced an open source product for the NoSQL space, the Oracle NoSQL Database. Unlike other popular products including Redis, MongoDB, Cassandra, Voldermort and many others, Oracle has set a benchmark on the features that are truly necessary for highly available data systems.

    Many products in the NoSQL space have told you that consistency is not needed, eventual consistency is good enough, that transactions are not performant enough to include as a feature. No standards exists, there is no common interface for communication, or key features that products aim to meet or better. With this product, features including transactions, replicated data and failover which are built in, are features other open source NoSQL products will need to match.

    Oracle NoSQL Database

      [Read more...]
    Oracle Open World Presentations
    +2 Vote Up -1Vote Down

    At Oracle Open World 2011 there has been a large number of MySQL presentations. You can download the slides of my two presentations at Explaining the MySQL Explain and Improving Performance with Better Indexes.

    You can find additional supporting information for these presentations in the Effective MySQL: Optimizing SQL Statements book.

    The Effective MySQL Book Series
    +3 Vote Up -1Vote Down

    Announced on Sunday at Oracle Open World 2011 is the release of the Effective MySQL book series starting with the “Optimizing SQL Statements” title. The goal of the Effective MySQL series is a highly practical, concise and topic specific reference providing applicable knowledge to use on each page. A feedback comment provided today was “no fluff” which is great comment to re-enforce the practical nature of the series.

    Details on the Effective MySQL Optimizing SQL Statements page include a sample chapter, code

      [Read more...]
    Speaking at Insight Out in Tokyo
    +1 Vote Up -0Vote Down

    I will be joining a stellar class of speakers at the Insight Out DB Showcase in Tokyo from Oct 19-21, 2011. This event covers several RDBMS technologies including MySQL.

    My topics are:

    • Better MySQL Security and Administration
    • Mastering MySQL Performance Tuning
    Reasons to use MySQL 5.5 Presentation
    +1 Vote Up -1Vote Down

    I recently gave a presentation at the New York Effective MySQL Meetup on the new features of, and some of the compelling reasons to upgrade to MySQL 5.5. There are also a number of new MySQL variables that can have a dramatic effect on performance in a highly transactional environment, innodb_buffer_pool_instances and innodb_purge_threads are just two to consider.

    For more information on all the new variables, status, reserved words and benchmarks of new features you can Download Presentation Slides.

    Utilizing multiple indexes per MySQL table join
    +1 Vote Up -1Vote Down

    Historically it was considered that MySQL will generally use only one index per referenced table in a SQL query. In MySQL 5.0 the introduction of merge indexes enabled for certain conditions the possibility to utilize two indexes however this could result in worst performance then creating a better index. In MySQL 5.1 it became possible to control optimization switches with the optimizer_switch system variable.

    However in explaining how to utilize the intersection, union and sort union in queries I discovered that MySQL could use three indexes for one given table.

            Extra: Using union(name,intersect(founded,type)); Using where
    

    I was not aware of this.

    Extra: Using Index
    +1 Vote Up -1Vote Down

    Many people consider this information in the MySQL Query Execution Plan (QEP) to indicate that the referenced table is using an index. It actually means that ONLY the index is used. This can for larger and more frequent queries provide a significant boost.

    In a recent example, adding an index dropped a query from 190ms to 6ms. However adding a better index dropped that 6ms query to 1.2ms. When executed 100s/1000s of times per second these millisecond improvements can have a huge benefit in greater scalability. While people often tune slow running queries, in a well tuned system shaving milliseconds of queries, in this example making 6ms query 80% better is a far greater improvement.

    You can get a detailed explanation of how to identify, create and verify covering indexes from my Percona Live presentation

      [Read more...]
    Why SQL_MODE is important
    +1 Vote Up -0Vote Down

    Today was another example of where a correct SQL_MODE saved customer data from being corrupted. By default, MySQL does not enforce data integrity. It allows what is called silent truncations where the result of what you INSERT or UPDATE does not represent truth. NOTE: I see very few customers ever have this correctly configured, those that do have actually listened to my advice.

    If you do not read any further, your production MySQL environments should be running with at the bare minimum of SQL_MODE=STRICT_ALL_TABLES however I would also advocate for additional SQL_MODE settings.

    For this example, some modified undesirable code attempted to reduce a counter by 1, however because of an UNSIGNED data type and a correctly set SQL_MODE, the application produced an error and data was not corrupted.

    This is what should happen with your SQL.

      [Read more...]
    
    
    	
    Percona Live New York is underway
    +0 Vote Up -0Vote Down

    Today we have a dedicated MySQL conference in New York with Percona Live. It is great to see an overflowing room in the opening keynote. With over 20 speakers and 4 dedicated tracks there is a lot of content for attendees.

    With all the confusion over conference ownership since the Oracle acquisition I applaud Percona for taking an initiative, first in San Francisco and now here in New York. Also announced today is the next Percona Live in London which is great for the MySQL ecosystem in Europe.

    Another reason to avoid RDS
    +1 Vote Up -0Vote Down

    My list of reasons for never using or recommending Amazon’s MySQL RDS service grows every time I experience problems with customers. This was an interesting and still unresolved issue.

    ERROR 126 (HY000): Incorrect key file for table '/rdsdbdata/tmp/#sql_5b7_1.MYI'; try to repair it
    

    You may see this is a MyISAM table. The MySQL database is version 5.5, all InnoDB tables and is very small 100MB in total size.
    What is happening is that MySQL is generating a temporary table, and this table is being written to disk. I am unable to change the code to improve the query causing this disk I/O.

    What I can not understand and have no ability to diagnose is why this error occurs sometimes and generally when the database is under additional system load. With RDS you have no visibility of the server running the production database. While you have SQL


      [Read more...]
    query_cache_size=0 is not enough
    +2 Vote Up -0Vote Down

    Last week at the OUG Harmony conference thanks to Dimitri Kravtchuk I learned that setting query_cache_size=0 does not disable and remove locking from the Query Cache. You actually need to also set query_cache_type=0. This appears to been a bug, seen in the presently still open MySQL bugs database entry #38511.

    My recommendation to customers now is to set both variables on all existing MySQL versions if you are not using the MySQL Query Cache.

    Thanks to the Performance Schema in MySQL 5.5 for uncovering this. More information in Dimitri’s detailed post at MySQL Performance: Using Performance Schema

    Details of all MySQL presentations at

      [Read more...]
    Speaking at Percona Live New York
    +0 Vote Up -0Vote Down

    As the top MySQL expert in New York it is great to join the team at Percona for the upcoming Percona Live in New York City on May 26th. As an invited speaker I am joining a select list of expert speakers including Harrison Fisk from Facebook, Kurt von Finck from Monty Program and Monty Taylor from the core Drizzle team.

    My presentation will be on Improving Performance with Better Indexes where I will not only show how to apply indexes to improve query performance, but how to apply better indexes and provide even greater performance gains via a great technique known as a covering index.

    Upcoming MySQL presentation in New York
    +1 Vote Up -1Vote Down

    On Tuesday I will be speaking in New York at the Effective MySQL Meetup group where I will be giving the presentation “MySQL Idiosyncrasies That Bite”. For more information and to register, check out the Meetup Event. There are just 10 seats left.

    To promote the upcoming Percona Live event in New York, for attendees to the Meetup there will be a draw for a FREE ticket to the May 26th event.

    Effective MySQL New York is the only MySQL group now operating in New York. Please join our group for the latest information and events for the MySQL community.

    Basic scalability principles to avert downtime
    +1 Vote Up -1Vote Down

    In the press in the last two days has been the reported outage of Amazon Web Services Elastic Compute Cloud (EC2) in just one North Virginia data center. This has affected many large website includes FourSquare, Hootsuite, Reddit and Quora. A detailed list can be found at ec2disabled.com.

    For these popular websites was this avoidable? Absolutely.

    Basic scalability principles if deployed in these systems architecture would have averted the significant downtime regardless of your development stack. While I work primarily in MySQL these principles are not new, nor are they complicated, however they are fundamental concepts in scalability that apply to any technology

      [Read more...]
    Use Replication for backups? Are you schemas consistent?
    +3 Vote Up -0Vote Down

    Many people have a master/slave MySQL environment of various different topologies, and many use the slave as a backup.
    Is your slave schema identical to your production schema? As long as an SQL statements completes without an error, your slave schema can differ. Common examples are different indexes or storage engines for example. However if you use the slave as backup, you want to ensure when you recover, you are recovering a production environment, not a slave environment.

    While the changes may be small, the can lead to different results. For a client I found that the default value of a price field was 10.00 in one schema and 0.00 in another. Not withstanding using defaults for important fields and not defining in an INSERT is a different issue, it highlighted the different schemas can easily exist.

    My tool of choice is


      [Read more...]
    I vote for Planet MySQL moderation
    +4 Vote Up -7Vote Down

    How this happens or who does it is obviously a larger and more complex conversation however it is better then involving innocent animals.

    How is it that trivial $#*! gets voted and has a string of comments I can not explain, however Planet MySQL should have practical MySQL related content. I would vote for the front page and RSS feed to show only moderated content, and content that did not pass the cut (and there is a lot of this) can be available on a less important page if necessary.

    Rather then complaining like many open source communities, let us propose a way to improve the system we use.

    Checked that MySQL backup log lately?
    +4 Vote Up -1Vote Down

    Running a MySQL backup and ensuring it completed successfully and backup files exist is not enough. In my B&R Quiz from Checked your MySQL recovery process recently? one important step is “Do you review your backup logs EVERY SINGLE day or have tested backup monitoring in place?”

    This is what I found when reviewing a backup log for a client today.

    mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES
    mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES
    

    The backup script was completing, backup files were in place (and are listed in the log file) however these

      [Read more...]
    How to block DROP statements on the slave
    +3 Vote Up -0Vote Down

    This was a request yesterday on a mailing list about how to I block DROP statements from running on a slave. There were responses including MySQL could not do this, or try other replication technologies however I thought I would share how you can easily do this on a slave.

    You simply deny access on the slave. It is as simple as changing the MySQL privileges for the user(s) in question to remove DROP or ALTER capabilities.

    Of course this will break replication rather then simply ignore the statement however I suspect that is the intended goal anyway. The first statement that uses the table will fail regardless. With appropriate monitoring of replication you should know in about 15 seconds. (Hint: If you don’t monitor replication or monitor it frequently, DO SO NOW!)

    These leads to the question, what if the statements are legitimate? There

      [Read more...]
    MySQL conference schedule
    +3 Vote Up -0Vote Down

    I am one of the crazy individuals(*) that will be speaking at both the regular O’Reilly MySQL Conference and the IOUG Collaborate conference both being held in the second week of April. My 4 presentations are:

      [Read more...]
    Upcoming NY Presentation – How Better Indexes Save You Money
    +3 Vote Up -0Vote Down

    For all those in New York this is an upcoming MySQL presentation held in conjunction with our colleagues at General Assembly on March 22nd 2011.

    This presentation “How Better Indexes Save You Money” will be discussing how one simple technique can result in huge MySQL performance improvements and with zero code changes necessary. Many people think they know indexes, however MySQL and MySQL Storage Engines have some specifics that differ from more traditional RDBMS products. Learn some of the key analysis and verification techniques and be able to see immediate potential results in performance.

    You can find more details at Meetup.com EffectiveMySQL. This new group is all about highly technical MySQL related content “no fluff, just stuff”.

    Part 2 – Simple lessons in improving scalability
    +3 Vote Up -0Vote Down

    Given the popular response from my first lesson in improving scalability where I detailed simple ways to eliminate unnecessary SQL, let me share another common bottleneck with MySQL scalability that can be instantly overcome.

    Analyzing the writes that occur on a system can expose obvious potential bottlenecks. The MySQL Binary Log is a wealth of information that can be mined. Simple DML Counts per table can be achieved by a single line command.

    Let’s look at the following example output of a production system:

    mysqlbinlog /path/to/mysql-bin.000999 |  \
       grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter"  | \
       cut -c1-100 | tr
      [Read more...]
    Previous 30 Newer Entries Showing entries 61 to 90 of 439 Next 30 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.