2

We have a 64 bit mac os x server running mysql. The machine has 48 GB of ram.

Top shows as below.

MemRegions: 12053 total, 16G resident, 12M private, 76M shared. PhysMem: 2072M wired, 17G active, 29G inactive, 48G used, 21M free.

Top output for mysql is

COMMAND             %CPU    TIME        #TH      #WQ     #PORTS    #MREGS   RPRVT     RSHRD     RSIZE     VPRVT     VSIZE     PGRP     PPID     STATE       UID     FAULTS 
mysqld              31.9     07:27:15    69/4     0       204       655      15G+      244K      15G+      16G       23G       64202    1        running     74      4183172+

One of the queries doesn't complete for 20k rows even for > 12 hours. I think thrashing is going on because the available physical memory free is 21M only. But the query completes in a minute for 2k rows.

All the tables references in queries are myisam but 2 tables are innodb.

As memory is the concern, here are some mysql parameters that are memory intensive.

key_buffer_size = 12G max_allowed_packet = 1G innodb_buffer_pool_size = 8G myisam_sort_buffer_size = 64M

Can someone tell me if there is anything wrong with the configuration.

Boolean
  • 533
  • 1
  • 4
  • 10
  • It has nothing to do with the amount of free physical memory. If more physical memory were free, it would be thrashing more because it would be using *less* working space. The problem is the working set being too big -- you have to figure out why that is. – David Schwartz Oct 02 '11 at 05:52

1 Answers1

2

enable slow query log and see if you have any bad queries going on

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

Mike
  • 22,310
  • 7
  • 56
  • 79