MySQL – Tuning After Installation

Friday, October 27, 2006

Read this post at the MySQL Performance Blog. I found it informative and will need to take an in-depth look at modifying the MySQL variables discussed.

I will quickly admit I am guilty of using default values for MySQL variables. Luckily, none of our MySQL installations are “mission critical”.

I wrote a recent post about the MySQL Error 1206 I encountered. Could possibly have avoided this problem with the information I found in the MySQL Performance Blog. For example, by increasing the innodb_buffer_pool_size value it not only resolved the 1206 error, it also improved query performance. Chalk it up as another lesson learned.

Oracle10gR2 Alert E-Mail

Thursday, October 26, 2006

I received an e-mail from Oracle Global Customer Support regarding the RDBMS server patchset for Sun Sparc Solaris 64-bit. The e-mail said:

Please note that Patch 5117016 is a MANDATORY PATCH required to be installed and run immediately AFTER installation of the Patch Set on Sun Sparc Solaris 64-bit. This patch fixes a known issue in Patch Set on Sun Sparc Solaris where the libserver10.a library is installed incorrectly into $ORACLE_HOME/rdbms/lib instead of into $ORACLE_HOME/lib.

Any patches applied without Patch 5117016 installed must be backed out, Patch 5117016 must be applied and then the patches reinstalled.

Not applying the patch can cause subsequent problems such as:

  • Applying patches on top of may have no effect.
  • Changing the DBA user or SGA attach address may have no effect

Further details on this issue can be found in ALERT Note 394933.1

I checked the “Oracle 10g Release 2 (10.2) Support Status and Alerts” (Metalink Note 316900.1) and sure enough there are known issues (Metalink Note 359415.1) with a link to a note for Bug 5117016.

Next I went to my Oracle10gR2 installation on my Solaris 9 64-bit server and executed the commands below.

% cd $ORACLE_HOME/rdbms/lib
% ls -l libserver*
-rwxr-xr-x 1 oracle dba 154257164 Feb 24 2006 libserver10.a

% cd $ORACLE_HOME/lib
% ls -l libserver*
-rwxr-xr-x 1 oracle dba 153646336 Oct 20 15:52 libserver10.a

Notice the October 20 date on the $ORACLE_HOME/lib/libserver10.a file. I had version already installed as well as CPUJul2006 (Patch 5225799). On October 20, I applied the patch for Bug 5458753 as described in my blog post “Oracle10g – Bug 5458753”.

It would appear after applying Patch 5458753 on October 20, the libserver10.a library was placed in the correct directory. In addition, according to a development team, the patch corrected the “SQL can execute in wrong Schema” problem.

Perhaps that is why the alert bullet-point is phrased “Applying patches on top of may have no effect.”

Oracle on HP-UX

Tuesday, October 24, 2006

In preparation for installing the Oracle patch on a HP-UX 11.00 64-bit server, I ran into the following issues that needed resolution before I could begin the upgrade.

Initially executed the Unix InstallPrep Script (Metalink Note 189256.1) to verify the installation requirements were met. There were some required OS patches missing as listed below.

Required OS package PHCO_24148 is not installed
Required OS package PHKL_24268 is not installed
Required OS package PHKL_25475 is not installed
Required OS package PHKL_25525 is not installed
Required OS package PHNE_24715 is not installed
Required OS package PHSS_23670 is not installed
Required OS package PHSS_24301 is not installed
Required OS package PHSS_22868 is not installed

After the required OS patches (or their successors) were installed by my Unix Admin, I started the Oracle Installer and encountered the error ‘Unable to convert from “UTF-8” to “roman8” for NLS!’. I found the solution in Metalink Note 390456.1.

The resolution requires an additional entry to the “aliases” section of the /usr/lib/nls/iconv/config.iconv file.

alias utf8 UTF-8

This file is set to read-only.

-r–r–r– 1 bin bin 14762 Oct 24 17:26 config.iconv

So in order to save the change specified, I needed to temporarily change the permissions (chmod 777), edit and save the file, and then change the permission back (chmod 444) using the root account.

Execute permission for oracle on /usr/sbin/swapinfo is required.

-r-xr–r– 1 bin bin 24576 Feb 5 2001 swapinfo

Had to permanently change the permissions (chmod 555) using the root account.

-r-xr-xr-x 1 bin bin 24576 Feb 5 2001 swapinfo

Update (05-Dec-2006)
It appears Oracle updated the CPUOct2006 advisory on October 31 and there are vulnerabilities in the patch. You can find the details at the Integrigy Oracle Security Blog.

MySQL Error 1206

Friday, October 20, 2006

“The total number of locks exceeds the lock table size”
I encountered this error when executing the SQL below.

DELETE perfdata_service_raw
FROM perfdata_service_raw, perfdata_host
WHERE perfdata_service_raw.host_name = perfdata_host.host_name AND
perfdata_host.is_deleted = 1

Googled to find a resolution and found this link where the resolution is to increase the innodb_buffer_pool_size variable in /etc/my.cnf. The default value is 8M, so I set it to 256M, restart the mysqld service (service mysqld restart), and the problem is resolved.


The part I want to understand is what this variable has to do with table locks. The description of the innodb_buffer_pool_size variable in the MySQL 4.x Documentation states:

“The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system.”

I continue to search, but not finding anything at the moment that will help me understand how lock table size and innodb_buffer_pool_size are related. The quest for an answer continues …

Update (21-Oct-2006)
Found an explanation here which says:

“The SQL statement sets locks on all the records it scans. If you have a small buffer pool, then the InnoDB lock table may indeed grow so big that it does not fit in the buffer pool. “

I want to understand more about the InnoDb lock table, so a little more research will need to be done.

Update (23-Oct-2006)
Found what I was looking for about the InnoDB lock table and it’s behavior at MySQL Performance Blog. It gives some background on the InnoDB lock table:

“in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record allocated for each hash and for each row locked on that page bit can be set.”

Karol Zielinski: Just a tech stuff
James Wallace:

Oracle on Solaris

Friday, October 13, 2006

I installed the Oracle patch on a Solaris 9 64-bit server. Fortunately before I installed the patch I reviewed “Oracle9i Release 2 (9.2) Support Status and Alerts” on Metalink. For isssues specific to the patch read Metalink Note 388281.1. There are known issues (Bug 4547809) on Solaris 64-bit, as well as other OS platforms.

The primary issue with my platform is outlined in Metalink Note 390993.1 regarding a missing owapatch.sql file. The note outlines a workaround which is to download and copy the owapatch.sql file into $ORACLE_HOME/rdbms/admin prior to running catpatch.sql.

Other issues include Bug 5606601, Bug 5503340, and Bug 4087018.  All appear to have a solution or workaround.

Update (17-Oct-2006)
It appears the patch already contains the security patches in CPUJul2006 and CPUOct2006.

Update (05-Dec-2006)
It appears Oracle updated the CPUOct2006 advisory on October 31 and there are vulnerabilities in the patch. You can find the details at the Integrigy Oracle Security Blog.

Password Topics

Friday, October 13, 2006

I really need to implement a better Oracle account and password process for my development environments. Saw this blog entry by Andy Campbell back in March, but still have not gotten around to putting this simple process in place.

Update (30-Oct-2006)
Additional information about Oracle passwords on Robert Vollman’s blog.

Password Manager
I use KeePass Password Safe for keeping my passwords secure. I’ve been using this tool since January 2006. It serves my needs very well for all the passwords I need to remember for work and home. Jeff Hunter recently described his frustration with passwords here.

Update (16-Oct-2006)
New KeePass version 1.06 available here.

Good information here from Frank Mash about storing passwords in MySQL.

MS SQL Server Blogs

Saturday, October 7, 2006

I’m always looking for additional insights from Tech blogs. Recently read a Database Trends and Applications article (August 2006, page 6) by Kevin Kline entitled SQL Server Embraces the “Glasnost” of the Blogosphere. In the article, he lists blogs written by members of the Microsoft SQL Server team. A great way to get additional insight on specific areas of SQL Server 2005.

I have added some of the blogs mentioned in the article to both my Firefox bookmarks and here on my blog (see MS SQL Server Blogs).