Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 53 10 Older Entries

Displaying posts with tag: production (reset)

Using MySQL triggers and views in Amazon RDS
+1 Vote Up -0Vote Down

I recently had an opportunity to migrate a customer from a physical server into Amazon’s RDS environment. In this particular case the customers’ platform makes extensive use of MySQL triggers and views.  I came across two significant issues that prevented me from following Amazon’s documentation, which basically states “use mysqldump” but doesn’t call out a specific method of dealing with MySQL triggers and views.

Amazon Relational Database Service (Amazon RDS) is a great platform if you’re looking for complete hands-off management of your MySQL environment, but comes at a cost in the area of flexibility, i.e. you don’t have SUPER privilege and this brings up additional challenges.

  • You need to ensure you set log_bin_trust_function_creators=1 ( by default this is off, 0).
  • You need to
  •   [Read more...]
    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...]
    Showing entries 1 to 10 of 53 10 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.