If you are using the Solaris dhcp server, stop now. Save yourself hours of time of trying to figure out pntadm, dhtadm, and just use ISC DHCP on Solaris. It’s very simple, painless, and you will be glad you did. The only problem I ran across was compiling the ISC dhcp straight off the website, it compiled fine, but I ran into issues where it wouldn’t see packets properly. So I just used the one off Blastwave instead. Here’s how to get a jumpstart going with ISC:

- CD setup

Copy the CD contents to an existing directory:

# /cdrom/Solaris_11/Tools/setup_install_server /jumpstart/solx86b81

Set the /jumpstart directory shared NFS:

# zfs set sharenfs=ro,anon=0 pool/jumpstart

- Jumpstart configuration

Create /jumpstart/configs/sysidcfg, /jumpstart/configs/any_machine and /jumpstart/configs/rules:

sysidcfg:

system_locale=C
timezone=US/Central
terminal=xterms
security_policy=NONE
root_password=yTUdfabsalkjfE
timeserver=localhost
network_interface=primary
{dhcp
protocol_ipv6=no}
name_service=NONE
nfs4_domain=dynamic
service_profile=limited_net

any_machine:

install_type initial_install
system_type standalone
partitioning default
cluster SUNWCXall
fdisk all solaris all
partitioning explicit
filesys rootdisk.s0 20000 /
filesys rootdisk.s1 20000 swap
filesys rootdisk.s7 free /zfs

rules:

any - - any_machine -

Run /jumpstart/configs/check to create the rules.ok file:

# /jumpstart/configs/check

- Client setup

Run the add_install_client tool, this creates everything in /tftpboot:

# /cdrom/Solaris_11/Tools/add_install_client -d -e 06:00:00:00:00:00 -s 192.168.0.177:/jumpstart/solx86crossbow -c 192.168.0.177:/jumpstart/configs -p 192.168.0.177:/jumpstart/configs -t /jumpstart/solx86crossbow/boot i86pc

-d : it will be a dhcp client
-e : mac address of client
-s : ip and directory of where cd is located
-c : ip and directory of where rules file is located
-p : ip and directory of where the sysidcfg is located
-t : path where the boot image is located

- ISC DHCP configuration

The ISC DHCP 4 off of the ISC website compiles fine and runs fine, but doesn’t see DHCP requests properly. This is due to the Solaris DLPI issue described here. There is a fix to uncomment a line in the code but then it doesn’t compile properly.

The ISC dhcp that comes out of Blastwave is version 3 and it works fine seeing requests and sending them.

- Configuration

Configuration of ISC DHCP is very simple and painless, especially if you are used to Solaris DHCP. Most of the options in the configuration file are self explanatory and you don’t even need to look up what they mean (if you are familiar with how DHCP works).

Here’s the dhcpd.conf for jumpstart (values will need to be changed depending on your network):

option domain-name-servers 192.168.0.1;
default-lease-time 1000;
max-lease-time 10000;
allow bootp;
allow booting;

ddns-update-style none;
ignore unknown-clients;

authoritative;

option space SUNW;
option SUNW.root-mount-options code 1 = text;
option SUNW.root-server-ip-address code 2 = ip-address;
option SUNW.root-server-hostname code 3 = text;
option SUNW.root-path-name code 4 = text;
option SUNW.swap-server-ip-address code 5 = ip-address;
option SUNW.swap-file-path code 6 = text;
option SUNW.boot-file-path code 7 = text;
option SUNW.posix-timezone-string code 8 = text;
option SUNW.boot-read-size code 9 = unsigned integer 16;
option SUNW.install-server-ip-address code 10 = ip-address;
option SUNW.install-server-hostname code 11 = text;
option SUNW.install-path code 12 = text;
option SUNW.sysid-config-file-server code 13 = text;
option SUNW.JumpStart-server code 14 = text;
option SUNW.terminal-name code 15 = text;
option SUNW.SbootURI code 16 = text;

subnet 192.168.0.0 netmask 255.255.255.0 {
range 192.168.0.100 192.168.0.250;
option subnet-mask 255.255.255.0;
option broadcast-address 192.168.0.255;
option routers 192.168.0.1;
}

group {
use-host-decl-names on;
vendor-option-space SUNW;

filename “pxegrub.I86PC.Solaris_11-1″;
next-server 192.168.0.177;
option SUNW.JumpStart-server “192.168.0.177:/jumpstart”;

host box {
hardware ethernet 00:e0:81:33:74:d4;
fixed-address 192.168.0.50;
option SUNW.sysid-config-file-server = “192.168.0.177:/jumpstart/configs/sysidcfg”;
}
}

The *filename* option may change between Solaris versions, so check your /tftpboot directory for the pxegrub image that exists in there.

- Starting ISC dhcp

ISC dhcpd has a few options and is run like:

# /opt/csw/sbin/dhcpd interface0

If you run it without interface0 it will run on all interfaces.

-d : debug mode
-cf : alternate configuration file
-lf : alternate lease file
-p : port (default 67)
-f : run in foreground
-q : don’t print out copyright message
-t : test configuration for correct syntax
-T : test lease file syntax

When dhcpd is started, it reads two files at startup: the dhcpd.conf configuration file, and the leases file. The lease file holds all of the dhcp leases it gives out and by default is at /var/db/dhcpd.leases. For the Blastwave installation, it is at /var/opt/csw/dhcp/dhcpd.leases. You may need to create this file first before starting dhcpd. The pid file for the daemon is at /var/opt/csw/dhcp/dhcpd.pid.

For debugging, the best way to run it is:

# /opt/csw/sbin/dhcpd -d -f -cf /path/to/dhcpd.conf

I’ve finally got around to releasing the second version of my SMFAlert program. I’ve had this done for a while, just been too busy to put it up. Here are the changes:

Download

Even though there aren’t DTrace probes for MySQL released yet, we can still get useful information from MySQL. DTrace has a pid provider which allows us to get into any function the program is executing and see it’s arguments. The only drawback is you have to go digging around in the source code to find out what you want to see. But thanks to guys like Brendan Gregg, they have already done some of the digging for us. Even if we want to go digging around ourselves, it’s really not that hard; you just have to get your feet wet. I will show some examples of this and how easy it is to hunt down your own functions.

First let’s start with functions that have already been dug up for us:

mysql_parse(thd, thd->query, length, & found_semicolon);

This is the function MySQL uses to parse a query. So all we have to do is trace this function through the pid provider and we get to see all the queries coming through. This shows arg1 as being the query, and we must copy it in to kernel land where DTrace works for it to see the string:

root@ferrari:~# dtrace -qn ‘pid$target:mysqld:*mysql_parse*:entry { printf(”%Y %s\n”, walltimestamp, copyinstr(arg1)) }’ -p `pgrep -x mysqld`
2007 Sep 27 10:04:35 select * from blah
2007 Sep 27 10:04:58 select * from tablenothere

Notice that this will show all queries, even if they aren’t successful. Now that we can trace queries, this can give us good information. For example we can see what queries are executed the most:

root@ferrari:~# dtrace -qn ‘pid$target:mysqld:*mysql_parse*:entry { @queries[copyinstr(arg1)] = count() }’ -p `pgrep -x mysqld`
^C

select * from blah 5
select * from tablenothere 10

You can’t get this kind of information from MySQL unless you write some kind of script to parse through the query log. If we know that there is a query being executed 1000 more times than the others, we could always try to get this one to cache. Now lets say we want to find out how long a query took to execute. The function mysql_execute_command does the actual execution of the queries so all we do here is subtract the entry and return timestamps of this function. The script shown below uses this:

root@ferrari:~# ./exactquerytimes.d -p `pgrep -x mysqld`
Tracing… Hit Ctrl-C to end.

Query: SELECT COUNT(*) FROM joe_visitors where upper(vs_browser) not like ‘%GOOGLE%’ and upper(vs_browser) not like ‘%GOOGLE BOT%’ and upper(vs_browser) not like ‘%BOT%’ and upper(vs_browser) not like ‘%MSN%’ and upper(vs_browser) not like ‘%MSNBOT%’ and upper(,
Time: 2.32

On the MySQL side, it showed this query being executed at 2.32 seconds as well:
1 row in set (2.32 sec).

This is awesome information because as of now MySQL doesn’t allow you to see a slow query that is less than 1 second (I believe this is a fix in MySQL 5.1). So with this, we can see not just slow queries, but all queries and how long they take to execute with their times.

Now let’s try this same query but I bumped my query_cache_size up to 50M:

The first try (won’t hit the cache):

root@ferrari:~# ./exactquerytimes.d -p `pgrep -x mysqld`
Tracing… Hit Ctrl-C to end.

Query: SELECT COUNT(*) FROM joe_visitors where upper(vs_browser) not like ‘%GOOGLE%’ and upper(vs_browser) not like ‘%GOOGLE BOT%’ and upper(vs_browser) not like ‘%BOT%’ and upper(vs_browser) not like ‘%MSN%’ and upper(vs_browser) not like ‘%MSNBOT%’ and upper(,
Time: 2.28

And the second try hits the cache but doesn’t show anything through DTrace. So this means a query that is served from the cache won’t show up in mysql_parse. Right now this probably doesn’t mean much, but as you learn more about how the internals of MySQL work, then troubleshooting becomes much easier down the road.

So far this has all been information that was provided. Now I will show how simple it is search through MySQL’s source and look at functions.

First we need to decide what to look for. Let’s say we want to find out every time that slow query is written to the slow query log. First we download the MySQL source code from http://www.mysql.com. Now we can search through the source code for ’slow query’:

root@ferrari:/export/home/derek/mysql-5.0.45# ggrep -ri ’slow query’ *

This turns up only a few source code files, one of them looking most obvious called log.cc with the expression “Write to the query log”. The MySQL code is very well commented so it makes searching really easy. Looking in this file, that comment is right above the function:

/*
Write to the slow query log.
*/
bool MYSQL_LOG::write(THD *thd,const char *query, uint query_length,
time_t query_start_arg)

It’s obvious that this function is the function that writes to the slow query log. Running this script below, looking for function LOG while a slow query is being inserted shows this function being executed with some weird characters around it:

root@ferrari:~# dtrace -F -n ‘pid$1:mysqld:*LOG*:entry {} pid$1:mysqld:*LOG*:return {}’ `pgrep -x mysqld`
dtrace: description ‘pid$1:mysqld:*LOG*:entry ‘ matched 126 probes
CPU FUNCTION
0 -> _ZN9MYSQL_LOG5writeEP3THD19enum_server_commandPKcz
0 -> _ZN9MYSQL_LOG5writeEP3THDPKcjl
0 < - _ZN9MYSQL_LOG5writeEP3THDPKcjl

The only thing bad about tracing MySQL through the pid provider is that these weird characters change between MySQL versions, so we can’t always trace for ‘_ZN9MYSQL_LOG5writeEP3THDPKcjl’ if we want it to work on other machines. We have to trace for *MYSQ*LOG*write* which slowquerycounts.d uses:

root@ferrari:~# ./slowquerycounts.d -p `pgrep -x mysqld`
Tracing… Hit Ctrl-C to end.

SELECT COUNT(*) FROM joe_visitors where upper(vs_browser) not like ‘%GOOGLE%’ and upper(vs_browser) not like ‘%GOOGLE BOT%’ and upper(vs_browser) not like ‘%BOT%’ and upper(vs_browser) not like ‘%MSN%’ and upper(vs_browser) not like ‘%MSNBOT%’ and upper(
3

As you can see DTrace can be very powerful even if we don’t have probes released yet, we just have to do a little extra work. Some of the information shown can be obtained from MySQL but using DTrace still provides a benefit because we don’t have to enable anything in the MySQL configuration, possibly making us restart the server. I’m providing these scripts in the MySQLDTraceKit.tar.gz. Hopefully in the near future we will have real MySQL probes.

I run this site on a smaller sized Accelerator (ah the beauty of root access). Because I have limited resources and memory, it’s essential to tune anything I can to use less. So last night I was taking a look at my MySQL slow query log to see what queries were taking the longest (most likely not using indexes properly). The only query that shows up really often is for my jVisitors plugin that tracks who visits the site:

select id, vs_ip, vs_session, vs_user_agent,
vs_country, vs_os, vs_os_ver, vs_browser, vs_browser_ver,
date_format(DATE_SUB(vs_date, INTERVAL 6 HOUR),’%a, %e/%m/%Y’) as vs_dte,
date_format(DATE_SUB(vs_date, INTERVAL 6 HOUR),’%h:%i:%s%p’) as vs_time
from joe_visitors
where upper(vs_browser) not like ‘%GOOGLE%’ and upper(vs_browser) not like ‘%GOOGLE BOT%’ and upper(vs_browser) not like ‘%BOT%’ and upper(vs_browser) not like ‘%MSN%’ and upper(vs_browser) not like ‘%MSNBOT%’ and upper(vs_browser) not like ‘%YAHOO%’ and upper(vs_browser) not like ‘%YAHOO! SLURP%’ and upper(vs_browser) not like ‘%ZYBORG%’ and upper(vs_browser) not like ‘%W3C%’ and upper(vs_browser) not like ‘%SURVEY BOT%’ and upper(vs_browser) not like ‘%ALEXA%’ and upper(vs_browser) not like ‘%BECOMEBOT%’ and upper(vs_browser) not like ‘%CRAWLER%’ and upper(vs_ip) not like ‘%127.0.0.1%’ and upper(vs_ip) not like ‘%82.69.92.174%’ and upper(vs_ip) not like ‘%24.83.205.219%’ and upper(vs_ip) not like ‘%60.34.15.16%’ and upper(vs_ip) not like ‘%64.158.138.48%’ and upper(vs_ip) not like ‘%64.210.196.198%’ and upper(vs_ip) not like ‘%64.246.161.30%’ and upper(vs_ip) not like ‘%65.214.36.60%’ and upper(vs_ip) not like ‘%65.88.178.10%’ and upper(vs_ip) not like ‘%66.147.154.3%’ and upper(vs_ip) not like ‘%66.151.189.7%’ and upper(vs_ip) not like ‘%66.180.233.4%’ and upper(vs_ip) not like ‘%66.196.90.%%’ and upper(vs_ip) not like ‘%66.196.91.%%’ and upper(vs_ip) not like ‘%66.249.64.79%’ and upper(vs_ip) not like ‘%66.250.128.131%’ and upper(vs_ip) not like ‘%66.98.170.93%’ and upper(vs_ip) not like ‘%68.142.249.160%’ and upper(vs_ip) not like ‘%68.142.249.201%’ and upper(vs_ip) not like ‘%68.142.250%’ and vs_os!=” and vs_browser!=” and 1 order by vs_date desc LIMIT 70110, 10;

That is one hell of a query. And I quickly found out that MySQL won’t use indexes that use database functions like upper(), or use indexes with ‘not like’ or anything with %’s around it. So that really throws this query out of the question, unless it can be re-written.

So the first thing I did was found out where in the PHP code this query was being executed, which turned out to be in the plugins/wp_jvisitors.php file. I searched for upper and found right where it was. What I noticed was it shouldn’t have to translate the vs_ip into an upper because vs_ip stores an IP address, it should be the same all the time, so I took that out. Then I started checking vs_browser for GOOGLE BOT, MSNBOT and all the matches in the query and realized it wasn’t matching anything. This query didn’t even need all the ‘not like’ matching and removing it would most likely speed the query up. Then a further look and the top of the file showed some configuration settings:

// Enter the IP you want to prevent from being listed
// Separate values with comma ‘,’. Set FALSE to disable blocking
$jBlockIP = “127.0.0.1,82.69.92.174,24.83.205.219,60.34.15.16,64.158.138.48,64.210.196.198,64.
246.161.30,65.214.36.60,65.88.178.10,66.147.154.3,66.151.189.7,66.180.233.4,66.196.90.%,66.196.91.%,66.249.64.79,66.
250.128.131,66.98.170.93,68.142.249.160,68.142.249.201,68.142.250″;

// Enter the search engine bot names (identified as Browser in jVisitors) you want to prevent from being listed
// Separate values with comma ‘,’. Set FALSE to disable blocking
$jBlockBrowser = “Google,Google Bot,Bot,MSN,MSNBot,Yahoo,Yahoo! Slurp,ZyBorg,W3C,Survey Bot,Alexa,B
ecomeBot,Crawler”;

All I had to do was set these to false to not use those options and now I see no more slow queries. So if you ever click on the “Total Visitors” link at the bottom of my blog, you may notice it’s a bit snappier now

Working at Joyent I get to see many different types of MySQL setups whether it’s replicated, clustered, load balanced or just a single instance. Each one runs a lot differently than the other so they all require different settings. Even though each one needs custom tuning, there are a few performance wins you can get with almost any MySQL database:

- If you are using MyISAM tables, make sure you have a good key_buffer size. If you are using InnoDB tables, make sure you are using a good innodb_buffer_pool_size. If you don’t have either of these set they default at only 8MB. Bumping these values up can decrease disk IO and make your database run a lot smoother. So many times I’ve seen these set to such low or default values with huge DB’s.

- Use indexes. Enable the slow query log and look for queries that show up most often, then learn the explain command on them.

- Check Created_tmp_disk_tables in show status; This value usually goes in hand with huge queries that aren’t using indexes. If it keeps increasing it means you are having to create on disk files to execute the query. Consider increasing tmp_table_size or putting your disk files on a TMPFS by setting tmpdir = /tmp.

- Use the query cache if it can help you. Check the query_cache_size. Check how well it is performing. You can do this by doing a ’show status’ and then using this formula:

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

If there are a lot of Qcache_lowmem_prunes, that means your query cache size isn’t big enough.

- If Opened_tables is increasing you probably have table_cache too small.

- If you have a lot of connections, check Threads_created in show status; If it keeps increasing, you need to up the thread_cache_size in my.cnf. The max connections in MySQL are default at 200. You can easily check how many connections you have by doing a prstat in Solaris and looking at the number of LWP (MySQL starts 10 LWP when it starts and then 1 LWP per connection). If you have a lot of connections it can eat up CPU having to create the threads over and over.

Thanks to Solaris 10 privileges you can run web servers that have to bind to port privileged ports as non-root users. This is one of the first things I did with my Webserver 7 setup. The internet is a dangerous place.

Add the privilege:

# usermod -K defaultpriv=basic,net_privaddr webservd

su to webservd and start the process. Wait.. no need to do this if you are running this under SMF. Here’s my SMF for Sun Java System Web 7 (http instance only, not admin server).

UPDATE: After testing this with SMF, its kind of annoying. Sun Java System Web uses a “watchdog” process that will restart the web processes if they get killed. If you kill the web processes, they restart fine through watchdog. If you kill the watchdog process, SMF picks it up the kill but can’t restart it because its already gone. It would be nice to be able to disable this watchdog process. I wonder if anyone has got this to work.. a search around google reveals nothing.

Setting up PHP on Sun Web 7 is really simple. My small sized Accelerator has no issues running it, only that I had to tune down the threading and make sure the admin server is disabled when you are not using it because it takes up around 200MB of memory. So before you go running anything, I’d recommend clicking through the admin web console and disabling things that aren’t needed: webdav, java, check thread pools, check caching.

There are a few different ways you can run PHP on Sun Java System Web. NSAPI, Fastcgi, and Cgi. Each one has pros and cons explained here. In this config, I use the NSAPI plugin.

To get started download the PHP Plugin pack and extract it in your webserver7/plugins directory. The PHP pack comes already compiled with MySQL and PostgreSQL support. Here’s the steps:

# cd /opt/webserver7/plugins
# gzip -dc phppack-5_2_0-solaris-amd64.zip | tar xvf -
# cp php/php.ini-recommended php/php.ini

Add these lines to your magnus.conf file:

Init fn=”load-modules” shlib=”libphp5.so” funcs=”php5_init,php5_close,php5_execute,php5_auth_trans”
Init fn=”php5_init” errorString=”PHP Initialization Failed!”

In your https-server-name/config/obj.conf under Object name=”default” section, add:

* There may be more than one file with obj.conf, to find out which one you are using you can use the wadm command line: webserver7/bin/wadm get-virtual-server-prop –user=admin –config=server-name –vs=server-name object-file

Service type=”magnus-internal/php” fn=”php5_execute”

Also in the obj.conf, modify your PathCheck to include index.php

Finally, add the mime type to https-server-name/config/mime.types:

type=magnus-internal/php exts=php,php3,php4,php5

and now restart your web instance for changes to take effect. You should see something like this when it starts:

info: php5_init reports: Initialized PHP Module (20 threads exspected)

* Quiz: Do you see the spelling mistake?

If you have any issues, check the README in the plugins/php dir, it has troubleshooting information.

I finally got around to doing something cool with my blog. A new look and image, a new webserver, and also now serving it up from my Joyent Accelerator.

Becoming familiar with something other than Apache has been really exciting. Running my blog on Sun Java System Web 7 will help me become more familiar with the product and what it has to offer, so be on the look for cool stuff I may post about it. I already know that setting PHP up on it is a breeze.

If anyone could tell me how to get rid of this favicon that would be great though, disabling it in the config doesn’t seem to do anything.

Here are some results I did on a test compiling MySQL 5.0.41 with Sun Studio 12 vs GCC 3.4.5. This was on a x4100:

Single threaded test:

GCC 3.4.5:

# time make
real 8m36.944s
user 6m53.556s
sys 1m24.685s

Sun Studio 12:

# time dmake
real 6m54.376s
user 9m34.147s
sys 2m3.422s

Conclusion: Sun Studio 12 owns.

Here are some other results of a threaded compile with Sun Studio 12 just to show off:

# time dmake -j 8
real 6m11.507s
user 9m44.306s
sys 2m17.845s

# time dmake -j 4
real 5m52.583s
user 9m52.261s
sys 2m16.401s

4 threads is the sweet spot which is the number of CPU’s in the system.