tag:blogger.com,1999:blog-14481434987163117832024-03-13T09:08:40.327-07:00MySQL ThoughtsHarrisonhttp://www.blogger.com/profile/05318054903467929570noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-1448143498716311783.post-49784524432408928722010-05-17T06:37:00.000-07:002010-05-17T06:51:58.153-07:00Poor Man's Profiler using Solaris' pstackRecently I was working with the output of <a href="http://docs.sun.com/app/docs/doc/816-0210/6m6nb7mih?a=view">pstack</a> from a hung MySQL server and wanted to use <a href="http://poormansprofiler.org/">Poor Man's Profiler</a> in order to combine stack traces. Unfortunately, the awk magic expects the output from gdb's thread apply all bt output. <br /><br />gdb output:<br /><pre><br />Thread 10 (Thread 0xa644db90 (LWP 26275)):<br />#0 0xb7f47410 in __kernel_vsyscall ()<br />#1 0xb7f33b1a in do_sigwait () from /lib/tls/i686/cmov/libpthread.so.0<br />#2 0xb7f33bbf in sigwait () from /lib/tls/i686/cmov/libpthread.so.0<br />#3 0x081cc4fc in signal_hand ()<br />#4 0xb7f2b4fb in start_thread () from /lib/tls/i686/cmov/libpthread.so.0<br />#5 0xb7d25e5e in clone () from /lib/tls/i686/cmov/libc.so.6<br /><br />Thread 9 (Thread 0xa641cb90 (LWP 26273)):<br />#0 0xb7f47410 in __kernel_vsyscall ()<br />#1 0xb7d1e881 in select () from /lib/tls/i686/cmov/libc.so.6<br />#2 0x081d1190 in handle_connections_sockets ()<br />#3 0x081d1ef3 in main ()<br />...<br /></pre><br />pstack output:<br /><pre><br />----------------- lwp# 56 / thread# 56 --------------------<br /> fffffd7ffed7bb7a sigtimedwait (fffffd7ffe3aee10, fffffd7ffe3aee20, 0)<br /> fffffd7ffed6aced sigwait () + d<br /> fffffd7ffed62740 __posix_sigwait () + 40<br /> 0000000000712dcd signal_hand () + 12d<br /> fffffd7ffed7704b _thr_setup () + 5b<br /> fffffd7ffed77280 _lwp_start ()<br />----------------- lwp# 53 / thread# 53 --------------------<br /> fffffd7ffed7c6ca pollsys (fffffd732fe31dd0, 0, fffffd732fe31e60, 0)<br /> fffffd7ffed234c4 pselect () + 154<br /> fffffd7ffed23792 select () + 72<br /> fffffd7ffe866008 os_thread_sleep () + 50<br /> fffffd7ffea6dafb srv_lock_timeout_and_monitor_thread () + 1b3<br /> fffffd7ffed7704b _thr_setup () + 5b<br /> fffffd7ffed77280 _lwp_start ()<br />...<br /></pre><br />However, we can see how powerful and flexible PMP is by making a small change to the awk script and poof, we had the same output:<br /><br /><pre><br /> awk '<br /> BEGIN { s = ""; } <br /> /thread#/ { print s; s = ""; } <br /> /^ [0-9a-f]/ { if (s != "" ) { s = s "," $2} else { s = $2 } } <br /> END { print s }' | sort | uniq -c | sort -r -n -k 1,1<br /></pre><br /><br />Yet another win for PMP.Harrisonhttp://www.blogger.com/profile/05318054903467929570noreply@blogger.com3tag:blogger.com,1999:blog-1448143498716311783.post-68503072180629680592010-03-02T13:02:00.000-08:002010-03-02T14:06:24.648-08:00Semi-sync Replication TestingI have recently been trying out <a href="http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html">semisynchronous replication</a>. This is a new feature added in MySQL 5.5 based on the <a href="http://code.google.com/p/google-mysql-tools/wiki/SemiSyncReplication">original google patch</a>.<br /><br />Installing was very simple and has been covered <a href="http://datacharmer.blogspot.com/2009/12/getting-started-with-mysql-55.html">elsewhere</a> in detail. Suffice to say it was relatively simple to do.<br /><br />While test I was a bit surprised by some behavior I saw that turned out correct. What I wanted to examine what was semi-synchronous actually does and use cases for it. <br /><br />The manual defines this feature correctly in very careful language:<br /><blockquote>If semisynchronous replication is enabled on the master side and there is at least one semisynchronous slave, a thread that performs a transaction commit on the master blocks after the commit is done and waits until at least one semisynchronous slave acknowledges that it has received all events for the transaction, or until a timeout occurs.</blockquote><br /><br />There is a subtle difference to how this is described in other places, for example from Guiseppe's blog (not picking on him, just an example since I have seen in many places):<br /><blockquote>That is, before committing, the master waits until at least one slave has acknowledged that it has received the portion of binary log necessary to reproduce the transaction.</blockquote><br /><br />What the difference is the fact that the blocking and relay to the remote slave occurs <span style="font-weight:bold;">after</span> the transaction is actually committed to disk.<br /><br />This means that if the master crashes it is possible that it may have committed transactions on disk that are not on the slave. However, the client will get back an error saying that the commit failed.<br /><br />What is the use case for this then?<br /><br />For failover purposes, this is generally exactly what you need. The client can know it has failed and can redo the transaction on the slave. There is no data loss and everything is nicely guaranteed.<br /><br />What doesn't it help?<br /><br />Where it isn't useful is for recovery after the crash. When you finally get your master restarted, it may already have some transactions that were later replayed on the slave. This will naturally cause replication to break and things fail.Harrisonhttp://www.blogger.com/profile/05318054903467929570noreply@blogger.com2tag:blogger.com,1999:blog-1448143498716311783.post-79222317155859385932010-01-14T10:35:00.000-08:002010-01-14T17:11:36.257-08:00State of MySQL SearchRecently I took part in the first meeting of the <a href="http://www.facebook.com/#/group.php?gid=269730273824">MySQL Search Team</a>. You can read more about the team <a href="http://intuitive-search.blogspot.com/2010/01/mysql-search-team-has-launched.html">here</a>.<br /><br />My task was to represent the customer interests regarding fulltext search. My report had a few main points which I made:<br /><br /><ul><li>MySQL Fulltext Search (FTS) has several big issues with it compared to other solutions. I have listed these in the order of importance that I see. Numbers 1 and 2 are very close in importance however, so I can see those going either way. <br /><br /><ol><li>MyISAM only: Limits the usefulness in many very busy environments due to table level locking and crash safety</li><br /><li>Performance: The <a href="http://forge.mysql.com/wiki/MySQL_Internals_Algorithms#Full-text_Search">design</a> of FTS makes performance hurt as the index size exceeds RAM. Combining multiple second queries with MyISAM table level locking is a recipe for failure.</li><br /><li>Features: There are a lot of features lacking in FTS that exist in other products. Stemming, synonyms, dictionary usage, CJK support, etc... are all potential avenues of investigation.</li></ol><br /><li>Third-party solutions are commonly used for searching data in MySQL, but does require extra work to deploy or implement. Tools like <a href="http://lucene.apache.org/">Lucene</a>, <a href="http://www.sphinxsearch.com/">Sphinx</a>, etc... are very good at what they do, and are normally faster and more fully featured compared to MySQL FTS. The big issue is that normally there is some effort involved in implementing these, so the ease of use in implementing and maintaining is lower than MySQL FTS.</li><br /></ul><br /><br />So the forward looking question is how to combine the best attributes of these two solutions to get one easy to deploy and use robust solution. Personally, I can see a few possible routes forward:<br /><ul><li>Improve MySQL FTS! There is a lot of research and known best practices that exist for search. This would leverage those in order to make the MySQL solution more robust and featureful. From the current state of FTS, it would be a pretty decent amount of work, but could have good returns.</li><li>Improve the glue code between the third parties and MySQL. This could take a variety of forms. One idea I had was in the the form of index plugins where as rows are inserted/updated/deleted, the external datastores can be automatically updated as well. This is very similar to triggers, but is a bit different since there can be more integration and easier deployment. </li></ul>It's possible the best solution may be a mix of the two options to allow for more flexibility or even something completely new that I haven't thought of!<br /><br />Regardless, I am happy that people at MySQL are finally taking a look at the poor state of the MySQL fulltext search environment and trying to figure out new good solutions for this space.Harrisonhttp://www.blogger.com/profile/05318054903467929570noreply@blogger.com3tag:blogger.com,1999:blog-1448143498716311783.post-79222306371923259222009-12-02T12:47:00.000-08:002009-12-02T13:36:39.077-08:00Ext4 with MySQL binary logs oddityI was working with a customer recently that kept seeing 10-12 second hangs in MySQL. Everything would be working well and suddenly all data changing statements would stop working.<br /><br />Once the 10-12 seconds passed, the system would recover and everything would be fine for a while. This would repeat every 30-45 minutes normally, and would occur more frequently when the system was doing some heavy ETL type activity (data loads, big updates, etc...), with the ETL ones being longer and worse generally.<br /><br />One of the steps taken while investigating was to check out vmstat/iostat. From this we noticed that during the spike there was very intense disk activity. I suspected something wrong with InnoDB such as the famous <a href="http://dimitrik.free.fr/blog/archives/2009/08/entry_76.html">purge hiccup</a> or something with the log files checkpointing, etc... However we then found out that it was the disk system where the binary logs were stored and not the InnoDB files.<br /><br />While continuing to investigate, it was noticed that the times it occurred happened to exactly match up with the times that the binary log rotations were occurring. Using strace, we were able to notice that the fdatasync() call that accompanied the rotation was taking a very long time (the majority of the delay time).<br /><br />Finally we were able to pin it down to ext4 and <a href="https://bugs.launchpad.net/ubuntu/+source/linux/+bug/317781">how it delays data writes for a very long time (30 minutes)</a>. Compare this to ext3 which will flush things every 5 seconds or so. I am told that ext4 recently changed this behavior (this was in 2.6.30), so hopefully this won't hit more people.<br /><br />What was happening was as follows:<br /><ul><br /><li>Binary log data gets written to over time</li><br /><li>ext4 in an attempt to increase performance does not write the data to disk</li><br /><li>ext4 continues to not write data to disk, even as hundreds of MB of binary log sits in memory and the disk is mostly idle</li><br /><li>Binary log gets full (1024MB) and rotates while holding the log mutex</li><br /><li>Rotation calls an fdatasync prior to closing the file</li><br /><li>ext4 now has to write the data out and takes 10-12 seconds to do so</li><br /><li>log mutex prevents any commits while it writes</li><br /><li>Write finishes, log rotates, things repeat</li><br /></ul><br /><br />To alleviate this, we ended up setting <a href="http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#sysvar_sync_binlog">sync_binlog</a>=100. This will force the binary log to fsync periodically and hence not allow ext4 to wait forever to write the data out to the file. There is also the commit mount option for ext4 which should give similar benefits by forcing it to write more often but I didn't test it.Harrisonhttp://www.blogger.com/profile/05318054903467929570noreply@blogger.com6tag:blogger.com,1999:blog-1448143498716311783.post-20155671196790969272009-05-15T14:52:00.000-07:002009-05-15T15:05:26.565-07:00Small features being pushedSo back in 2004 I created a small feature for mysqladmin allowing it to prompt for a password to set, rather than needing to specify it on the command line. It was a feature that came up in a training course I was teaching, so I wrote it up while at the hotel one day. I had filed this as <a href="http://bugs.mysql.com/5724">Bug #5724</a>. <br /><br />The reason it got filed as a feature request bug is due to the fact that being a non-developer at MySQL AB meant I followed the normal community contributions process. I had several patches accepted via the community model (mysql prompt, some mysql readline enhancements, etc...) previous to this patch. However, for some reason around the time that this patch was submitted, patch acceptance slowed down.<br /><br />Apparently the community acceptance process is actually working again, because today it got <a href="http://lists.mysql.com/commits/73069">queued</a> and pushed to a team tree which will be merged into MySQL 6.0. Only took 4.5 years to get in. Hopefully, my next patch will be included in a much shorter time now with the new process.<br /><br />Note that I haven't done anything with this bug recently, <a href="http://trainedmonkey.com/">jimw</a> apparently found it himself or was prodded by someone to handle it.Harrisonhttp://www.blogger.com/profile/05318054903467929570noreply@blogger.com3tag:blogger.com,1999:blog-1448143498716311783.post-14300745650147664402009-05-13T12:50:00.000-07:002009-05-13T15:01:08.896-07:00Production example of Dtrace + MySQL (Part 1)I had read a lot about using dtrace with MySQL, but recently had the chance to use it and thought it worked extremely well, so I wanted to share. <a href="http://dammit.lt/">Domas</a> mentioned some of this in the presentation he did on dtrace + MySQL at the MySQL conference.<br /><br />The basics of this are true, somethings have been changed to help protect the identity of the customer.<br /><br />I am going to spread this out over a few postings to show how dtrace can be used to incrementally find out things that aren't available otherwise.<br /><br /><br />The customer was reporting that queries were extremely fast, taking a few milliseconds normally, but sometimes they would get queries that took 500+ milliseconds. They wanted to know why and how this was occurring. They happened to be on Solaris 10, so dtrace to the rescue!<br /><br />So the first task was to find out what were the slow queries. In MySQL 5.1, we could use the slow query log with millisecond precision, but naturally this was MySQL 5.0. The first script was designed just to capture the slow queries and see if they had any sort of pattern. The initial script looked like:<br /><br /><code><br />#!/usr/sbin/dtrace -s<br /><br />#pragma D option quiet<br /><br />/* how much memory to use maximum<br /> * if you start getting errors regarding dropped data from the script, then <br /> * you might get incorrect information<br />*/<br />#pragma D option dynvarsize=512m<br /><br /><br />/* how much of the query to copy, large takes up more memory */<br />#pragma D option strsize=256<br /><br />/*<br /> value in nanoseconds of when to log a query as slow and information<br /> about it<br /> 1000000 is 1 ms<br /> 50000000 is 50 ms<br /> 250000000 is 250 ms<br /> */<br />inline int long_query_time = 250000000;<br /><br />BEGIN<br />{<br /> /* initialize our aggregates in case there aren't any */<br /> @total["Total queries"] = count();<br /> @total["Total slow queries"] = count();<br /> clear(@total);<br /> self->in_query = 0;<br /> self->query = "";<br /> printf("Capturing any queries longer than %d milliseconds.\n", long_query_time /<br /> 1000000);<br /> printf("Please press Ctrl+C to exit when done...\n\n");<br />}<br /><br />/*<br /> * catches the beginning of the execution of our query<br /> */<br /><br />pid$target::*dispatch_command*:entry<br />/ self->in_query==0 /<br />{<br /> @total["Total queries"] = count();<br /> self->start_time = timestamp;<br /> self->in_query=1;<br />}<br /><br />/*<br /> * Find our slow queries<br /> */<br /><br />pid$target::*dispatch_command*:return<br />/ (timestamp - self->start_time) > long_query_time && self->in_query==1 /<br />{<br /> self->total_time = timestamp - self->start_time;<br /><br /> @total["Total slow queries"] = count();<br /> printf("Date/time: %Y\n", walltimestamp);<br /> printf("Query: %s\n",self->query);<br /> printf("Query time: %d microseconds\n", self->total_time / 1000);<br /><br /> printf("\n");<br />}<br /><br />/*<br /> * executes for every query to reset them<br /> */<br /><br />pid$target::*dispatch_command*:return<br />/ self->in_query == 1/<br />{<br /> self->query="";<br /> self->in_query=0;<br /> self->start_time=0;<br /> self->total_time=0;<br />}<br /><br />/*<br /> * catch the query string<br /> */<br /><br />pid$target::*mysql_parse*:entry<br />/ self->in_query == 1/<br />{<br /> self->query = copyinstr(arg1); <br />}<br /><br /><br />END <br />{<br /> printa("%s: %@d\n", @total); <br />}<br /></code><br /><br />This then results in output such as:<br /><br /><pre><br /># ./test.d -p 27609<br />Capturing any queries longer than 250 milliseconds.<br />Please press Ctrl+C to exit when done...<br /><br />Date/time: 2009 May 13 23:11:52<br />Query: select sleep(2)<br />Query time: 2004913 microseconds<br /><br />^C<br />Total queries: 1048<br />Total slow queries: 1<br /></pre><br /><br />I did a few tests on the overhead to determine if it would slow down there system (they do several thousand requests a second), and to my surprise it had virtually no overhead once running. There was a bit of extra overhead when the script was first initiated and when ending it and it would cause a very slight hiccup in processing (< 1 second). It was decided this was okay, and we scheduled it to run during some lower load times so this hiccup wasn't a big deal.<br /><br />This caught a few thousand slow queries over the course of an hour. Some were easy to discard due to the nature of the queries, but some others needed further investigation. The next post will discuss what the next steps were and will start getting data that we can't see in the slow query log.Harrisonhttp://www.blogger.com/profile/05318054903467929570noreply@blogger.com0tag:blogger.com,1999:blog-1448143498716311783.post-80377190777252934672009-02-19T12:37:00.000-08:002009-02-20T12:25:54.484-08:00My Favorite New Feature of MySQL 5.1: Less InnoDB LockingMySQL 5.1 has a lot of nice new features, partitioning, event scheduler, dynamic logging, etc... Most of these require changing some application code or design logic to make use of them. However, there is a few new features that almost every user of InnoDB can take advantage of right away and that is the ability to set MySQL so that less locking is required for many statements in InnoDB. There are a few changes which make this possible.<br /><br />The first one is the new auto_increment locking for InnoDB. There is a good article which talks about this <a href="http://dev.mysql.com/tech-resources/articles/mysql-5.1-recap.html">here</a> and a large section the in the <a href="http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-increment-configurable">manual</a>. <br /><br />In MySQL 5.0 and previous, InnoDB used a special query-duration table level lock to acquire the AUTO-INC lock. This basically caused all inserts into the same table to serialize. With single row inserts it normally wasn't too bad but could prevent some scalability with multiple threads inserting. However, where it was quite bad was large data insertions, such as INSERT...SELECT and LOAD DATA INFILE. While these were running, all other insertions into the table would be prevented due to this table level lock.<br /><br />MySQL 5.1 has changed this behavior. There is a new option innodb_autoinc_lock_mode which controls how InnoDB will handle this. The default is a value of 1 which works well if you are doing only inserts where MySQL knows the amount of rows being inserted. For example, a single row insert or a multi-row insert would be fine in this mode. In these cases, it will use a very short term mutex which will be released immediately after acquiring the needed values. It should remove all contention issues if you are using only these statements.<br /><br />Statements where the number of rows being inserted are unknown, such as INSERT...SELECT and LOAD DATA INFILE both still use the AUTO-INC table level lock for this and will cause other single row statements to lock on it. To fix this you need to set innodb_autoinc_lock_mode=2. This will make each row in these long running statements get a new value, rather than holding it for the duration. The problem with this is that now the values generated won't be consecutive anymore. Other transactions could grab values to use in the middle of the sequence. <br /><br />For some applications this might be a problem (but shouldn't if you are just using the auto_increment for uniqueness). The other issues comes from the binary log. In statement level binary logging, the auto_increment starting point is logged and then the statement. However, since it is no longer consecutive, the slave can't create the appropriate values and replication will break. The solution to this also comes in 5.1 in the form of row level binary logging. You can enable all row based binary logging and it will then handle this without issue.<br /><br />So to summarize, to pretty much completely remove auto_increment locking in InnoDB, you will need to set both innodb_autoinc_lock_mode=2 and binlog_format=row.<br /><br /><br />There is a second locking improvement in InnoDB related to the row level binary logging. To enable this, you will need to use READ COMMITTED isolation level. When you do this in 5.1, you will then cause InnoDB to reduce locking for DML statements a great deal. It will not lock any rows searched upon, but not changed with the DML statements.<br /><br />In older versions every row that was searched in a data changing statement would be locked. For example, take the following statement:<pre>DELETE FROM tbl WHERE key < 1000 AND non_key = 500</pre>In 5.0, assuming the key was used, every row less than 1000 would be exclusively locked, even if only a few were changed. In 5.1 and READ COMMITTED, only the rows that are actually changed will be locked now. As you can imagine, this potentially can reduce locking contention a great deal.<br /><br /><br />One final topic I want to mention innodb_locks_unsafe_for_binlog option. People have asked if they should enable this when binlog_format=row. It makes sense to do so, since with row level binary logging, the restrictions for the binary log are gone. The answer is that this variable does not matter anymore in 5.1 if you set READ COMMITTED. In all cases where innodb_locks_unsafe_for_binlog used to reduce locking, READ COMMITTED now does as well. <br /><br />From the <a href="http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog">manual</a>:<br /><blockquote>This also means that if new items are added to the database, InnoDB does not guarantee serializability. Therefore, if innodb_locks_unsafe_for_binlog is enabled, InnoDB guarantees at most an isolation level of READ COMMITTED. (Conflict serializability is still guaranteed.) </blockquote>This means that you also can not run higher than READ COMMITTED while using innodb_locks_unsafe_for_binlog, so there is no point in setting innodb_locks_unsafe_for_binlog.<br /><br /><br />So to summarize and get the best locking out of InnoDB in 5.1, you want to set the following options:<pre>[mysqld]<br />innodb_autoinc_lock_mode=2<br />binlog_format=row<br />tx_isolation=READ-COMMITTED</pre><br /><br /><br />Keep in mind that row based binary logging does potentially have additional disk overhead compared to statement binary logging, so as always, please test these values before using them in production.Harrisonhttp://www.blogger.com/profile/05318054903467929570noreply@blogger.com4tag:blogger.com,1999:blog-1448143498716311783.post-4068797359964608952009-02-18T12:13:00.001-08:002009-02-18T12:53:53.373-08:00MySQL, Windows 32-bit, and /3GBOne question that comes up relatively commonly in support is how much memory can MySQL use on my system. As you may know, MySQL runs in a single process with many threads. So the memory limit it has is based upon the operating system limits for a single process. Different operating systems have different limits, but the one I want to discuss is Windows 32-bit (such as 2000, XP, 2003, vista).<br /><br />The short answer is that your mysqld-nt.exe can use up to ~1.8G of RAM. The question is why is that so. Since we have a 32-bit address space, in theory there is 2^32 mapping for the address space (4G). However, Windows uses what is called a 2G/2G split in that addressable space. 2G are allowed for the actual application to use and 2G is for the kernel mappings.<br /><br />This 2G is then the address space of points, which normally ends up with around 1.8G of memory really being usable. If you try to use more than this, then you end up getting Out of Memory errors from MySQL.<br /><br />Now Windows 2000 and above has a way you can adjust this split. Not all versions of Windows within these families support this, so it depends upon what version you are using. The specific feature is called <a href='http://blogs.technet.com/askperf/archive/2007/03/23/memory-management-demystifying-3gb.aspx'>'4 GT RAM Tuning' by Microsoft</a>.<br /><br />To adjust this, you will need to do a few steps, to enable it on Windows and then within MySQL.<br /><br />First, you need to boot Windows with the /3GB flag given to it. You will need to edit the boot.ini, find the instance you are booting off of, and add that flag to it.<br /><br />Second, you will need to enable for any program that wants to use this split. MySQL does not ship with this done by default (it is normally a compile time option), so we will need to manually set it (or compile your own mysqld-nt.exe on Windows).<br /><br />There is a program that comes with Visual Studio C++ called 'editbin'. I use the express version which is free. There is a /LARGEADDRESSAWARE flag you can pass to it to enable the option for your server. I am running this via the Visual Studio Command Prompt.<br /><br /><pre>C:\Program Files\MySQL\MySQL Server 5.0\bin>editbin /LARGEADDRESSAWARE mysqld-nt.exe<br />Microsoft (R) COFF/PE Editor Version 8.00.50727.42<br />Copyright (C) Microsoft Corporation. All rights reserved.</pre><br /><br />To confirm that this works, you can use dumpbin /HEADERS and there is an option in the very top which says if it is enabled or not.<br /><pre>C:\Program Files\MySQL\MySQL Server 5.0\bin>dumpbin /headers mysqld-nt.exe<br />Microsoft (R) COFF/PE Dumper Version 8.00.50727.42<br />Copyright (C) Microsoft Corporation. All rights reserved.<br /><br /><br />Dump of file mysqld-nt.exe<br /><br />PE signature found<br /><br />File Type: EXECUTABLE IMAGE<br /><br />FILE HEADER VALUES<br /> 14C machine (x86)<br /> 6 number of sections<br /> 487FA5AC time date stamp Thu Jul 17 16:03:56 2008<br /> 0 file pointer to symbol table<br /> 0 number of symbols<br /> E0 size of optional header<br /> 12F characteristics<br /> Relocations stripped<br /> Executable<br /> Line numbers stripped<br /> Symbols stripped<br /> Application can handle large (>2GB) addresses<br /> 32 bit word machine</pre><br /><br />Notice the second to last line, "Application can handle large (>2GB) addresses". This means it is enabled for the binary, if it is missing, then the binary does not have it enabled.<br /><br />Once these two things are done you will now have a 3G address space for MySQL, and you should be able to go up to ~2.6G or so. You are still limited to smaller allocations (ie, you can't set innodb_buffer_pool_size=2.5G), but it can give you more memory for handling connections and per-connection buffers which can effectively allow you to increase the large global buffers.<br /><br />For more details, I would like to recommend you read the <a href='http://blogs.technet.com/askperf/archive/2007/03/23/memory-management-demystifying-3gb.aspx'>technet blog</a> about the topic, it is quite a good explanation.Harrisonhttp://www.blogger.com/profile/05318054903467929570noreply@blogger.com10tag:blogger.com,1999:blog-1448143498716311783.post-8555321223509189442009-02-05T14:02:00.000-08:002009-02-05T13:44:10.836-08:00My take on MySQL 5.1So being that I am a support engineer with Sun/MySQL, I figured I would chime in with my opinion of MySQL 5.1 GA so far since I have been using it for a while. For history, I have been with MySQL AB/Sun since the 4.0 release cycle. This post does not represent any official views of my employer, Sun Microsystems, and all such disclaimers.<br /><br />First off, I want to say that Sun assisted greatly with MySQL 5.1. I think it is a <u>much</u> better release than it would have been with MySQL AB doing the release. I do think this will continue in the future from everything I have experienced and seen so far. Sun knows how to make solid software.<br /><br /><br />So do I think that 5.1 was released too early? Not at all from my experiences of the MySQL releases. 5.1 is fairing quite well, and I think everyone that has a reason to upgrade to it (such as less locking for InnoDB auto_increment, partitioning, dynamic logging, etc...) should certainly start the process of doing so.<br /><br />MySQL 5.1 is in quite good shape compared to my experiences with MySQL 4.0 to 5.0. As a chart of my experiences:<table border="1"><tr><th>Release</th><th>GA Version</th><th>My first recommended version</th></tr><br /><tr><td>MySQL 4.0</td><td>4.0.12</td><td>4.0.15</td></tr><br /><tr><td>MySQL 4.1</td><td>4.1.7</td><td>4.1.12</td></tr><br /><tr><td>MySQL 5.0</td><td>5.0.15</td><td>5.0.36</td></tr><br /><tr><td>MySQL 5.1</td><td>5.1.30</td><td>5.1.31</td></tr><br /></table><br />These versions aren't based on exact bugs fixed, but based on interactions with many customers using the versions and various issues being experienced. It has a bit of gut feeling involved too, so it certainly is not a pure scientific method for determining this.<br /><br />Also by no means is MySQL 5.1 perfect. There have been some <a href="http://bugs.mysql.com/bug.php?id=38883">pretty</a> <a href="http://bugs.mysql.com/bug.php?id=40954">big</a> <a href="http://bugs.mysql.com/bug.php?id=40972">bugs</a> found and some <a href="http://bugs.mysql.com/bug.php?id=38066">annoying</a> ones too. However, overall it has been working pretty well for most users.<br /><br />I still do recommend you do test out all new functionality to ensure it works for you as there are many limitations. However, I would say that for any major software upgrade with any database system, this isn't specific to MySQL or the 5.1 release.<br /><br />So go ahead and give 5.1 a try-out if you have been on the fence. Have a look around and kick the tires and be sure to <a href="http://bugs.mysql.com">report any bugs</a> you find.Harrisonhttp://www.blogger.com/profile/05318054903467929570noreply@blogger.com2tag:blogger.com,1999:blog-1448143498716311783.post-79568138259642769472009-01-29T06:38:00.000-08:002009-01-29T06:49:10.507-08:00XA and Persistent InnoDB LocksRecently a customer mentioned that they were seeing corruption rarely when they copied InnoDB files using LVM to setup a new slave. Investigating further, it turns out that replication would start, but would then hit a lock wait timeout. This locking issue occurred across restarts causing replication to always fail. They would solve this by taking a new LVM snapshot and resetting it up.<br /><br />This is a classic case of an <a href="http://dev.mysql.com/doc/refman/5.0/en/xa.html">XA transaction</a> in a prepared state persisting across restarts and the LVM sometimes taking a snapshot at the exact instant when this can occur.<br /><br />Most people don't use actually XA transactions, so they may not consider this possibility. However, it can still occur even if you do not use them due to XA being used internally for the binary log and InnoDB. We will be able to identify that case later.<br /><br />You can tell this is occurring due to a mention of prepared transactions in the error log:<br /><pre>090128 11:09:09 [Note] Starting crash recovery...<br />090128 11:09:09 InnoDB: Starting recovery for XA transactions...<br />090128 11:09:09 InnoDB: Transaction 0 18707 in prepared state after recovery<br />090128 11:09:09 InnoDB: Transaction contains changes to 1 rows<br />090128 11:09:09 InnoDB: 1 transactions in prepared state after recovery<br />090128 11:09:09 [Note] Found 1 prepared transaction(s) in InnoDB<br />090128 11:09:09 [Warning] Found 1 prepared XA transactions</pre><br /><br />In addition, you will see a transaction listed in PREPARE without any MySQL or OS thread id in the output of SHOW INNODB STATUS:<br /><pre>---TRANSACTION 0 18707, ACTIVE (PREPARED) 11 sec, OS thread id 0<br />, undo log entries 1</pre><br /><br /><br />So how do you solve this when it occurs? The key is using the <a href="http://dev.mysql.com/doc/refman/5.0/en/xa-statements.html">XA commands</a> which allow you to manipulate XA transactions in the prepared state.<br /><br />The first command is XA RECOVER which will show you a list of the possible transactions to handle. The data column has the XID of the transaction to rollback. If the XID starts with MySQLXid then it is an internal generated XID. The XID can contain unprintable characters for your terminal, so keep that in mind it might be a bit different than it initially looks.<br /><pre>mysql> xa recover;<br />+----------+--------------+--------------+------+<br />| formatID | gtrid_length | bqual_length | data |<br />+----------+--------------+--------------+------+<br />| 1 | 3 | 0 | foo | <br />+----------+--------------+--------------+------+<br />1 row in set (0.00 sec)</pre><br /><br /><br />After that, you can then use XA ROLLBACK to remove the transaction or XA COMMIT to save the change. Both of these require that you specify the XID that you got from the previous one.<br /><br />I wrote a one-line shell script which might work when you have a single XID to commit. I haven't tested it very thoroughly, so don't get mad at me if it doesn't work in all cases.<br /><pre>echo xa commit \'`mysql --skip-column-names -e 'xa recover' | awk '{ print $4 }'`\' | mysql</pre>Harrisonhttp://www.blogger.com/profile/05318054903467929570noreply@blogger.com11tag:blogger.com,1999:blog-1448143498716311783.post-46329292787331214242009-01-16T11:41:00.000-08:002009-01-16T12:13:14.368-08:00Enabling InnoDB Large Pages on LinuxIn MySQL 5.0, InnoDB gained the ability to use Linux Large Page support for allocating memory for the buffer pool and additional memory pool. <br /><br />A few customers have asked about using it and there is virtually no documentation on what is required on Linux to enable it. I actually ended up having to read some of the Linux kernel source code to figure out some of this.<br /><br />This uses the API as documented at:<br /><a href="http://www.mjmwired.net/kernel/Documentation/vm/hugetlbpage.txt"><br />http://www.mjmwired.net/kernel/Documentation/vm/hugetlbpage.txt</a><br /><br />To set this up and use it, you first need a kernel that supports it. All of the recent RHEL kernels do by default from what I can tell. On my Ubuntu systems, I'm not seeing it enabled normally.<br /><br />Then on the OS level you will need to do the following procedures:<br /><pre><br /> # Set the number of pages to be used<br /> # Each page is normally 2MB, so this would be 40 MB<br /> # This actually allocates memory, so it requires that much memory to be available<br /> echo 20 > /proc/sys/vm/nr_hugepages<br /><br /> # Set the group number that is allowed to access this memory<br /> # The mysql user must be a member of this group<br /> echo 102 > /proc/sys/vm/hugetlb_shm_group<br /><br /> # Increase the amount of shmem allowed per segment<br /> # 256MB in this case<br /> echo 268435456 > /proc/sys/kernel/shmmax<br /><br /> # Increase total amount of shared memory<br /> # This is 4KB pages, ie. 16GB below<br /> echo 4194304 > /proc/sys/kernel/shmall<br /></pre><br />For MySQL usage, you would normally want the shmmax to be close to the shmall.<br /><br />You would normally want to put these into an rc file or similar to do it at every boot sequence (early in the boot sequence normally, prior to MySQL starting).<br /><br />To verify it works with:<br /><pre>cat /proc/meminfo | grep -i huge</pre>The final step is in order to make use of the hugetlb_shm_group, you need to give the mysql user 'unlimited' value for the memlock limit. This can either by done by editing /etc/security/limits.conf or by adding the following to your mysqld_safe:<br /><pre>ulimit -l unlimited</pre>This will cause the root user to set it to unlimited before switching to the mysql user.<br /><br />Finally, you will want to add the large-pages option to your my.cnf:<br /><pre> [mysqld]<br /> large-pages</pre>With this option, InnoDB will use it automatically for the two memory pools. If it can not, it will fail back and use traditional memory and output a warning to the error log.<br /><br />You can verify it is being used by looking at:<br /><pre>cat /proc/meminfo | grep -i huge</pre><br />Solaris also has the ability to use large pages (of different sizes as well), but MySQL doesn't support that yet.Harrisonhttp://www.blogger.com/profile/05318054903467929570noreply@blogger.com4