MySQL Community Server 5.5.23 has been released

[Hery Ramilison]

Dear MySQL users,

MySQL 5.5.23 is a new version of the 5.5 production release of the
world's most popular open source database. MySQL 5.5.23 is recommended
for use on production systems.

MySQL 5.5 includes several high-impact enhancements to improve the
performance and scalability of the MySQL Database, taking advantage of
the latest multi-CPU and multi-core hardware and operating systems. In
addition, with release 5.5, InnoDB is now the default storage engine for
the MySQL Database, delivering ACID transactions, referential integrity
and crash recovery by default.

MySQL 5.5 also provides a number of additional enhancements including:

- Significantly improved performance on Windows, with various
  Windows specific features and improvements
- Higher availability, with new semi-synchronous replication and
  Replication Heart Beat
- Improved usability, with Improved index and table partitioning,
  SIGNAL/RESIGNAL support and enhanced diagnostics, including a new
  Performance Schema monitoring capability.

For a more complete look at what's new in MySQL 5.5, please see the
following resources:

MySQL 5.5 is GA, Interview with Tomas Ulin:
http://dev.mysql.com/tech-resources/interviews/thomas-ulin-mysql-55.html

Documentation:
http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html

Whitepaper: What's New in MySQL 5.5:
http://dev.mysql.com/why-mysql/white-papers/mysql-wp-whatsnew-mysql-55.php

If you are running a MySQL production level system, we would like to
direct your attention to MySQL Enterprise Edition, which includes the
most comprehensive set of MySQL production, backup, monitoring,
modeling, development, and administration tools so businesses can
achieve the highest levels of MySQL performance, security and uptime.
http://mysql.com/products/enterprise/

For information on installing MySQL 5.5.23 on new servers, please see
the MySQL installation documentation at
http://dev.mysql.com/doc/refman/5.5/en/installing.html

For upgrading from previous MySQL releases, please see the important
upgrade considerations at:
http://dev.mysql.com/doc/refman/5.5/en/upgrading.html

MySQL Database 5.5.23 is available in source and binary form for a
number of platforms from our download pages at:
http://dev.mysql.com/downloads/mysql/

Not all mirror sites may be up to date at this point in time, so if you
can't find this version on some mirror, please try again later or choose
another download site.

We welcome and appreciate your feedback, bug reports, bug fixes,
patches, etc.:
http://forge.mysql.com/wiki/Contributing

The following section lists the changes in the MySQL source code since
the previous released version of MySQL 5.5. It may also be viewed
online at:
http://dev.mysql.com/doc/refman/5.5/en/news-5-5-23.html

Enjoy!


Changes in MySQL 5.5.23 (12 April 2012)

Functionality Added or Changed

  * The MySQL-shared-compat RPM package enables users of Red
Hat-privided mysql-*-5.1 RPM packages to migrate to
Oracle-provided MySQL-*-5.5 packages. MySQL-shared-compat now
replaces the Red Hat mysql-libs package by replacing
libmysqlclient.so files of the latter package, thus satisfying
dependencies of other packages on mysql-libs. This change
affects only users of Red Hat (or Red Hat-compatible) RPM
packages. Nothing is different for users of Oracle RPM
packages. (Bug #13867506)

Bugs Fixed

  * Security Fix: Bug #59533 was fixed.

  * Performance: Partitioning: InnoDB Storage Engine: The
statistics used by the optimizer for queries against
partitioned InnoDB tables were based only on the first
partition of each such table, leading to use of the wrong
execution plan. (Bug #13694811)
References: This bug was introduced by Bug #11756867.

  * Performance: InnoDB Storage Engine: Improved the performance
of the DROP TABLE statement for InnoDB tables, especially on
systems with a large buffer pool. The fix speeds up the
processing for freeing entries in the adaptive hash index.
(Bug #13704145, Bug #64284)

  * InnoDB Storage Engine: Deleting a huge amount of data from
InnoDB tables within a short time could cause the purge
operation that flushes data from the buffer pool to stall. If
this issue occurs, restart the server to work around it. This
issue is only likely to occur on 32-bit platforms. (Bug
#13847885)

  * InnoDB Storage Engine: If the server crashed during a TRUNCATE
TABLE or CREATE INDEX statement for an InnoDB table, or a DROP
DATABASE statement for a database containing InnoDB tables, an
index could be corrupted, causing an error message when
accessing the table after restart:
 InnoDB: Error: trying to load index index_name for table
 InnoDB: table_name but the index tree has been freed!
In MySQL 5.1, this fix applies to the InnoDB Plugin, but not
the built-in InnoDB storage engine. (Bug #12861864, Bug
#11766019)

  * InnoDB S

Re: Commit commands with SELECT

[Stephen Tu]

Wrapping even just selects around a transaction absolutely matters,
depending if you care about isolation.

Consider the following two clients running on the same mysql instance, w/
--transaction_isolation=serializable. Suppose we have the following innodb
table:
CREATE TABLE FOO (i INTEGER, j INTEGER);

Client 1:
SELECT * FROM foo WHERE i = 0;
SELECT * FROM foo WHERE i = 0;

Client 2:
UPDATE foo SET j = 1 WHERE i = 0;

Suppose the table starts out with a single tuple (0, 0). Now, if client 1
and client 2 are running at the same time, wrapping client 1's select
statements with a BEGIN/COMMIT removes the possibility of the following
interleaving:

C1: SELECT * FROM foo WHERE i = 0;
C2: UPDATE foo SET j = 1 WHERE i = 0;
C1: SELECT * FROM foo WHERE i = 0;

Without the BEGIN/COMMIT, the interleaving above is completely valid. Now
to answer your questions.

> On 09.04.2012, at 11:38, Rozeboom, Kay [DAS] wrote:
> >
> >> We have an application with blocks of code that begin with setting
> autocommit off, and end with a commit.  The code in between does only
> selects, no updating.
> >>
> >> 1)  Am I correct in thinking that the autocommit and commit
> statements don't really accomplish anything useful?
>

No, you need to reason about whether or not the select statements need to
run in isolation for correctness (like the above example).


> >> 2)  If the autocommit and commit statements are unneeded, do they
> add enough additional overhead that I should be concerned about them?
>

I don't think you gain any overhead by using explicit transactions. For
instance, a single select statement (I believe) is really equivalent to

BEGIN; SELECT ...; COMMIT;

However, you do incur overhead in the sense that the longer your
transaction block is, the more time you spend holding (read) locks, and
thus excluding writers.

Hope that helps,
--
Stephen Tu

Re: Commit commands with SELECT

[Luis Motta Campos]

Hello 

COMMIT statements may or may not force the database to call fflush() to flush 
your double-write to disk. This may or may not affect your performance, 
depending on your scale, traffic, and how much you're trying to squeeze your 
hardware. If you're working on the borderline like I am, benchmark, benchmark, 
benchmark.

My 0.02€.

Kind regards,
--
Luis Motta Campos
is a DBA, Foodie, and Photographer

On 9 Apr 2012, at 20:47, Karen Abgarian wrote:

> I vote 1) yes 2) no
> 
> It could be result of the app developer's convenience to just wrap anything 
> they submit to the database in a transaction.   Selects are not transaction 
> but autocommit/commit do no harm.   That might be the thinking. 
> 
> 
> On 09.04.2012, at 11:38, Rozeboom, Kay [DAS] wrote:
> 
>> We have an application with blocks of code that begin with setting 
>> autocommit off, and end with a commit.  The code in between does only 
>> selects, no updating.
>> 
>> 1)  Am I correct in thinking that the autocommit and commit statements 
>> don't really accomplish anything useful?
>> 
>> 2)  If the autocommit and commit statements are unneeded, do they add 
>> enough additional overhead that I should be concerned about them?
>> 
>> Kay Rozeboom
>> Information Technology Enterprise
>> Iowa Department of Administrative Services
>> Telephone: 515.281.6139   Fax: 515.281.6137
>> Email:  kay.rozeb...@iowa.gov
>> 
>> 
>> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
> 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: FYI: how to prevent mysql from oom-killer

[Reindl Harald]

hm - my servers generally have no swap or better said
only a 500 MB swap-file, they are all virtual machines
in a ESXi cluster

you really do not want a VM swapping
this leads in unusaeable performance

in my case the problem was another process eating up all memory
by user-mistake and my hint is to make sure in this case that
this process get killed and never mysqld

but i never ever had to reboot any linux guest to get it back to life

Am 13.04.2012 16:27, schrieb Johnny Withers:
> I had this same problem with the OOM for a while. Very frustrating to have to 
> reboot a server to bring it back to life.
> 
> I found out the OOM only ran when the swap file was about 99% full. The 
> servers I had this problem on had 16GB and
> 24GB of ram, but only 2GB of swap. I increased the swap on the 24GB servers 
> to 48GB and on the 16GB servers to
> 32GB. The swap never fills to over 60% now and I haven't' had any OOM 
> problems since and the systems run great.
> 
> I've also set vm.swappiness=0 in /etc/sysctl.conf
> 
> -JW
> 
> On Fri, Apr 13, 2012 at 7:17 AM, Reindl Harald  > wrote:
> 
> the following may be useful for most server systems
> 
> OOM-killer acts if some process reclaims more and more
> memory and the kernel randomly kills unimportant tasks
> using hughe memory
> 
> in case of a running mysqld the classification "unimportant"
> is nearly all time wrong and can cause hughe damage and work
> in other words: you really never want killed a database server
> randomly instead "dbmail-imapd" which can be restarted via
> systemd without pain and may be the root-cause of OOM
> _
> 
> with one single command you can protect processes from get killed
> i started to run this every 15 minutes to make sure it is also
> active after restarts
> 
> i am considering include this in "mysqld.service" as
> "ExecStartPost=-/usr/local/bin/mysql-no-oom.sh" in our
> internal mysqld-packages and include also the script
> _
> 
> [root@mail:~]$ cat /etc/crontab | grep oom
> 0,15,30,45 * * * * root bash /usr/local/bin/mysql-no-oom.sh
> 
> [root@mail:~]$ cat /usr/local/bin/mysql-no-oom.sh
> #!/bin/bash
> pgrep -f "/usr/libexec/mysqld" | while read PID; do echo -1000 > 
> /proc/$PID/oom_score_adj; done
> 
> 
> 
> 
> -- 
> -
> Johnny Withers
> 601.209.4985
> joh...@pixelated.net 



signature.asc
Description: OpenPGP digital signature

Re: FYI: how to prevent mysql from oom-killer

[Johnny Withers]

I had this same problem with the OOM for a while. Very frustrating to have
to reboot a server to bring it back to life.

I found out the OOM only ran when the swap file was about 99% full. The
servers I had this problem on had 16GB and 24GB of ram, but only 2GB of
swap. I increased the swap on the 24GB servers to 48GB and on the 16GB
servers to 32GB. The swap never fills to over 60% now and I haven't' had
any OOM problems since and the systems run great.

I've also set vm.swappiness=0 in /etc/sysctl.conf

-JW

On Fri, Apr 13, 2012 at 7:17 AM, Reindl Harald wrote:

> the following may be useful for most server systems
>
> OOM-killer acts if some process reclaims more and more
> memory and the kernel randomly kills unimportant tasks
> using hughe memory
>
> in case of a running mysqld the classification "unimportant"
> is nearly all time wrong and can cause hughe damage and work
> in other words: you really never want killed a database server
> randomly instead "dbmail-imapd" which can be restarted via
> systemd without pain and may be the root-cause of OOM
> _
>
> with one single command you can protect processes from get killed
> i started to run this every 15 minutes to make sure it is also
> active after restarts
>
> i am considering include this in "mysqld.service" as
> "ExecStartPost=-/usr/local/bin/mysql-no-oom.sh" in our
> internal mysqld-packages and include also the script
> _
>
> [root@mail:~]$ cat /etc/crontab | grep oom
> 0,15,30,45 * * * * root bash /usr/local/bin/mysql-no-oom.sh
>
> [root@mail:~]$ cat /usr/local/bin/mysql-no-oom.sh
> #!/bin/bash
> pgrep -f "/usr/libexec/mysqld" | while read PID; do echo -1000 >
> /proc/$PID/oom_score_adj; done
>
>


-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: FYI: how to prevent mysql from oom-killer

[Reindl Harald]

Am 13.04.2012 15:47, schrieb Mihamina Rakotomandimby:
> On 04/13/2012 03:17 PM, Reindl Harald wrote:
>> with one single command you can protect processes from get killed
>> i started to run this every 15 minutes to make sure it is also
>> active after restarts
> 
> I understand your issue, but isn'there a configuration way to just limit the 
> memory usage of MySQL?

no, you did not understand the issue nor my intention
intention was not "i have a problem and needs help"
intention was "if someone may have a problem this is the help"

the problem is that ANOTHER process or many of them triggering
a OOM situation and after that the kernel killing randomly
processes to prevent from a system crash

the proc-setting is to prevent that this randomly process
is mysqld instead the process which is eating up your memory
_

as real world example (i was there)

* machine has 9 GB RAM
* machine is a dbmail-server
* mysqld has assigend buffer-pools and so on up to 7 GB
* someone tryies to copy a 2 GB attachment in drafts folder
* the memory usage of imapd explodes
* the first times OOM killer is killing imapd
* at least the OOM is killing mysqld

you want NOT killed mysqld in this situation
you want not killed mysqld damaging tables becuase you are
running out of memory because too much httpd-processes
eating up your memory

at least - you never want to get randomly killed mysqld

pgrep -f "/usr/libexec/mysqld" | while read PID; do echo -1000 > 
/proc/$PID/oom_score_adj; done

is preventing you from this happening
maybe you want prevent such a situation before it happend

i searched how can i proctect our servers, found a solution and
thought it is a good idea to publish it on 3 mailing-lists
where many users could need it



signature.asc
Description: OpenPGP digital signature

Re: FYI: how to prevent mysql from oom-killer

[Mihamina Rakotomandimby]

On 04/13/2012 03:17 PM, Reindl Harald wrote:

with one single command you can protect processes from get killed
i started to run this every 15 minutes to make sure it is also
active after restarts


I understand your issue, but isn'there a configuration way to just limit 
the memory usage of MySQL?


--
RMA.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

[Claudio Nanni]

Hi
you have to see the reason in the mysql log file,
that is a file either in the datadir with .err extension or in the /var/log
directory.
tail the last 30 lines you will see the reason why it failed to start.

Claudio


2012/4/11 Prabhat Kumar 

> did you check permission of file /var/run/mysqld/mysqld.sock
>
> On Wed, Apr 11, 2012 at 9:48 AM, Larry Martell  >wrote:
>
> > On Wed, Apr 11, 2012 at 2:51 AM, Ganesh Kumar 
> wrote:
> > > Hi Guys,
> > >
> > > I am using debian squeeze it's working good, I am trying to install
> > > mysql-server. mysql-server installation successfully but didn't start
> > > service
> > > root@devel:/var/run# more /etc/mysql/my.cnf |grep socket
> > > # Remember to edit /etc/mysql/debian.cnf when changing the socket
> > location.
> > > socket  = /var/run/mysqld/mysqld.sock
> > > socket  = /var/run/mysqld/mysqld.sock
> > > socket  = /var/run/mysqld/mysqld.sock
> > >
> > > root@devel:~# /etc/init.d/mysql restart
> > > Stopping MySQL database server: mysqld.
> > > Starting MySQL database server: mysqld . . . . . . . . . . . . . .
> > failed!
> > > root@devel:~# mysql -u root -p
> > > Enter password:
> > > ERROR 2002 (HY000): Can't connect to local MySQL server through socket
> > > '/var/run/mysqld/mysqld.sock' (2)
> > > root@devel:~# cd /var/run/mysqld/
> > > root@devel:/var/run/mysqld# ls
> > > root@devel:/var/run/mysqld#
> >
> > Is selinux enabled? If so, check the logs for that.
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> >
> >
>
>
> --
> Best Regards,
>
> Prabhat Kumar
> MySQL DBA
>
> My Blog: http://adminlinux.blogspot.com
> My LinkedIn: http://www.linkedin.com/in/profileprabhat
>



-- 
Claudio

FYI: how to prevent mysql from oom-killer

[Reindl Harald]

the following may be useful for most server systems

OOM-killer acts if some process reclaims more and more
memory and the kernel randomly kills unimportant tasks
using hughe memory

in case of a running mysqld the classification "unimportant"
is nearly all time wrong and can cause hughe damage and work
in other words: you really never want killed a database server
randomly instead "dbmail-imapd" which can be restarted via
systemd without pain and may be the root-cause of OOM
_

with one single command you can protect processes from get killed
i started to run this every 15 minutes to make sure it is also
active after restarts

i am considering include this in "mysqld.service" as
"ExecStartPost=-/usr/local/bin/mysql-no-oom.sh" in our
internal mysqld-packages and include also the script
_

[root@mail:~]$ cat /etc/crontab | grep oom
0,15,30,45 * * * * root bash /usr/local/bin/mysql-no-oom.sh

[root@mail:~]$ cat /usr/local/bin/mysql-no-oom.sh
#!/bin/bash
pgrep -f "/usr/libexec/mysqld" | while read PID; do echo -1000 > 
/proc/$PID/oom_score_adj; done



signature.asc
Description: OpenPGP digital signature

Re: Keynote videos from Percona Live MySQL Conference

[Suresh Kuna]

Thank you Baron, Much appreciated.

On Fri, Apr 13, 2012 at 11:32 AM, Baron Schwartz  wrote:

> If you were not at the Percona Live MySQL Conference over the last few
> days, the keynote videos are recorded for your convenience. You can
> see them at http://www.percona.tv/
>
> Presentations will be posted at http://www.percona.com/live/ as well,
> after the speakers submit them to us for posting. I will mention them
> when they're ready.
>
> - Baron
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


-- 
Thanks
Suresh Kuna
MySQL DBA

Re: Keynote videos from Percona Live MySQL Conference

[Claudio Nanni]

Thanks Baron!

very much appreciated!

Claudio


2012/4/13 Baron Schwartz 

> If you were not at the Percona Live MySQL Conference over the last few
> days, the keynote videos are recorded for your convenience. You can
> see them at http://www.percona.tv/
>
> Presentations will be posted at http://www.percona.com/live/ as well,
> after the speakers submit them to us for posting. I will mention them
> when they're ready.
>
> - Baron
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


-- 
Claudio