Home / server / Questions / 317584
In Process
Boolean
Asked: 2011-10-02 15:58:28 +0800 CST

Mysql performance, understanding top

  • 772

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.

performance mysql mysql5

1 Answers

  1. 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

    • 2