Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

OS Tuning

Status
Not open for further replies.

TomBarrand

Programmer
Aug 9, 2000
162
GB
Hi,

I have installed MySQL 4.1 on SUSE 9 for IBM Open Power Server. The server has 4GB memory and 380 GB hard disk space.

I am having performance problems with MySQL. I have put posts on several MySQL forums to see hot to optimise MySQL for my environment, but have not rectified the situation yet.

Are there any changes that I should make to the OS so that MySQL will run quickly?

Thanks for your help
 
What kinds of performance problems are you seeing? That's a heck of a big box to see performance problems. What size data space are you trying to manage? How many concurrent transactions?

Linux itself will do a good job of managing memory in conjunction with MySQL so unless your dataspace is mega large I wouldn't go there first.

What kind of disks are you using? SCSI? IDE? Disk performance can affect response.

What file system are you using? Inappropriate block size on the FS can *sometimes* affect performance of apps that frequently read small or large blocks of data.

Having enough mysql daemons started can be an issue, which you can lookup in the "mysql.ini" configuration documentation.

Using "socket" vs. "port" connections can also change performance of mysql. Use socket whenever you can, but that's for local connections.

You could describe the issue better, these are some quick hits.

D.E.R. Management - IT Project Management Consulting
 
I have a Java based application that runs in Tomcat in a Windows environment using SQL Server. As part of a customer project I have got the application to work on Tomcat on Linux with MySQL.

Both databases have a large amount of data in some of the tables, e.g. 500,000+ records in some of the tables. Some of the tables have indexes on them. The total size of the database is about 4GB.

There is a specifc part of the application that is slow when running against MySQL (10 seconds to write a record to a table with 500,000+ records in it.) The same bit of functionality running against SQL Server is instance, e.g. 2 seconds max. The code is doing an insert statement. Once the insert has taken place we need to get the ID for the inserted record. This is using SELECT MAX....... The ID column is set as the Primary Key and is indexed.

The disks are SCSI.

To me it sounds like my mysql installation is not optimised for my environment. Not really had any useful suggestions from the MySQL forums so thought it best just to check that the OS wasn't the problem.
 
I believe, reading your post, that your code logic might be working too hard to find the ID of the last inserted record. Check the MySQL website and MySQL interface routines you are using. I'm almost certain that you can run an insert with a request to be returned information about the record as part of the transaction.

That might help. Please do work to optimize Mysql for your application as well.

D.E.R. Management - IT Project Management Consulting
 
It sounds like this is a 2-step process that takes 10 seconds...which part of it is that takes this long? I bet it is not the INSERT but rather the second part. I believe that MAX() function result is not cached. In your case, perhaps the LAST_INSERT_ID() function is better. Check out this excerpt from mysql documentation:


-------------
LAST_INSERT_ID() , LAST_INSERT_ID(expr)

Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column.

mysql> SELECT LAST_INSERT_ID();
-> 195

The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions.

The value of LAST_INSERT_ID() is not changed if you update the AUTO_INCREMENT column of a row with a non-magic value (that is, a value that is not NULL and not 0).
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top