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 31 to 53

Displaying posts with tag: production (reset)

Should you move from MyISAM to Innodb ?
+1 Vote Up -0Vote Down

There is significant portion of customers which are still using MyISAM when they come to us, so one of the big questions is when it is feasible to move to Innodb and when staying on MyISAM is preferred ?

I generally prefer to see Innodb as the main storage engine because it makes life much simpler in the end for most users - you do not get to deal with recovering tables on the crash or partially executed statements. Table locks is no more problem, hot backups are easy, though there are some important things which we have to consider on case by case basics before recommending the move.

Is MyISAM used as default or as a choice ? This is the most important question to ask upfront. Sometimes MyISAM is there just because it is default, in other cases this is deliberate choice with system being optimized to deal with MyISAM limits,

  [Read more...]
XtraDB storage engine release 1.0.2-2 (New Year edition)
+0 Vote Up -0Vote Down

Today we announce release 1.0.2-2 of our XtraDB storage engine.

Here is a list of enhancements:

  • split-buffer_pool_mutex

The patch splits global InnoDB buffer_pool mutex into several and eliminates waitings on flush IO and mutex when there is no enough free buffers. It helps if you have performance drops when data does not fit in memory.

InnoDB has a concurrent transaction limit of 1024 because in the standard InnoDB the number of undo slots is fixed value. This patch expands the maximum number of undo slots to 4072 and allows better utilizing modern hardware. (Thank

  [Read more...]
Goal driven performance optimization
+0 Vote Up -0Vote Down

When your goal is to optimize application performance it is very important to understand what goal do you really have. If you do not have a good understanding of the goal your performance optimization effort may well still bring its results but you may waste a lot of time before you reach same results as you would reach much sooner with focused approach.

The time is critical for many performance optimization tasks not only because of labor associated expenses but also because of the suffering - slow web site means your marketing budget is wasted, customer not completing purchases, users are leaving to competitors, all of this making the time truly critical matter.

So what can be the goal ? Generally I see there are 2 types of goals seen in practice. One is capacity goal this is when the system is generally overloaded so

  [Read more...]
Recovering CREATE TABLE statement from .frm file
+0 Vote Up -0Vote Down

So lets say you have .frm file for the table and you need to recover CREATE TABLE statement for this table. In particular when we do Innodb Recovery we often get .frm files and some mess in the Innodb tablespace from which we have to get data from. Of course we could relay on old backups (and we do ask for them for a different reason anyway) but there is never guaranty there were no schema changes in between.

So how to recover CREATE TABLE from .frm file ?

Recovering from .frm for Innodb Table

If we simply copy .frm file back to the database we will see the following MySQL creative error message:

PLAIN TEXT SQL:
  •   [Read more...]
    Announcing Percona XtraDB Storage Engine: a Drop-in Replacement for Standard InnoDB
    +1 Vote Up -0Vote Down

    Today we officially announce our new storage engine, "Percona XtraDB", which is based on the InnoDB storage engine. It's 100% backwards-compatible with standard InnoDB, so you can use it as a drop-in replacement in your current environment. It is designed to scale better on modern hardware, and includes a variety of other features useful in high performance environments.

    Percona XtraDB includes all of InnoDB's ACID-compliant design and advanced MVCC architecture, and adds features, more tunability, more metrics, more scalability on many cores, and better memory usage. We choose features and fixes based on customer requests and on our best judgment of real-world needs. We have not included all the InnoDB patches available. For example Google's well-known InnoDB patch set is

      [Read more...]
    SHOW OPEN TABLES - what is in your table cache
    +0 Vote Up -0Vote Down

    One command, which few people realize exists is SHOW OPEN TABLES - it allows you to examine what tables do you have open right now:

    PLAIN TEXT SQL:
  • mysql> SHOW open TABLES FROM test;
  • +----------+-------+--------+-------------+
  • | DATABASE | TABLE | In_use | Name_locked |
  • +----------+-------+--------+-------------+
  • | test     | a     |      3 |           0 |
  • +----------+-------+--------+-------------+
  • 1 row IN SET (0.00 sec)
  • This command lists all non-temporary tables in the table-cache, showing each of them only once (even if table is opened more than

      [Read more...]
    Reading MySQL Enterprise future…
    +0 Vote Up -0Vote Down

    Well, actually it is not reading future, but just mysql-5.1.30.rhel4.spec file from MySQL RedHat 4 SRPM.

    I found there few MySQL Enterprise Editions, namely:

    MySQL Enterprise Server - Advanced Edition
    MySQL Enterprise Server - Pro Edition
    MySQL Enterprise Server - Classic Edition

    What is difference ? Let's see.

    For MySQL Enterprise Server - Advanced Edition:
    %define PARTITION_BUILD 1
    %define INNODB_BUILD 1

    For MySQL Enterprise Server - Pro Edition:
    %define PARTITION_BUILD 0
    %define INNODB_BUILD 1
    So this one comes without Partitions.

    And MySQL Enterprise Server - Classic Edition
    %define PARTITION_BUILD 1
    %define INNODB_BUILD 0
    Which I am finding most interesting ... with Partitioning but without InnoDB ?


    Entry posted by Vadim |











      [Read more...]
    When to use Hardware upgrade instead of Software Optimization
    +0 Vote Up -0Vote Down

    One typical question which frequently pops up is whenever it is better to use hardware upgrade or optimize software more. I already wrote about it, for example here.

    Today I'll look at the same topic from the consultants view. When consultant should suggest hardware upgrade and when it is not in a simple checklist form.

    How good is hardware ? Sometimes people use so crappy hardware it would be much cheaper for them to upgrade before purchasing much of professional services. Though in some cases people like their system to be optimal and so they want to run it on some old box even if it costs them more to optimize it. It may be valid choice allowing to take a hardware

      [Read more...]
    Computing 95 percentile in MySQL
    +0 Vote Up -0Vote Down

    When doing performance analyzes you often would want to see 95 percentile, 99 percentile and similar values. The "average" is the evil of performance optimization and often as helpful as "average patient temperature in the hospital".

    Lets set you have 10000 page views or queries and have average response time of 1 second. What does it mean ? Really nothing - may be one page view was 10000 seconds and the rest was in low milliseconds or may be you had every single page view taking 1 second, which are completely different.

    You also do not really care about average performance - the goal of good user experience is majority of users to have good experience and average is not a good fit here. Defining your response time goal in 95 or 99 percentile is much better. Say you say 99 percentile response time should be one second, this means only 1 percent of

      [Read more...]
    MySQL for Hosting Providers - how do they manage ?
    +0 Vote Up -0Vote Down

    Working with number of hosting providers I always wonder how do they manage to keep things up given MySQL gives you so little ways to really restrict how much resources single user can consume. I have written over a year ago about 10+ ways to crash or overload MySQL and since that people have come to me and suggested more ways to do the same.

    This is huge hole in MySQL design, thinking little about users isolations and resource quotas and interesting enough I have not seen significant changes in fresh our MySQL 5.1 GA or even something major on the roadmap for future MySQL versions. May be Drizzle will give it a thought ? This surely would help adoption by (especially low end) Hosting Providers and remember this exactly where a lot of kids start to

      [Read more...]
    Using Multiple Key Caches for MyISAM Scalability
    +0 Vote Up -0Vote Down

    I have written before - MyISAM Does Not Scale, or it does quite well - two main things stopping you is table locks and global mutex on the KeyCache.

    Table Locks are not the issue for Read Only workload and write intensive workloads can be dealt with by using with many tables but Key Cache Mutex will still hunt you. If you aware of MySQL history you may think Key Cache scalability was fixed with new Key Cache in MySQL 4.1, and indeed previously it did not even scale with one CPU as global lock was held during IO duration, In MySQL 4.1 the lock is held only when key block (1KB to 4KB) is being copied from Key Cache to thread local buffer, which is terrible contention spot in particular on systems with many CPU cores.

    Happily

      [Read more...]
    Using MySQL 5.1 in production for over a year
    +0 Vote Up -0Vote Down



    Lenz has just published an interview to Adam Donnison, Senior Web Developer in the MySQL web team. Under his watch, the site at mysql.com has been powered by MySQL 5.1 for one and half years.
    In production! Talk about eating your own dog food!
    In addition to the database server, Adam has spearheaded the test of Enterprise tools and the Query Analyzer. Excellent example, Adam!
    Using INFORMATION_SCHEMA instead of shell scripting
    +0 Vote Up -0Vote Down

    INFORMATION_SCHEMA, in particular by favorite TABLES table is not only helpful to understand tables you have on the system, but I have also found it to be very helpful as a scripting language for variety of database administration tasks. It can be more straightforward compared to using shell or Perl when the operation is database specific.

    For example if you would like to MySQLDump only Innodb table in one file per database you can do the following:

    PLAIN TEXT SQL:
  • mysql> SELECT concat("mysqldump ",table_schema," ",table_name, ">> ",table_schema,".sql") FROM TABLES WHERE engine='innodb' LIMIT 5;
  • +------------------------------------------------------------------------------+
  • |
  •   [Read more...]
    Drilling down to the source of the problem
    +0 Vote Up -0Vote Down

    I had an interesting tuning case few days ago. The system serving high traffic using Innodb tables would be stalling every so often causing even very simple queries both reads and writes taking long time to complete, with progress almost paused (dropping from thousands to tens of queries per second).

    On the surface the problem looked simple - in the processlist every so often you would see a lot of queries, mostly selects taking 10+ seconds while at the same time there was no significant iowait, neither high CPU usage. Closer examination showed there were hundreds of queries stuck in the innodb queue, with innodb_thread_concurrency set to 8

    Happily enough innodb_thread_concurrency is the variable which can be set online so it is easy to try a few different values and see what works best. In this case we

      [Read more...]
    Why audit logging with triggers in MySQL is bad for replication
    +0 Vote Up -0Vote Down

    Recently I was tasked with investigating slippage between master and slave in a standard replication setup.

    The client was using Maatkit's mk-table-checksum to check his slave data was indeed a fair copy of that of the master.

    mk-table-checksum --algorithm=BIT_XOR h=hostname.local,u=root,p=xxx --replicate=checksum.checksum --emptyrepltbl --chunksize=500000 --databases mydb --sleep 1

    He could then examine the checksum.checksum table and see all was well, however there were various tables with different crc values.

    PLAIN TEXT SQL:
  • db: mydb
  • tbl: Foo_History
  • chunk: 0
  • boundaries: 1=1
  • this_crc:
  •   [Read more...]
    Fighting MySQL Replication Lag
    +0 Vote Up -0Vote Down

    The problem of MySQL Replication unable to catch up is quite common in MySQL world and in fact I already wrote about it. There are many aspects of managing mysql replication lag such as using proper hardware and configuring it properly. In this post I will just look at couple of query design mistakes which result in low hanging fruit troubleshooting MySQL Replication Lag

    First fact you absolutely need to remember is MySQL Replication is single threaded, which means if you have any long running write query it clogs replication stream and small and fast updates which go after it in MySQL binary log can't proceed. It is either more than than just about queries - if you're using explicit transactions all updates from the transactions are buffered together and

      [Read more...]
    Finding what Created_tmp_disk_tables with log_slow_filter
    +0 Vote Up -0Vote Down

    Whilst working with a client recently I noticed a large number of temporary tables being created on disk.

    show global status like 'Created_tmp%'

    | Created_tmp_disk_tables | 91970 |
    | Created_tmp_files | 19624 |
    | Created_tmp_tables | 1617031 |

    Looking at a 60 second sample showed there was a steady flow of queries creating disk based tables.

    mysqladmin ext -ri60

    | Created_tmp_disk_tables | 74 |
    | Created_tmp_files | 3 |
    | Created_tmp_tables | 357 |

    Luckily this client was running the Percona





      [Read more...]
    When is it a time to upgrade memory ?
    +0 Vote Up -0Vote Down

    Quite commonly I get a question similar to this - "My Innodb Buffer Pool is already 90% full, should I be thinking about upgrading memory already?"
    This is a wrong way to put the question. Unless you have very small database (read as database which is less than innodb_buffer_pool_size) You will have all buffer pool busy sooner or later.
    How to figure out if it is time for upgrade when ?

    Look at number of misses per second Check number of innodb file reads and writes per second and see how high are these. Decent drive can do some 150-200 IOs/sec this is how you can guess about the load. If you just get couple of reads per second your working set fits to the memory very well if it is hundreds you're likely to be IO bound or becoming one.

    Look at Iowait iostat -dx 10 will



      [Read more...]
    Unused indexes by single query
    +0 Vote Up -0Vote Down

    Usually unused indexes are devil, they waste diskspace, cache, they make INSERT / DELETE / UPDATE operations slower and what makes them worse - it is hard to find them.

    But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query

    PLAIN TEXT SQL:
  • SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
  • FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics IS
  • ON (s.TABLE_SCHEMA = IS.TABLE_SCHEMA AND
  • s.TABLE_NAME=IS.TABLE_NAME AND
  • s.INDEX_NAME=IS.INDEX_NAME)
  • WHERE IS.TABLE_SCHEMA IS NULL;
  • PLAIN TEXT  [Read more...]
    What Bugs makes you to recomend upgrade most frequently ?
    +0 Vote Up -0Vote Down

    What bug makes you to recommend upgrading most frequently ? For me it is this bug which makes it quite painful to automate various replication tasks.

    It is not the most critical bug by far but this makes it worse - critical bugs would usually cause upgrades already or were worked around while such stuff as causing things like "sometimes my slave clone script does not work" may hang on for years.


    Entry posted by peter | One comment

    Add to:

      [Read more...]
    How quickly you should expect to see bugs fixed
    +0 Vote Up -0Vote Down

    Over a year ago I wrote about pretty nasty Innodb Recovery Bug. I ran in the same situation again (different system, different customer) and went to see the status of the bug... and it is still open.

    You may thing it is minor issue but in fact with large buffer pool this bug makes database virtually unrecoverable (if 10% of progress in 2hours qualifies as that). It is especially nasty as it is quite hard to predict. Both customers had MySQL crash recovery happening in reasonable time... most of the times until they run into this problem.

    So what is the point ? Have modest expectations about when your favorite MySQL bugs are fixed (This is actually Innodb one, so

      [Read more...]
    Basic requirements of production database environments
    +0 Vote Up -0Vote Down

    I just need to get some basics off of my chest here, it’s by no means a full list but it’s the most basic list I can think of to start with, and it’s basic because I am surprised by some of the slop I’ve seen in production environments.

    1. Highly available server clusters - this is different than load balancing cluster, if confused see here.

    2. Disaster recovery

    -> this means daily,weekly,monthly backups as well as off site backups, and tertiary backups as well as a plan to get those backups imported and running in production as fast as possible. Backups should have consistency checking when they are created.

    3. Security

    -> perimeter on the network, VLAN’d databases from the web/app servers, firewall, ACLs, etc

    -> system level: strong passwords on

      [Read more...]
    MySQL Community vs Enterprise tension
    +0 Vote Up -0Vote Down

    I probably don't spend quite enough time following progress around MySQL considering how critical the product is to us. I'd like to consider it part of the infrastructure in a way I treat Red Hat Enterprise Linux, ie something I can trust to make good progress and follow up on a quarterly basis. Naturally we have people who watch both much more closely, but my time simply should, and pretty much is, spent doing something else.

    However, it seems MySQL really demands a bit more attention right now. Today I went and read Jeremy Cole's opinion about MySQL Community (a failure), and I have to say I agree on many of the points. MySQL simply has not yet found a model that works as well as that of Red Hat's Fedora vs

      [Read more...]
    Previous 30 Newer Entries Showing entries 31 to 53

    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.