Kevin Murphy just posted Tip of the Day — max_connections and I thought it might be nice to know what people actually set this to so I’ve created a Survey “What’s your max_connections” (totally anonymous).

For the record, while a consultant the record for a production system was 9,500. Yes you read right, and I might add the client was running a 32-bit OS as well, just to boot.
Apparently they had also tested they could get 9,500 connections, problem is they didn’t do anything with the connections, so didn’t see any per thread memory usage, and I did not get confirmation of how the actually system memory usage was used during the test.

It’s been six weeks since the completion of the acquisition of MySQL by Sun Microsystems. In this time, on the MySQL News and Press Releases there has only been one (1) Press Release here.
In the six weeks between the announcement and completion of the acquisition MySQL made ten (10) Press Releases, and the six weeks preceding this including the Christmas break there was (six) Press Releases.

On the Sun Press Releases in this period there has been twenty three (23), but just the same one MySQL specific.

The MySQL Developer Zone has picked up three articles in the past week, but nothing prior to that for a month.

So, Where’s the News of what’s happening with MySQL?

It seems our only options are the official company blog of Kaj Arno and his travels , Robin Schumacher and Giuseppe Maxia as other sources of information.

There has been some discussion recently regarding the death of MySQL Read Replication starting with Brian Aker and then Farhan, Arjen and Paul have all chimed in. Whatever you want to call it, the next generation of replication approach is clearly on the agenda of the industry leaders and pack followers. We should take a programmatic look however and ask ourselves a few questions. Such as:

1. The Use — What is/was MySQL Replication used for?
2. The Reasons — Why was it used?
3. The Problems — Why is there a need for something better, different or improved?

Read Scalability

1. The Use — Clearly the most commonly known method of MySQL Replication is for Read Scalability. The ability for many more reads to occur against the data then writes. I call it the YouTube effect, thousands/millions of reads (i.e. view a video) to a single write (i.e. upload of a video)
2. The Reasons — Database servers could not support the read/write load. Applications were poorly designed for sharding data. The database was being used for a function when it wasn’t really needed. The MySQL Server can’t scale to a massive number of cores.
3. The Problems — MySQL Replication suffers from the asynchronous condition. A successful transaction on the Master, is not confirmed with a Slave before acknowledgment to the client. Google has made inroads in their own environment to improve the asynchronous nature with the SemiSyncReplication patch, now submitted back to the community. The number one effect of asynchronous replication is lag, and again poorly developed applications simply can’t support successful scale-out. Other problems include locking contention when not using a transactional engine.

Backups

1. The Use — A Replication slave is used to perform a database backup, designed to not affect production usage.
2. The Reasons — MySQL does not support a single “unbreakable” online backup solution. Period. There are partial/incomplete implementations, dependent on storage engines and data volume. There is a Roadmap for a solution in MySQL 6.0 (finally), but it’s way too early to tell what features will there be, will you need to pay for some of them, will it just work, let alone who is going to upgrade to 6.0 for this.
3. The Problems — There exists no consistent method for backup across all storage engines. There is no compatibility for backup/point in time recovery (e.g. binary logs/redo logs) across different master/slave servers. No mirrored binary logs is also a problem, something also in the Google Patch.

Failover

1. The Use — You need Business Continuity or Revenue Continuity Solutions (RCS) as discussed by Marc Simony. You need to ensure the maximum uptime possible with a failover infrastructure when some component (software or hardware) fails and your production database is not accessible.
2. The Reasons — MySQL provides no Online DDL. In a 24×7 large volume, changing functionality application this provides an unnecessary complexity for applying changes easily.
3. The Problems — When correctly configured and applied in a controlled situation, failover can work, provided your data is not too large, and your traffic is not too much. It simply doesn’t have a guarantee to work without data loss in an uncontrolled (i.e. disaster recovery) situation.

Others

There are more reasons, something to write about at a later time. These would include.

• Testing
• Alternative data access path (e.g. Data Warehousing)
• Upgrade Path/Migration Path

Options

To handle Read Scalability. Why select something from the database (or various level of caches in the database call when it can be managed by the Application). Memcache is the obvious buzz word here. With tighter database integration (such as the patches and commands FaceBook has) this could be a winner.

Backups is a tough one, without practical storage engine agnostic consistent solutions I don’t feel the backup conundrum will be resolved anytime soon.

To handle a successful failover you need a guarantee that the data is consistent. A Synchronous Replication solution will solve this. MySQL Cluster is a synchronous solution, however it is impractical for basically every application that is already running with MySQL Replication.

What other options exist? Food for thought.

About Memcache

A little on Memcache for those still in the dark ages. Many large sites such as FaceBook,Fotolog, Wikipedia and Slashdot are strong proponents of Memcached. Created originally for Live Journal Memcached has become the defacto standard in this level of application data caching.

The description from the source.

memcached is a high-performance, distributed memory object caching system, generic in nature, but intended for use in speeding up dynamic web applications by alleviating database load.

This week I’d expected to hear good friend Giuseppe (CCO) Maxia speak about MySQL SandBox at the Hamburg MySQL April Meetup.

This is product I’ve thought about using, wanted to use, but just never got to the point to using. I download the current version 1.18, I had MySQL tar’s already of 5.0, 5.1 and 6.0 and was all ready until the late topic change.

However due to the language barrier in the second talk, I got a one-on-one lesson. Now I know how it works, and understands the strengths I can use it as part of my standard vanilla testing. There are some improvements I could see, something perhaps I can contribute if they allow a Perl part-timer too. The joy of open source is I can add, modify, change and submit my work, if not wanted for example, there is simply no loss to me.

• By default installation goes to $HOME, and then creates a different standard for individual, replication or multiple instances. I’d like to see this default to a directory say $HOME/msb so all different installs are all under one directory node. You can specific this currently via –sandbox_directory otherwise.
• express_install.pl could benefit from a –help option
• I’d like the set_replication.pl and set_many.pl commands to support an array of mysql tar’s. I’m most interested in testing replication say from 5.0 to 5.1 and 6.0, not just 5.0 to 5.0. I’d also like to be able to install a 5.0, 5.1 and 6.0 versions and be able to use the multi_cmd to run the same SQL across different versions easily.
• The multi_cmd would benefit from a means to list the identify of the instance with the output. It’s hard as MySQL does not have MYSQL_SID, only a server_id which can be a little confusing.

Thanks Giuseppe for your work on this product.

Current syntax

./install.pl --help
Unknown option: help
    The MySQL Sandbox,  version 1.18 05-Apr-2008
    (C) 2006,2007,2008 Giuseppe Maxia, MySQL AB
syntax: ./install.pl [options]
    -h --home_directory = name          The home directory. (default: $HOME (/Users/rbradfor))
    -d --sandbox_directory = name       Where to install the sandbox, under home-directory
    -P --sandbox_port = number          The port number to use for the sandbox server.
                                        (Default: 3310)

    --datadir_from = name               Where to get datadir files from. Available options are
                                        archive   will be taken from the archived data
                                                  provided with the package. They include
                                                  default username and passwords
                                                  ( DEPRECATED )
                                        script    the script mysql_install_db is called, with
                                                  default users, no passwords.
                                        dir:name  will be copied from an existing mysql directory
                                        (Default: script)

    -i --install_version = name         Which version to install (3.23, 4.0, 4.1, 5.0 or 5.1) default: 5.0
    -b --basedir = name                 Base directory for MySQL (default: /usr/local/mysql)
    -m --my_file = name                 which sample my-{small|large|huge}.cnf file should be used
                                        for additional configuration
                                        You may enter either the label (small|large|huge) or a full
                                        file name. (default: none)

    -f --conf_file = name               Configuration file containing options like the ones
                                        you can give on the command line (without dashes)

    -U --operating_system_user = name   Operating system user (for mysql installation)
                                        default: $USER (rbradfor)

    -u --db_user = name                 user for global access to mysql (Default: sandbox)
    -p --db_password = name             password for global access to mysql (Default: sandbox)
    -c --my_clause = name               option to be inserted in a my.cnf file
                                        it may be used several times

    --prompt_prefix = name              prefix to use in CLI prompt (default: mysql)
    --force                             Use this option if you want to overwrite existing directories
                                        and files during the installation. (Default: disabled)

    --no_ver_after_name                 Do not add version number after sandbox directory name (default: disabled)
    -v --verbose                        Use this option to see installation progress (default: disabled)
    --load_grants                       Loads the predefined grants from a SQL file.
                                        Useful when installing from script.
                                        (default: disabled)

    --no_load_grants                    Does not loads the predefined grants from a SQL file.
                                        (default: disabled)

    -t --interactive                    Use this option to be guided through the installation process (default: disabled)
    --no_confirm                        suppress the confirmation request from user

Example:
     ./install.pl --my_file=large --sandbox_directory=my_sandbox

Tonight’s Hamburg MySQL Meetup was a packed room of 60-70 people at Sun in Hamburg. The most I’ve seen @ a MySQL Meetup across three cities, countries, continents and years (my first Oct 5, 2004).

As expected most was in German. Pity, I would have liked to hear about Open Office and MySQL. Giuseppe, CCO (That’s Chief Cartoon Officer – I liked that one) gave in English a general review of MySQL. He highlighted the reasons “Why did Sun by MySQL?” including most popular, most dynamic and people with the Freedom to work anywhere.

Three points I would like to re-iterate for the community.

The MySQL Forge contains the Worklogs, i.e. what is going to be developed in current & future versions and the status of tasks in next version, say in review, in development, completed development etc. This is important because this gives exposure to the world what MySQL are indeed working on.
• The MySQL University each Thursday gives a free session that includes from an expert audio and visual slides as well as the ability to ask questions via IRC. I’ve attended one of these before and I’d like to see this continue to improve and grow.
• MySQL is open and values external contributions from the community. My new co-worker Paul creator of PBXT highlights this. I hope with additional time within the MySQL Community I can help to contribute and lobby for greater differentiation of MySQL Community and MySQL Enterprise in favor of the Community.

Giuseppe did however not talk about the MySQL SandBox as scheduled. I was even prepared having wanted to use his software previously. I’d downloaded and was all ready. I did manage to get a one-on-one lesson which I found most useful, and a few points of enhancements to consider.

I didn’t know there were so many MySQL people in the area, the following attending tonight. Lenz, Giuseppe, Kai V, Kay, Jan, Ulf W and Georg whom I’d not met before. Most I’ll see next week at the MySQL Users Conference.

I had need to show the official MySQL business shirt to somebody, so I did a Google Search on “mysql conference photos” (without the quotes) as I know I’ve seen photos of staff at the conference online before. Google Search Link

To my utter surprise, Google provided 3 image search results. Two of them were of Paul McCullagh, CTO of PrimeBase Technologies. Not only was this unexpected, the first image of Paul was actually from my own Blog. Go figure how I managed that Search Engine Optimization.

In reviewing some of my notes I thought it was relevant to ensure this small MySQL gem is documented for my history purposes.

Historically I’ve seen people do a complex SELECT statement twice, first to get a COUNT(*) and then to retrieve the data, perhaps in a paginated format. In MySQL you can combined both SELECT statements into 1 SQL. You still need a second SQL statement, but it is less impactful to your system.

Here is how?

mysql> select sql_calc_found_rows * from mysql.user limit 2;
...
2 rows in set (0.01 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

The people, plain and simple.

I encourage you, if you are not part of the local MySQL community, then why not? Find a local MySQL Meetup group, attend the MySQL Conference, get online to IRC @ freenode#mysql, subscribe to some lists or read the forums. I didn’t meet these people being an observer, but part of the community.

As I wrote recently with some vacation time and the need to pick the brain of some experts I organized a quick trip to Germany to specifically visit Jan Kneschke and Paul McCullagh to improve my MySQL skills in certain areas on my own time (something I’d find harder to want to do at Sun when they owned all IP & copyright even during my personal time, so what incentive is there?)

As with the best laid plans of mice and men, the mention a few weeks earlier of skiing (my number one passion) and the chance to do this with Michael Zinner in Austria was too much a golden opportunity to miss, so this was added to my trip.

But it didn’t stop there. These were my plans yet news of my visit brought out others to want to see me during my visit. With Jan, additional events organized included my chance to see Kai Voigt, and then Jens Bollmann and Ulf Wendel. With Paul, lunch was organized with Lenz Grimmer. This is one beauty of a distributed company such as MySQL, people in lots of different places and something I am most glad that will not be lost just simply because I’m no longer an employee.

In the past year I’ve spent quality time with other MySQL Community people and also not just from MySQL. This past twelve months has included time with Sheeri Kritzer Cabral in Boston many times (including her wedding), Jay Pipes in Columbus (including also going there for Thanksgiving), Chad Miller in Orlando (even just to visit one day to see a shuttle launch), Gary Whizin in Santa Cruz, Baron Schwartz in Charlottesville and of course Farhan Mashraqi in New York. It’s great to get to know people better personally, to meet spouses and partners and to just consider life in general. People and their own goals and dreams are far more significant then just our own careers.

During my time at MySQL I’ve also made friends with several clients and have spent time with them in varying locations in the US including New York, Jacksonsville, Chicago and San Francisco.

In the past week following a request for any Linked In recommendations of my work, I’ve exceeded my goals of 25, and I’m now at 31 of 166 contacts, many from members of the MySQL community and clients.

I’m sure 2008 will be no different. Any offers for me to visit are always most welcome and to live by my motto “Life is Short. Live Life. Life is an Adventure”.

I wanted to follow up my earlier post My Ideas for MySQL Camp III with some of motivations. Luckily, good friend and colleague Jeremy literally took all of 15 mins to respond to my post with “So, I see your vision for the event itself. What’s your vision for the results of the event?” (Unfortunately with 150+ draft posts, it’s taken some time to complete my reply).

The present differentiation of MySQL Community and MySQL Enterprise in my eyes is a joke. Now before I start or re-start more then already one flame war let me first talk about MySQL Enterprise. MySQL Enterprise is a great and necessary product offering by MySQL Inc. It serves essential services to essential customers with features such as commercial 24×7 support, MySQL Enterprise Monitor which is an excellent start when clients have nothing (and boy I’ve seen more then my share of these), monthly updates and certified binaries.

My gripe is not with MySQL Enterprise, but with little to nothing that MySQL has done with MySQL Community. Take these simple facts.

MySQL Community and MySQL Enterprise were split at 5.0.27 in October 2006.
Only one contribution was added to the Community side, SHOW PROFILE in 5.0.37. What was to be a start of hopefully more creative contributions, suddenly stopped and has not changed since. My only thought could be, heaven forbid you now change the scope of functionality in 5.0 between Community and Enterprise, but wasn’t that the point?

Ok, new features change the scope of the base feature set. New features may make the product more unstable. Community people get this. The present release cycle process with the differentiation is a joke. You can’t add any features to 5.0, you can’t add any to 5.1 for like the past year. I’d go so far as to say, ZERO community contributions submitted even now would make 6.0. We don’t want to draw from sales and marketing features of Falcon and Online Backup.

MySQL Community is an essential product, it’s the only choice when MySQL Enterprise is not an option.

So to answer Jeremy’s question. What’s your vision for the results of the event?

1. To personally gain a better in-depth process of understanding the MySQL code, debugging, enhancing and contributing to MySQL
2. To see others gain the same skills, knowledge and appreciation.
3. To see better documentation of the process (A goal of the MySQL Community team)
4. To see the length and breath of creative input increase
5. To ignite some urgency and pressure to actually see MySQL Community differentiate to the benefit of the community.
6. To simply have fun, hack code and enjoy it with others

Since drafting this response some time ago, Sun has acquired MySQL, MySQL 5.1 still remains in RC, and we now have a beta of Falcon in an alpha of 6.0 (which I don’t get). With the MySQL Conference happening in a few weeks, I’ll be keen to know what’s going to happen with the MySQL Community differentiation and whether any information will indeed be forthcoming.

I plan to revisit this topic in the next 3 to 6 months to see if anything has actually changed as a result of Sun. We will just have to wait patiently.

In the past few months I’ve not had to look for a job, they look for me. If your a well qualified MySQL DBA there are many opportunities, I’ve had so many of these offers in the past 12 months I’ve lost count. However a DBA job would not offer the job satisfaction I’m seeking in my career goals now in 2008.

I’ve been able to consider 4 genuine positions in just the past few weeks, and indeed my new employer Primebase Technologies was someone I’d never considered working for, and indeed they had never considered me until a chance lunch meeting less then one month ago.

I did seriously consider working with Paul Vallee and the team at Pythian. Pythian looks for the best people from their respective levels of technology expertise, whether a college graduate or a 2 decade RDBMS veteran. This company has a great work ethic and a strong commitment to a 40 hour work week. With offices in multiple countries and a growing business, and a prominent client list as seen with the Pythian Web site even reviewing this company before talking to anybody provides a lot of good information.

I’ve followed Pythian closely since good friend MySQL She-BA Sheeri Kritzer Cabral was considering job opportunities last year. I was very impressed during this process. During recent vacation I took the time to visit Pythian, to speak personally with a number of people, from the CEO to potential fellow colleagues, to see the work environment, to have lunch and after work drinks. I invested more time in interviewing this potential company then they did in vis-versa, a principle I’d recommend to anybody for any future positions anywhere.

Today I read in How to become CEO, “Hire the best people. attract, motivate, train and reward the best people“. A good principle and one I know is at the heart of this company. Pythian is a company and a partnership I’ll be ensuring in the future remains strong. With a team of 60+ DBA’s across MySQL, Oracle and SQL Server they provide a dedicated service that MySQL (prior to acquisition) simply could not provide for market demand.

Comments on this site reflect the personal opinion of the author and may not reflect the opinion of any present or past employers.

I’ve had a lot of feedback from my resignation at MySQL. A lot positive, a lot of shock, even some avid discussion on why, and trying for me to reconsider my decision. Roland my evil twin (it’s a funny story), really challenging me which I very much appreciated, ensuring I had considered the multiple paths before me. I am a man of my own convictions, so reversing my decision was simply not an option on principle.

So what do I want in an ideal working environment. What was lacking, that I could not see and that I’m seeking. Here are two key points.

• “Continual improvement, 1% a day, review in 3 months.” This includes a commitment from the entire team, and a system to contribute, discuss, plan and see results. Something I’m a most firm believer of and will be ensuring is in place in my next position where I will have the responsibility.
• “Your employees are your most valuable customers”. This goes without saying. If you don’t treat your employees as well as your best customers they leave, often people however lower there expectations and time lesses people’s goals and desires.

One book I’m reading now on the opening introduction also sums my goals and desires well.

“Having ambition to better yourself, to be a contributor, to make a difference, to grow professionally, to be more successful.” – How to Become CEO – Jeffrey Fox

This sums we up well right now in 2008.

It’s often fun to review what you said previously.

Pride – August 11, 2006.
Your Ideal Job Requirements? – August 27, 2006.
If you can’t beat them, join them! – October 7, 2006.