One of my biggest pet peeves is when people name a host after a product, say 'mysql-prod-101', then later they decide to switch from MySQL to Oracle. I worked in an environment where a number of hosts contained 'tomcat' in the name but they were all running weblogic. Better to name based on the role, as you say, so the mysql-prod-101 would be better named db-prod-101. However, I've come to think that nowadays with configuration managment it's not really necessary to give hostnames at all. People usually don't like that suggestion when I bring it up, but if you are using something like puppet's 'facter' to run actions only on hosts that match certain 'facts', what do you really need hostnames for?
Edit: I just reread your paragraph about using positional hostnames. This seems like a good use, because the main problem I see with role based hostnames is that hosts can take on multiple roles. But in general the hostname seems unimportant if you are tagging your hosts with roles.
Ya, all our dev tools servers are named after the product they're running. (Well, most of them; one is named after a mythological being; one is named after its function.) As a user, this is frustrating because rather than remember what function I'm trying to access (e.g. mail, wiki, bugs, etc.) I have to remember what Atlassian decided to name their product that implements the function I want.
(Fortunately some entires in /etc/hosts + a local Apache server set up with RedirectRules fixes this annoyance, at least for myself…)
Edit: I just reread your paragraph about using positional hostnames. This seems like a good use, because the main problem I see with role based hostnames is that hosts can take on multiple roles. But in general the hostname seems unimportant if you are tagging your hosts with roles.