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

Unix Commands

free - memory usage
iostat 5 - IO statistics
ps -ef - processes

Find large files

To find all files with a size greater than 6GB use the following:

find / -type f -size +600000k -exec ls -lh {} \; | awk '{ print $9 ": " $5 }'

Suspect Database

sp_configure 'allow updates', 1 Reconfigure with override
sp_resetstatus 'crm'
sp_configure 'allow updates', 0 Reconfigure with override

restart sql service

If the databse is still suspect put it into emergency mode and extract the data

sp_configure 'allow updates', 1 Reconfigure with override
UPDATE master..sysdatabases SET status = 32768 WHERE name = 'DatabaseName'
restart sql service

Should now be able to use DTS or BCP to get the data out

No LDF and sp_detachdb not Run

1. Copy good mdf to new location
2. Create new database with same dbname, file names and locations.
3. Stop services
4. Copy good mdf and replace new empty mdf
5. Restart services - database will be suspect
6. Setup server to updates to sysdatabases:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

7. Put database in emergency mode:

update sysdatabases set status = 32768 where name = ''

8. Stop and start services again - database should be marked in emergency mode
9. Rebuild the log file:

DBCC TRACEON (3604)
DBCC REBUILD_LOG('','')
Go

10. DBCC CHECKDB(,REPAIR_REBUILD)

Sql Server Versions

SELECT
@@Servername
,SERVERPROPERTY ('productversion')
,SERVERPROPERTY ('productlevel')
,SERVERPROPERTY ('edition')