Originally posted by: praguer

On the subject system, I have a single issue. One query in MySQl causes thrashing and furthermore is unable to use RAM, going to disk temp storage. The exactly same MySQL instance and database runs on a 512MB RAM Intel Linus without any problem. I checked and tried all variations of MySQL parameters but it seems to me as an AIX memory management problem.

Is there a way of determining what might be causing this issue, please?

Originally posted by: flodstrom

Well, I'm not a db expert, but you may want to do some searching for AIX tuning for Oracle db's. Some things to consider regarding Oracle db tuning might also be applicable for MySQL.

Another thing may be paging space! How much page space does your server have? You should have at least 4G page space, if not even 8G to make AIX happy.

Originally posted by: praguer

I have done that and more. The only thing I did find out was an article for postgres which talks about "strange AIX memory management". I have tried r=v, r=v*2 --> NO EFFECT on anything. My PS is 1O % used at the highest load. It has to be a combination of MySQL memory call and AIX not talking properly.

Originally posted by: alethad

It would still be worth a try to make the OS adjustments that are suggested for Oracle DB's and if they don't work you can always set them back.

Here's the document. It's still worth reading so you can understand AIX memory. Look at page 12 in particular.

Good luck.

Originally posted by: praguer

I am sorry that I did not express myself clearly - I had already read and tried the Oracle suggestions. There were no changes - if anything, the query in question took 10% longer.

Originally posted by: Kosala

Hi,

If you're trying to compare a 512M linux machine with a 512M AIX, it's not apples to apple comparison. Linux is relatively much smaller footprint than AIX. When I last checked a bare minimum Linux kernel (kernel with the most needed modules) can fit in within a 16M memblock, where AIX 6.1 at least 256M to install. And 512M on AIX is not definitely enough memory to anything.

What do you mean by "One query in MySQl causes thrashing and furthermore is unable to use RAM, going to disk temp storage"? If you tell us what exactly you observe, we might be able to explain why it's happening like that.

Kosala

Originally posted by: praguer

Thank you for your response. I apologize if my question was not clear.

1) I am comparing a 4G Ram AIX with 512M Debian (it says so in the subject of my question)
2) What I mean by one query is an identical SQL select statement running on identical implementation of MySQL (with the exception of memory patameters)
3) What I exactly observe is that
3.1 On Linux the memory call (malloc) by MySQL is satisfied and the temporary space required by the query is acquired from RAM (or it's virtual representation)
3.2 On AIX he memory call by MySQL is NOT satisfied and the temporary space is created on /tmp FS

Since the time of my question here I have read many papers and found out that this is a common problem with AIX for other DB engines, such as Oracle or Postgres. It is related to the way AIX manages memory with a 256M limit on memory blocks. I have tried various suggestions but was unable to circumvent the memory constraints.

Lanny

Originally posted by: praguer

I am happy to report that the problem is solved. I installed the newest version of MySQL binary(64bit) and the subject problem disappeared. In addition, all the other DB performance has improved tremendously.

Thank you all for your help and patience.