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 中文
Showing entries 1 to 30 of 52 Next 22 Older Entries

Displaying posts with tag: production (reset)

Here’s my favorite secret MySQL 5.6 feature. What’s yours?
+2 Vote Up -0Vote Down

MySQL 5.6 has a great many new features, including, but certainly not limited to a number of performance improvements. However, besides the widely talked-about features such as InnoDB support for full text search, optimizer, performance schema improvements and

  [Read more...]
Get social and healthy with GlassFish
Employee_Team +0 Vote Up -1Vote Down

Two new stories have been published this week and both of them use GlassFish 3.1 in production. If you haven't seen them before, "Stories" is a blog with production use of GlassFish by small, medium, and large users with user questionnaires describing their experience with the rest of the community.

The first story is PointDebate, a "social network company that stir up, engage and give voice to most diverse opinions". They've been following pretty closely all the recent updates of GlassFish and now run the latest 3.1 version (only a month after it was released). They application is built using Java EE 6 and JSF in particular with RichFaces. The full architecture includes MySQL as well as EHCache and uses JMS to "decouple operations" (an somewhat underutilized architectural

  [Read more...]
Get social and healthy with GlassFish
Employee_Team +0 Vote Up -0Vote Down

Two new stories have been published this week and both of them use GlassFish 3.1 in production. If you haven't seen them before, "Stories" is a blog with production use of GlassFish by small, medium, and large users with user questionnaires describing their experience with the rest of the community.

The first story is PointDebate, a "social network company that stir up, engage and give voice to most diverse opinions". They've been following pretty closely all the recent updates of GlassFish and now run the latest 3.1 version (only a month after it was released). They application is built using Java EE 6 and JSF in particular with RichFaces. The full architecture includes MySQL as well as EHCache and uses JMS to "decouple operations" (an somewhat underutilized

  [Read more...]
MySQL Partitioning – can save you or kill you
+1 Vote Up -0Vote Down

I wanted for a while to write about using MySQL Partitioning for Performance Optimization and I just got a relevant customer case to illustrate it. First you need to understand how partitions work internally. Partitions are on the low level are separate table. This means when you're doing lookup by partitioned key you will look at one (or some of) partitions, however lookups by other keys will need to perform lookup in all partitions and hence can be a lot slower. The gain from updates typically comes from having smaller BTREE on the active partition(s) which allows for a lot better fit. Having potentially fewer level in BTREE is not that significant issue.

So lets see at example:

PLAIN TEXT SQL:
  • CREATE TABLE `tbl` (
  •   `id` bigint(20) UNSIGNED
  •   [Read more...]
    How well does your table fits in innodb buffer pool ?
    +2 Vote Up -0Vote Down

    Understanding how well your tables and indexes fit to buffer pool are often very helpful to understand why some queries are IO bound and others not - it may be because the tables and indexes they are accessing are not in cache, for example being washed away by other queries. MySQL Server does not provide any information of this type, Percona Server however adds number of tables to Information Schema which makes this information possible. It is just few queries away:

    PLAIN TEXT SQL:
  • mysql> SELECT `schema` AS table_schema,innodb_sys_tables.name AS table_name,innodb_sys_indexes.name AS index_name,cnt,dirty,hashed,round(cnt*100/index_size,2) fit_pct   FROM (SELECT index_id,count(*)
  •   [Read more...]
    Thinking about running OPTIMIZE on your Innodb Table ? Stop!
    +4 Vote Up -0Vote Down

    Innodb/XtraDB tables do benefit from being reorganized often. You can get data physically laid out in primary key order as well as get better feel for primary key and index pages and so using less space,
    it is just OPTIMIZE TABLE might not be best way to do it.

    If you're running Innodb Plugin on Percona Server with XtraDB you get benefit of a great new feature - ability to build indexes by sort instead of via insertion. This process can be a lot faster, especially for large indexes which would get inserts in very random order, such as indexes on UUID column or something similar. It also produces a lot better fill factor. The problem is.... OPTIMIZE TABLE for Innodb tables does not get advantage of it for whatever reason.

    Lets take a look at little benchmark I done by running OPTIMIZE for a second time on a table


      [Read more...]
    Getting History of Table Sizes in MySQL
    +2 Vote Up -0Vote Down

    One data point which is very helpful but surprisingly few people have is the history of the table sizes. Projection of data growth is very important component for capacity planning and simply watching the growth of space used on partition is not very helpful.

    Now as MySQL 5.0+ has information schema collecting and keeping this data is very easy:

    PLAIN TEXT SQL:
  • CREATE DATABASE stats;
  • USE stats;
  • CREATE TABLE `tables` (
  • `DAY` date NOT NULL,
  • `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  • `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  • `ENGINE` varchar(64) DEFAULT NULL,
  • `TABLE_ROWS` bigint(21) UNSIGNED DEFAULT NULL,
  • `DATA_LENGTH` bigint(21) UNSIGNED DEFAULT NULL,
  •   [Read more...]
    Replication of MEMORY (HEAP) Tables
    +3 Vote Up -0Vote Down

    Some Applications need to store some transient data which is frequently regenerated and MEMORY table look like a very good match for this sort of tasks. Unfortunately this will bite when you will be looking to add Replication to your environment as MEMORY tables do not play well with replication.

    The reason is very simple - both STATEMENT and ROW replication contain the changes to the data in binary logs. This requires the data to be same on Master and Slave. When you restart the slave you will lose contents of your MEMORY tables and replication will break. STATEMENT replication will often continue to run, with contents of the table just being
    different as there is a little checks whenever statements produce the same results on the slave. ROW replication will
    complain about ROW not exist for UPDATE or DELETE operation.

    So what you



      [Read more...]
    The story of one MySQL Upgrade
    +6 Vote Up -0Vote Down

    I recently worked on upgrading MySQL from one of very early MySQL 5.0 versions to Percona Server 5.1. This was a classical upgrade scenario which can cause surprises. Master and few slaves need to be upgraded. It is a shared database used by tons of applications written by many people over more than 5 years timeframe. It did not have any extensive test suite we could use for validation. As you might guess in such cases some of the original authors have moved on and nobody is exactly sure what application does or does not do with the database. Database is production critical with serious role in serious company so we can't just do a reckless upgrade

    First we needed to do a sanity check on existing replication setup. As we're checking replication consistency down the road we need to

      [Read more...]
    Cache Miss Storm
    +5 Vote Up -0Vote Down

    I worked on the problem recently which showed itself as rather low MySQL load (probably 5% CPU usage and close to zero IO) would spike to have hundreds instances of threads running at the same time, causing intense utilization spike and server very unresponsive for anywhere from half a minute to ten minutes until everything would go back to normal. What was interesting is Same query was taking large portion of slots in PROCESSLIST. I do not just mean query with same fingerprint but literally the same query with same constants.

    What we observed was a cache miss storm – situation which can happen with memcache (as in this case) as well as with query cache. If you have the item which is expensive to generate but which has a lot of hits in the cache you can get into situation when many clients at once will have miss in the cache and will attempt to

      [Read more...]
    Estimating Replication Capacity
    +3 Vote Up -0Vote Down

    It is easy for MySQL replication to become bottleneck when Master server is not seriously loaded and the more cores and hard drives the get the larger the difference becomes, as long as replication
    remains single thread process. At the same time it is a lot easier to optimize your system when your replication runs normally - if you need to add/remove indexes and do other schema changes you probably would be looking at some methods involving replication if you can't take your system down. So here comes the catch in many systems - we find system is in need for optimization when replication can't catch up but yet optimization process we're going to use relays on replication being functional and being able to catch up quickly.

    So the question becomes how can we estimate replication capacity, so we can deal with replication load before slave is unable to catch


      [Read more...]
    Scaling: Consider both Size and Load
    +3 Vote Up -0Vote Down

    So lets imagine you have the server handling 100.000 user accounts. You can see the CPU,IO and Network usage is below 10% of capacity - does it mean you can count on server being able to
    handle 1.000.000 of accounts ? Not really, and there are few reasons why, I'll name most important of them:

    Contention - This is probably the most obvious one. MySQL (and systems in general) do not scale perfectly with numbers of CPUs and number of concurrent requests. Reduced efficiency of CPU cache, Mutex contention and database lock contention all come here. Some of them are preventable and can be reduced by code changes, such as there have been a lot of advanced in scalability of MySQL by improving locking code design, others, such as row level locks would require application changes to allow more concurrent process. The scalability


      [Read more...]
    Color code your performance numbers
    +0 Vote Up -0Vote Down

    When analyzing how good or bad response time is it is not handy to look at the averages, min or max times - something what is easily computed using built in aggregate functions. We most likely would like to see some percentile numbers - 95 percentile or 99 percentile. The problem is computing these in SQL is ugly and resource consuming. There is however a simple way to get similar data, looking at it from the different point of view.

    When we're speaking about application we may not always care about exact value of response time but rather we want to see response time to be within certain range. For example if we define page feels good if response time is below 50ms it is not as important if response was 40ms or 44ms - it is much more important how frequently this goal was reached.

    In fact I prefer to define 2 performance level. One what users

      [Read more...]
    On Good Instrumentation
    +1 Vote Up -0Vote Down

    In so many cases troubleshooting applications I keep thinking how much more efficient things could be going if only there would be a good instrumentation available. Most of applications out there have very little code to help understand what is going on and if it is there it is frequently looking at some metrics which are not very helpful.

    If you look at the system from bird eye view - system needs to process transactions and you want it to successfully complete large number of transactions it gets (this is what called availability) and we want it to serve them with certain response time, which is what is called performance. There could be many variables in environment which change - load, number of concurrent users, database, the way users use the system but in the nutshell all what you really care is having predictable response time within certain range. So if we

      [Read more...]
    Should I buy a Fast SSD or more memory?
    +2 Vote Up -0Vote Down

    While a scale-out solution has traditionally been popular for MySQL, it's interesting to see what room we now have to scale up - cheap memory, fast storage, better power efficiency.  There certainly are a lot of options now - I've been meeting about a customer/week using Fusion-IO cards.  One interesting choice I've seen people make however, is buying an SSD when they still have a lot of pages read/second - I would have preferred to buy memory instead, and use the storage device for writes.

    Here's the benchmark I came up with to confirm if this is the case:

    • Percona-XtraDB-9.1 release
    • Sysbench OLTP workload with 80 million rows (about 18GB worth of data+indexes)
    • XFS Filesystem mounted with nobarrier option.
    • Tests run with:
      • RAID10 with BBU over 8 disks
      • Intel SSD X25-E 32GB
      • FusionIO 320GB MLC
      [Read more...]
    Missleading Innodb message on recovery
    +1 Vote Up -0Vote Down

    As I wrote about 2 years ago the feature of Innodb to store copy of master's position in Slave's Innodb tablespace got broken. There is a lot of discussions at the corresponding bug report while outcome of the fix remained uncertain for me (the bug is market duplicate while the bugs it seems to be duplicate for describe different issues).
    Anyway. The customer came to me today having the following message in the error log after Slave crash while running MySQL 5.1.41 (extra virgin version without XtraDB or Percona Patches)

    InnoDB: In a MySQL replication slave the last master binlog file
    InnoDB: position 0 10000000, file name mysql-bin.005000
    InnoDB: Last MySQL binlog


      [Read more...]
    Why Swapping is bad for MySQL Performance ?
    +1 Vote Up -0Vote Down

    So you get MySQL or other applications using too much memory on the box or OS behaving funny and using more memory for cache and pushing application to the swap. This causes swapping and causes performance problems. This much is obvious. But how bad is it ? Should you count it same as normal Disk IO as the box is having or is it worse than that ?

    Swapping is going to impact your performance more than just normal IO and here are 3 reasons why. If you know more please let me know, for my taste these 3 are bad enough so I have not been looking for more.

    Cache in the Swap File will multiply IO compared to just having less cache. What happens when page in cache is replaced which is swapped out itself ? First you have to find space to swap in the page (we're speaking about memory pressure right?) which means swapping out some page.

      [Read more...]
    Production scripts: sign me up for obfuscation
    +0 Vote Up -0Vote Down
    If there’s one thing that will always make me angry, it’s people that should not be editing my code going and editing my code. If you want to change something on the server and you have sudo privileges please let the real admin know beforehand. I don’t mind people improving processes or scripts but if [...]
    Verify master-master[||-slave] data consistency without locking or downtime
    +1 Vote Up -0Vote Down

    We all knew that we are risking with MMM. Risking, and placing availability as a more important like consistency.  But non of us can risk loosing data forever but we show using it, regarding to our conversations think:  "I can fix my data later on, but I can’t turn back time and prevent the downtime. (Pascal Hofmann@xaprb.com)".

    As I wrote before about staying online, now let me write about how to stay consistent.

    We all know, mmm is not like a key of salvation, but its getting close to it . While MySQL doesn't support multi-master-slave environments from it's source code, we will sleep badly wondering on the safety of our precious databases.

    But its not just about MMM, a few days ago we ran in to a well known InnoDB

      [Read more...]
    Hello, MySQL 6.0, err, something
    +3 Vote Up -1Vote Down

    I'm conflicted about the latest twist of the MySQL release saga, ie the announcement of the 6.0.11 alpha version and the accompanying note that it's the last 6.0 release and will be replaced by the already discussed milestone model. From an engineering point of view, I think this is the right step. I'm not sure about that, because I can't really tell exactly what is the engineering model chosen: trunk-first, then backport, or fix-in-releases, then forward port. I also can't tell whether the milestone model is going to be timeboxed or feature-scoped. Personally, I would prefer to see the former of both alternatives.

    From a customer point of view, I'm even more confused, though much less concerned. Okay, so 6.0 won't become the marketing version number of any

      [Read more...]
    Btw xtrabackup is not only backup..
    +3 Vote Up -0Vote Down

    It is obvious thing for me, but I just figured out it may be not common understanding. Xtrabackup is also can be used (not only can, but we actually use it this way) to clone one slave to another, or just setup new slave from the master. And it is done in almost non-blocking way ( true for InnoDB setups) for cloned server. Here is command

    PLAIN TEXT CODE:
  • innobackupex-1.5.1 --stream=tar /tmp/--slave-info | ssh user@DESTSERVER "tar xfzi - -C /DESTDIR"
  • When it finished on destination server you run

    PLAIN TEXT CODE:
  • innobackupex-1.5.1 --apply-log --use-memory=2G  /DESTDIR
  • And you have ready database directory,

      [Read more...]
    Compression for InnoDB backup
    +0 Vote Up -0Vote Down

    Playing with last version of xtrabackup and compress it I noticed that gzip is unacceptable slow for both compression and decompression operations. Actually Peter wrote about it some time ago, but I wanted to review that data having some new information. In current multi-core word the compression utility should utilize several CPU to speedup operation, and another my requirement was the ability to work with stdin / stdout, so I could do scripting something like: innobackupex --stream | compressor | network_copy.

    My research gave me next list: pigz (parallel gzip),

      [Read more...]
    When would you use SAN with MySQL ?
    +0 Vote Up -0Vote Down

    One question which comes up very often is when one should use SAN with MySQL, which is especially popular among people got used to Oracle or other Enterprise database systems which are quite commonly deployed on SAN.

    My question in such case is always what exactly are you trying to get by using SAN ?

    Depending on the goals the advice could be a lot different.

    Manageability SAN allows you to manage storage easily, compared to directly attach storage. If you have one box which starts to require more IO you can have more resources allocated to it. This is pattern which is somewhat typical for Enterprise managing a lot of relatively small MySQL installations or managed hosting provider. For large MySQL web MySQL installations using Sharding or replication IO needs are typically rather predictable. There are also often nice

      [Read more...]
    SSD, XFS, LVM, fsync, write cache, barrier and lost transactions
    +0 Vote Up -0Vote Down

    We finally managed to get Intel X25-E SSD drive into our lab. I attached it to our Dell PowerEdge R900. The story making it running is worth separate mentioning - along with Intel X25-E I got HighPoint 2300 controller and CentOS 5.2 just could not start with two RAID controllers (Perc/6i and HighPoint 2300). The problem was solved by installing Ubuntu 8.10 which is currently running all this system. Originally I wanted to publish some nice benchmarks where InnoDB on SSD outperforms RAID 10, but recently I faced issue which can make previous results inconsistent.

    In short words using Intel SSD X25-E card with enabled write-cache (which is default and most performance mode) does not warranty storing all InnoDB transactions on permanent storage.
    I am having some déjà vu here, as Peter was rolling this 5 years ago

      [Read more...]
    Understanding Performance Optimization Terminology
    +0 Vote Up -0Vote Down

    There are few terms you need to have a good understanding if you're working with high volume systems. I commonly see these mixed and people not understanding the difference between them.

    Performance - The performance comes down to performing up to users expectations (or expectations of your boss) which drills down to two important metrics - response time also sometimes called latency and throughput also called capacity. Response time applies to even single user and mostly critical in applications when a complex task needs to be performed such as report generation or search. It is just unacceptable if search takes a minute even if system has a capacity running for 100 of them at the same time. The response time is the most important aspect of performance because when we define system

      [Read more...]
    KISS KISS KISS
    +0 Vote Up -0Vote Down

    When I visit customers quite often they tell me about number of creative techniques they heard on the conferences, read on the blogs, forums and Internet articles and they ask me if they should use them. My advice is frequently - do not. It is fun to be creative but creative solutions also means unproven and people who had to become creative with their system often did that because they had no choice. Of course when they came to the bunch of conferences and told their story which resonated across the Internet sticking to the people mind as a good practice.

    There are 2 things you should ask yourself. First is the scale comparable - the recipes from Facebook, YouTube, Yahoo, are not good for like 99.9% of the applications because they are not even remotely close in size and so capacity requirements. Second if this "smart thing" was truly thought out

      [Read more...]
    XtraBackup: Open Source Alternative for Innodb Hot Backup call for ideas
    +0 Vote Up -0Vote Down

    For long time as main backup solution for MySQL on Linux we have been using LVM snapshots. Performance concerns from performance critical envinronment caused us to do LVM performance research which showed horrible results forcing us to look at more performing alternatives.

    Innodb Hot Backup is a good working solution but it is not Open Source and so we can't ensure it will support all XtraDB features - extra undo slots, rollback segments etc.

    After considering all alternatives we decided to develop Percona Xtrabackup tool, which will use same backup approach as InnoDB Hot Backup, that is taking online copy of InnoDB table files and transactional logs. This is

      [Read more...]
    Twitter — Tracking Production Actions?
    +0 Vote Up -0Vote Down

    I don’t want to post the link to this (perhaps, it was left public unintentional?) but here is what I stumbled upon recently. This is a log of production maintenance of IT systems in Perth, Western Australia (as far as I could say):

    Good idea but shouldn’t companies keep this sort of information private?

    Beware of MySQL Data Truncation
    +0 Vote Up -0Vote Down

    Here is nice gotcha which I've seen many times and which can cause just a minefield for many reasons.
    Lets say you had a system storing articles and you use article_id as unsigned int. As the time goes and you see you may get over 4 billions of articles you change the type for article_id to bigint unsigned but forget linked tables.

    PLAIN TEXT SQL:
  • mysql> CREATE TABLE article_comment(article_id int UNSIGNED NOT NULL, comment_id int UNSIGNED NOT NULL, KEY(article_id));
  • Query OK, 0 rows affected (0.11 sec)
  •  
  • mysql> INSERT INTO article_comment VALUES(4300000000,1);
  • Query OK, 1 row affected, 1 warning (0.00 sec)

  •   [Read more...]
    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...]
    Showing entries 1 to 30 of 52 Next 22 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.