PlanetMysql.ru – информация о СУБД MySQL

I found I never published this post as it was sitting in my drafts few months now — it was written in 13th February, 2010. I’m publishing it without any changes.

I learn therefore I am!

I’ve just wrote few bits about learning a new technology and after skimming through my Google Reader, I noticed a great post by Chen Shapira — Deliberate Practice. That’s reminded me about another aspect of learning that I didn’t mention — learning is a continuous process.

There are two aspects…

• No matter how good I am and how much I know, my knowledge and expertize become outdated relatively quickly these days unless I keep up with the new stuff. Unfortunately, there is so much new technologies these days that I have to be very selective on what I want to follow which is a big challenge in itself. On the other hand, I’d rather be challenged than bored. As DBA’s we are luckier than Developers — their world changes much faster than ours.
• I forget things I don’t use/do/read about/think about regularly. Refreshing my “old” memory is a must do. This is why I think Chen’s idea of deliberate practice is so great.

Some people are better on keeping the existing tools sharp, while others succeed on the lookout for new ways and techniques. To truly excel, we need to master both.

This photo (it was taken 5 years ago in Australia in one of Sydney’s “wild” parks) reminds me of this continuous learning process — perhaps, it will do the job for you as well.

A MySQL user group member saw that I use Poderosa as my ssh-on-Windows tool, and asked why I did not use PuTTY. My response was that I like having tabbed windows and hate having to keep opening another PuTTY program every time I want to open another connection. With Poderosa I can open a new connection with Alt-N, and I can even connect directly to Cygwin with an icon.

But Poderosa is not the tool I wanted to mention….Another user group member mentioned PuTTY Connection Manager. It wraps around PuTTY and gets the existing saved connections, makes a nicely tabbed browsing window where you can open sessions by double-clicking the connections, which are now listed on the right-hand side.

See screenshot below:

I have not played with other features such as sending a command to multiple windows, but even just having this is a HUGE win.

The Beacon Pattern:
- This is a “Get out of the business” pattern
- Identify an oft-occurring and annoying task
- Automate and document it to the point of being able to hand it off to someone far less technical

Example:
- System admins were being put in charge of scheduling rooms in the building
- They wrote a PHP web application to help them automate the task
- They refined the app, documented how to use it, and handed it off to a secretary
- They have to maintain the app, but it’s far less work.

The Community Pattern:

- Prior to launch of a new service, create user documentation for it.
- Point a few early adopters at the documentation and see if they can use the service with minimal support
- Use feedback to improve documentation, and the service
- Upon launch, create a mailing list, forum, IRC channel, or Jabber chat room and ask early adopters to help test it out.
- Upon launch, your early adopters are the community, and they’ll tell new users to use the tools you’ve provided instead of calling you.

Example:
- A beowulf cluster for an academic department
- Documented like crazy, early adopters were given early access to the cluster (demand was high)
- Crated a mailing list, early adopters were added to it with their consent, functionality was tested with them.
- Email announcing launch mentioned the early adopters in a ‘thank you’ secion, and linked them to their mailing list.

The DRY pattern
DRY = Don’t repeat yourself
Identify duplicate code in your automation scripts
Put subroutines that exist in an include file, and include them in your scripts.

Example:
- “sysadmin library”
- /var/lib/adm/.*pl
- Elapsed time and # of lines to script a task for which the library was useful plunged dramatically
– new tasks were thought up that were not considered before but were obvious now (ie, users that want to change their username)
– migrating to new services became much easier

The Chameleon Pattern
- Identify commonalities among your services
- Leverage those to create “Chameleon” servers that can be re-purposed on the fly
- Abstract as much of this away from the physical hardware
- Doesn’t need to involve virtualization, though it’s awfully handy if you can do it that way.
[this one is a bit harder to do with MySQL config files]

Example:
[puppet/cfengine were mentioned...]
ldapconfig.py – more than a script: a methodology

- But isn’t installing packages you don’t need bad? Depends on the package….ie, gcc is bad for enterprise

“Junior annoynances”

Terminal issues

Junior:
open terminal, login to machine1
think issue is with machine2, talks to machine1.
log out of machine1
log into machine2

Senior:
opens 2 terminals each of machine1 and machine2 to start

Junior:
networking issue ticket arrives
logs into server
runs tcpdump

Senior:
networking issue ticket arrives
logs into server
looks at logs

“Fix” vs. “Solution” ie “taking orders”
Junior will try fix a problem, senior will try to figure out what the problem is. ie, “I need a samba directory mounted under an NFS mount” a junior admin will try to do exactly that, a senior admin will ask “what are you trying to do with that?” because maybe all they need is a symlink.

Fanboyism
Signs you might be a fanboy:
- Disparaging users of latest stable release of $THING for not using the nightly (unstable) build which fixes more issues
- Creating false/invalid comparisons based on popular opinion instead of experience/facts
- Going against internal standards, breaking environmental consistency, to use $THING instead of $STANDARD (but this is also how disruptive technology works)
- Being in complete denial that most technology at some point or another stinks.
- Evaluating solutions based on “I like” instead of “we need” and “this does”.

The Beacon Pattern:
- This is a “Get out of the business” pattern
- Identify an oft-occurring and annoying task
- Automate and document it to the point of being able to hand it off to someone far less technical

Example:
- System admins were being put in charge of scheduling rooms in the building
- They wrote a PHP web application to help them automate the task
- They refined the app, documented how to use it, and handed it off to a secretary
- They have to maintain the app, but it’s far less work.

The Community Pattern:

- Prior to launch of a new service, create user documentation for it.
- Point a few early adopters at the documentation and see if they can use the service with minimal support
- Use feedback to improve documentation, and the service
- Upon launch, create a mailing list, forum, IRC channel, or Jabber chat room and ask early adopters to help test it out.
- Upon launch, your early adopters are the community, and they’ll tell new users to use the tools you’ve provided instead of calling you.

Example:
- A beowulf cluster for an academic department
- Documented like crazy, early adopters were given early access to the cluster (demand was high)
- Crated a mailing list, early adopters were added to it with their consent, functionality was tested with them.
- Email announcing launch mentioned the early adopters in a ‘thank you’ secion, and linked them to their mailing list.

The DRY pattern
DRY = Don’t repeat yourself
Identify duplicate code in your automation scripts
Put subroutines that exist in an include file, and include them in your scripts.

Example:
- “sysadmin library”
- /var/lib/adm/.*pl
- Elapsed time and # of lines to script a task for which the library was useful plunged dramatically
– new tasks were thought up that were not considered before but were obvious now (ie, users that want to change their username)
– migrating to new services became much easier

The Chameleon Pattern
- Identify commonalities among your services
- Leverage those to create “Chameleon” servers that can be re-purposed on the fly
- Abstract as much of this away from the physical hardware
- Doesn’t need to involve virtualization, though it’s awfully handy if you can do it that way.
[this one is a bit harder to do with MySQL config files]

Example:
[puppet/cfengine were mentioned...]
ldapconfig.py – more than a script: a methodology

- But isn’t installing packages you don’t need bad? Depends on the package….ie, gcc is bad for enterprise

“Junior annoynances”

Terminal issues

Junior:
open terminal, login to machine1
think issue is with machine2, talks to machine1.
log out of machine1
log into machine2

Senior:
opens 2 terminals each of machine1 and machine2 to start

Junior:
networking issue ticket arrives
logs into server
runs tcpdump

Senior:
networking issue ticket arrives
logs into server
looks at logs

“Fix” vs. “Solution” ie “taking orders”
Junior will try fix a problem, senior will try to figure out what the problem is. ie, “I need a samba directory mounted under an NFS mount” a junior admin will try to do exactly that, a senior admin will ask “what are you trying to do with that?” because maybe all they need is a symlink.

Fanboyism
Signs you might be a fanboy:
- Disparaging users of latest stable release of $THING for not using the nightly (unstable) build which fixes more issues
- Creating false/invalid comparisons based on popular opinion instead of experience/facts
- Going against internal standards, breaking environmental consistency, to use $THING instead of $STANDARD (but this is also how disruptive technology works)
- Being in complete denial that most technology at some point or another stinks.
- Evaluating solutions based on “I like” instead of “we need” and “this does”.

I am attending the Professional IT Community Conference – it is put on by the League of Professional System Administrators (LOPSA), and is a 2-day community conference. There are technical and “soft” topics — the audience is system administrators. While technical topics such as Essential IPv6 for Linux Administrators are not essential for my job, many of the “soft” topics are directly applicable and relevant to DBAs too. (I am speaking on How to Stop Hating MySQL tomorrow.)

So I am in Seeking Senior and Beyond: The Tech Skills That Get You Promoted. The first part talks about the definition of what it means to be senior, and it completely relates to DBA work:
works and plays well with other
understands “ability”
leads by example
lives to share knowledge
understands “Service”
thoughtful of the consequences of their actions
understands projects
cool under pressure

Good Qualities:
confident
empathetic
humane
personal
forthright
respectful
thorough

Bad Qualities:
disrespective
insensitive
incompetent
[my own addition - no follow through, lack of attention to detail]

The Dice/Monster Factor – what do job sites see as important for a senior position?

They back up the SAGE 5-year experience requirement
Ability to code in newer languages (Ruby/Python) is more prevalent (perhaps cloud-induced?)

The cloud allows sysadmin tasks to be done by anyone…..so developers can do sysadmin work, and you end up seeing schizophrenic job descriptions such as

About the 5-year requirement:
- Senior after 5 years? What happens after 10 years?
- Most electricians, by comparison, haven’t even completed an *apprenticeship* in 5 years.

Senior Administrators Code
- not just 20-line shell scripts
- coding skills are part of a sysadmin skill
- ability to code competently *is* a factor that separates juniors from seniors
- hiring managers expect senior admins to be competent coders.

If you are not a coder
- pick a language, any language
- do not listen to fans, find one that fits how you think, they all work…..
- …that being said, some languages are more practical than others (ie, .NET probably is not the best language to learn if you are a Unix sysadmin).

Popular admin languages:
- Perl: classic admin scripting language. Learn at least the basics, because you will see it in any environment that has been around for more than 5 years.

- Ruby: object-oriented language for people who mostly like Perl (except for its OO implementation)

- Python: object-oriented language for people who mostly hate Perl, objects or no objects. For example, you don’t have to create a String object to send an output.

But what if you do not have time to learn how to program?

- senior admins are better at managing their time than junior admins, so perhaps managing time
- time management means you’ll have more time to do things, it doesn’t mean all work work work.
- Read Time Management for System Administrators – there is Google Video of a presentation by the author, Tom Limoncelli.

Consider “The Cloud”
- starting to use developer APIs to perform sysadmin tasks, so learning programming is good.
- still growing, could supplant large portions of datacenter real estate
- a coder with sysadmin knowledge: Good
- a sysadmin with coding knowledge: Good
- a coder without sysadmin knowledge: OK
- a sysadmin with no coding interest/experience: Tough place to be in

Senior Admins Have Problems Too
Many don’t document or share knowledge
Maany don’t do a good job keeping up with their craft
Cannot always be highlighted as an example of how to deal with clients
Often reinvent the wheel – also usually there is no repository
Often don’t progress beyond the “senior admin” role

….on the other hand…..
cynicism can be good…..

Advice:
learn from the good traits
observe how others respond to their bad traits
think about how you might improve upon that
strive to work and play well with others, even if you don’t have a mentor for good/bad examples.

Now he’s going into talking about Patterns in System Administration….

I am attending the Professional IT Community Conference – it is put on by the League of Professional System Administrators (LOPSA), and is a 2-day community conference. There are technical and “soft” topics — the audience is system administrators. While technical topics such as Essential IPv6 for Linux Administrators are not essential for my job, many of the “soft” topics are directly applicable and relevant to DBAs too. (I am speaking on How to Stop Hating MySQL tomorrow.)

So I am in Seeking Senior and Beyond: The Tech Skills That Get You Promoted. The first part talks about the definition of what it means to be senior, and it completely relates to DBA work:
works and plays well with other
understands “ability”
leads by example
lives to share knowledge
understands “Service”
thoughtful of the consequences of their actions
understands projects
cool under pressure

Good Qualities:
confident
empathetic
humane
personal
forthright
respectful
thorough

Bad Qualities:
disrespective
insensitive
incompetent
[my own addition - no follow through, lack of attention to detail]

The Dice/Monster Factor – what do job sites see as important for a senior position?

They back up the SAGE 5-year experience requirement
Ability to code in newer languages (Ruby/Python) is more prevalent (perhaps cloud-induced?)

The cloud allows sysadmin tasks to be done by anyone…..so developers can do sysadmin work, and you end up seeing schizophrenic job descriptions such as

About the 5-year requirement:
- Senior after 5 years? What happens after 10 years?
- Most electricians, by comparison, haven’t even completed an *apprenticeship* in 5 years.

Senior Administrators Code
- not just 20-line shell scripts
- coding skills are part of a sysadmin skill
- ability to code competently *is* a factor that separates juniors from seniors
- hiring managers expect senior admins to be competent coders.

If you are not a coder
- pick a language, any language
- do not listen to fans, find one that fits how you think, they all work…..
- …that being said, some languages are more practical than others (ie, .NET probably is not the best language to learn if you are a Unix sysadmin).

Popular admin languages:
- Perl: classic admin scripting language. Learn at least the basics, because you will see it in any environment that has been around for more than 5 years.

- Ruby: object-oriented language for people who mostly like Perl (except for its OO implementation)

- Python: object-oriented language for people who mostly hate Perl, objects or no objects. For example, you don’t have to create a String object to send an output.

But what if you do not have time to learn how to program?

- senior admins are better at managing their time than junior admins, so perhaps managing time
- time management means you’ll have more time to do things, it doesn’t mean all work work work.
- Read Time Management for System Administrators – there is Google Video of a presentation by the author, Tom Limoncelli.

Consider “The Cloud”
- starting to use developer APIs to perform sysadmin tasks, so learning programming is good.
- still growing, could supplant large portions of datacenter real estate
- a coder with sysadmin knowledge: Good
- a sysadmin with coding knowledge: Good
- a coder without sysadmin knowledge: OK
- a sysadmin with no coding interest/experience: Tough place to be in

Senior Admins Have Problems Too
Many don’t document or share knowledge
Maany don’t do a good job keeping up with their craft
Cannot always be highlighted as an example of how to deal with clients
Often reinvent the wheel – also usually there is no repository
Often don’t progress beyond the “senior admin” role

….on the other hand…..
cynicism can be good…..

Advice:
learn from the good traits
observe how others respond to their bad traits
think about how you might improve upon that
strive to work and play well with others, even if you don’t have a mentor for good/bad examples.

Now he’s going into talking about Patterns in System Administration….

For the DIY folks out there!

You're installing MySQL using the Generic Linux Binary tar ball? If you do, you'll need to create a system user and group using useradd and groupadd. However, you would like to use the standard system IDs. No problem, Red Hat defines their standard system users and groups in their manual respectively as 27 and 27.

Here are the commands to create the system user mysql and its group mysql:

 shell> groupadd -r -g 27 mysql
 shell> useradd -u 27 -g 27 -r -d /var/lib/mysql -s /bin/bash mysql

We just had a booboo in one of our internal systems, causing it to not come up properly on reboot. The actual mishap occurred several weeks ago (simple case of human error) and was in itself a valid change so monitoring didn’t raise any concerns. So, as always, it’s interesting and useful to think about such events and see what we can learn.

Years ago, but for some now still, one objective is to see long uptime for a server, sometimes years. It means the sysadmin is doing everything right, and thus some serious pride is attached to this number. As described only last week in Modern Uptime on the Standalone Sysadmin blog, security patches are a serious issue these days, and so (except if you’re using ksplice sysadmin quality has become more a question of when you last did security updates (which might have involved a reboot), rather than the uptime number.

But I think the aforementioned booboo illustrates an additional aspect, I think it might be quite sensible to reboot a system every so many weeks (we can debate the interval and it may differ per system and situation) since in the end it will be rebooted some time, and that may show trouble at an inconvenient time. Better to test and fine out when you’re there.

Of course this also has consequences for either your external uptime (scheduled maintenance slots with outages), or thinking about your architecture differently. Can you take out any individual system in your infrastructure without some service getting interrupted? It’s doable, but not necessarily with some traditional approaches or equipment that carries the “enterprise” label.

Food for thought! As always, comments welcome.

One thing you learn when you start to manage several servers is that life is easier if things are done the same way. I manage a few MySQL database instances on a few different hosts and here are a list of some things which I think could do with improvement. Note these views are from a UNIX point of view and for Windows MySQL DBAs many of the comments may not be appropriate. Also I’m not talking here about the administration of a single database, but the problems when you administer multiple instances.

Issues

First lets talk about the issues I notice in my day to day usage of MySQL. Then I’ll try and come up with some suggestions as to how these issues might be resolved.

• Give up root earlier and expect to run as a non-root user. While it’s true that the mysqld process normally runs as the mysql user it’s also true that to be a MySQL DBA is almost impossible at the moment if you don’t have root access. To be clearer, at least that’s the case of most packaged UNIX versions of MySQL.
• Using a package manager can save you a lot of time, but only if the packages cover your requirements. MySQL packages at least for RedHat work fine, but not if you intend to run multiple versions of MySQL, multiple mysqld instances or as stated above if you don’t want to manage MySQL as the root user.
• Make it possible to manage multiple mysql instances the same way as single mysql instances. Currently that’s not possible and as such makes you choose one way or the other and perhaps differently on different servers. Not good.

Possible Solutions

When making changes it’s important to decide how to make that change. You can make things backwards compatible or you can from a certain software version change behaviour.  Currently MySQL 5.1 is GA which means that any changes if they were to take place would be not be until at least 6.0 as 5.4 seems pretty much on the way to be the next “GA” version shortly (end of the year)?

So here are my thoughts on making things “better”.

• Give up root earlier. MySQL startup scripts should be able to run as a non-root user and work roughly the same. MySQL uses port 3306 so does not need to run as root to bind to this port, so there’s no real need to do anything as root. If that means running 2 startup scripts rather than one then so be it. Both Oracle and Sybase do this, and DB2 if I remember correctly.
• Make mysqld_safe optional. While the idea of mysqld_safe is great, it restarts your server if it crashes, and it may have been necessary in the past, I’m not so convinced that it should be part of the default startup script. Currently I believe this needs to run as root. Again make it able to run as a non-root user, but also make the startup script use it optionally. If you don’t monitor a msyqld instance extensively you may never even know mysqld has crashed, as often the startup time may be pretty quick.
• Make mysqld_safe optionally notify the sysadmin or dba if there’s been a crash. This means you don’t need to monitor in more sophisticated ways if there’s a problem. An email is fine, or if not provide hooks for talking to an external program.
• Make it possible to install multiple MySQL packages at the same time. You can do this with the kernel and people don’t complain. Make it the same with MySQL. The focus of this is mainly for the server side where it may be that you want to upgrade. You can’t install a MySQL 5.0 and MySQL 5.1 rpm at the same time as the package’s file locations collide. That does mean doing things differently to how things are done now but it also means it’s much easier to run versions of MySQL on the same box. Once you’ve finished with a version and are no longer using it then the package can be erased as normal. There are packaging solutions to make sure that at least from the client side a specific version is the main version used, even if you may want to use a different version for specific purposes.
• Make it possible to run multiple mysqld instances at the same time. In theory this is possible now, with mysqld_multi.
  • However this is not the default behaviour and also requires you to change the init script. It would seem better to make the init script “multi-instance” aware and if configured appropriately this would be the default behaviour.
  • Also if supporting multiple instances ensure that each instances configuration files (my.cnf) are not stored in a common /etc/my.cnf file but each in a separate location. This makes it much easier move the instance together with it’s configuration file about.
  • Support for each instance the ability to optionally start the instance. Something like /etc/oratab comes to mind, where instance name, instance my.cnf, optional startup, optional use of mysqld_safe, and user to run under are configured.
  • Make it possible from the init script to start/stop all instances or an instance by name. Names are so much easier to understand than numbers!
• Maintain backwareds compatibility. This keeps everyone happy and makes the transition smoother.  MySQL has a lot of history so making big changes in an incompatible way mean that people get confused and upset. If the changes suggested above can be done in a way which allows current behaviour to be continued, but the new behaviour to be “enabled” with very little effort, then people who like me have to manage several boxes can take advantage of this new functionality.

It can be argued that if you write your own scripts that none of the above is necessary. It can be argued that if you build MySQL from source then you can install things how ever you want. If can be argued that it’s not MySQL’s problem, but the sysadmin administrator. It can be argued that every site is different. However to be honest while some of the smaller details may indeed be different and the scale may change in the end, one MySQL server is pretty much the same as the others. If MySQL, or Sun, or Oracle now make it easier to manage the boxes the way we need, and to do this consistently, and cover many of the use cases we won’t all be re-inventing the wheel. That’s what it seems we all do, or if not we put up with inconsistent setups on different servers that means we spend less time on tending the database and more on doing mundane things such as get things running the way we want.

I started using MySQL on a single server with just one instance running. That was some time ago. Others are likely to follow my steps and then run into some of the issues I am mentioning, or have come across this some time ago. The ideas I mention above are I think solvable but it would be much better if we could come up with a common solution rather than me implement my own or you implement yours.

So am I the only one who thinks these changes would be helpful, and am I missing some other points? Would love to know what you think.

I started my company about a year ago, but I’ve been doing consulting for a long time. In fact, my first job in the IT industry was working for a consulting firm. Before that, starting as far back as grade school, I was involved in a lot of volunteer civic and community service activities. I admire companies who get involved in their communities, or even outside of their communities, wherever help is needed.

As part of my business plan, I’ve put in place a policy of accepting one pro-bono consulting project per year. So far, I haven’t gotten any requests for free consulting work, so here’s my public shout out to let you know what types of services are available:

1. Speaking or Training. My specialties are things like advanced Linux administration and SQL, but I’m perfectly capable of delivering content for people who just need to know how the internet works, or want to know more about social media.Training, funny enough, has been the bulk of my business for the past year.

2. I can help with MySQL performance tuning on *nix systems, including finding hotspots related to the design of the database itself, or how your application code interacts with the database. If it happens that your MySQL server is performing poorly due to an underpowered system, I can also pinpoint which resource is dragging on the performance of your database.

3. If you just need random scripts written to perform *nix system administration tasks, I can consult with you about the requirements and write them for you. Note that while I can script in several languages, my preference for anything longer than 40 lines of code is Python.

4. I can build PC’s, install networks, set up firewalls and wireless routers, and all of the normal “office IT” functions, but note that my consulting is Linux consulting. I don’t work with Windows (well, I do, but not for free)

5. If there’s some other thing you’ve seen me blog about here, chances are I’ll be willing to perform a pro-bono consulting engagement to do it for you, or show you how to approach a problem, a large project, a migration, automation, monitoring, security or whatever.

Unless you happen to live within commuting distance to Princeton, NJ, work will be done remotely

Please email your request to jonesy at owladvisors dot com. Include your organization’s name, your contact info, and as much detail about the project and what your organization does as possible. The decision of which project to take on will be based solely on the information in your request!