Solaris Commands

Show verison information:
version

Show process with highest CPU utilisation:
ps -eo pid,pcpu,args sort +1n

Show process with highest memory usage:
ps -eo pid,vsz,args sort +1n

Disk geometry and partition infromation:
prtvtoc /dev/rdsk/c0t0d0s0

64 or 32 Bit
isalist -v
isainfo -v

System Configuration
sysdef

Processor info
psrinfo -v

Mysql Performance

The performance of Mysql is frustrating to say the least. It seems like taking a step back after using the likes of Oracle and SQL Server.

If so many big organisations are now using Mysql there must be a way to get acceptable performance without having to continually jump through hoops. It appears that as soon as you start to get tables of any significant size then everything grinds to a halt.

My databases are only a a few million records, the largest is just short of 100 million. Updates are a complete nightmare and if you want to add an index or a new column then forget taking a coffee break, you might aswell go on holiday.

Mysqlreport

"mysqlreport makes a friendly report of important MySQL status values. mysqlreport transforms the values from SHOW STATUS into an easy-to-read report that provides an in-depth understanding of how well MySQL is running. mysqlreport is a better alternative (and practically the only alternative) to manually interpreting SHOW STATUS. "

./mysqlreport --user myusername --password --outfile report.out --host myhost --port 3306

For the full guide see http://hackmysql.com/mysqlreportguide

SQL 2000 Error Handling

declare @Error int

begin transaction
insert into emp (empno,ename,sal,deptno)
values (@empno,@ename,@sal,@deptno)

set @Error = @@ERROR
if @Error <> 0 --if error is raised
begin
goto LogError
end
commit transaction
goto ProcEnd

LogError:
rollback transaction

declare @ErrMsg varchar(1000)
select @ErrMsg = [description] from master.dbo.sysmessages
where error = @Error
insert into error_log (LogDate,Source,ErrMsg)
values (getdate(),'sp_emp_insert',@ErrMsg)

ProcEnd:
end

MySql Outfile

SELECT
Field1, field2, …
INTO
OUTFILE '/tmp/filename.txt'
FIELDS TERMINATED BY '#' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM
Table

Mysql Tuning Tools

See http://hackmysql.com/ for details of mysqlreport and mysqlsla
See http://www.maatkit.org/tools.html for the mk-query-profiler