MyISAM v. InnoDB Performance

[jbennett]

I'm wondering about making this switch for a system with the following checks:

# Active Host / Service Checks: 771 / 1859
# Passive Host / Service Checks: 208 / 17064

I do not know much about data bases but have plenty of support to help with the switch.

Currently, we are running MySQL 5.0.95.

Perhaps the following information will help as well?

Code: Select all

mysql> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8384512 |
+-----------------+---------+
1 row in set (0.00 sec)

mysql> SELECT SUM(index_length) ndxsize FROM information_schema.tables WHERE engine='MyISAM';
+----------+
| ndxsize  |
+----------+
| 91992064 |
+----------+
1 row in set (0.03 sec)

mysql> SELECT SUM(data_length+index_length)/power(1024,2) datndxsize FROM information_schema.tables WHERE engine='MyISAM';
+-----------------+
| datndxsize      |
+-----------------+
| 299.23865032196 |
+-----------------+
1 row in set (0.02 sec)

Code: Select all

# free -m
             total       used       free     shared    buffers     cached
Mem:         12021       3257       8763          0        287       1777
-/+ buffers/cache:       1192      10828
Swap:        18527          0      18527

Code: Select all

# cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 46
model name      : Intel(R) Xeon(R) CPU           L7555  @ 1.87GHz
stepping        : 6
cpu MHz         : 1861.524
cache size      : 24576 KB
physical id     : 0
siblings        : 3
core id         : 0
cpu cores       : 3
apicid          : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc pni ssse3 cx16 sse4_1 sse4_2 popcnt lahf_lm
bogomips        : 3723.04
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 46
model name      : Intel(R) Xeon(R) CPU           L7555  @ 1.87GHz
stepping        : 6
cpu MHz         : 1861.524
cache size      : 24576 KB
physical id     : 0
siblings        : 3
core id         : 1
cpu cores       : 3
apicid          : 1
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc pni ssse3 cx16 sse4_1 sse4_2 popcnt lahf_lm
bogomips        : 3778.69
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 2
vendor_id       : GenuineIntel
cpu family      : 6
model           : 46
model name      : Intel(R) Xeon(R) CPU           L7555  @ 1.87GHz
stepping        : 6
cpu MHz         : 1861.524
cache size      : 24576 KB
physical id     : 0
siblings        : 3
core id         : 2
cpu cores       : 3
apicid          : 2
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx lm constant_tsc pni ssse3 cx16 sse4_1 sse4_2 popcnt lahf_lm
bogomips        : 3482.69
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

In my searching, I've come across this thread that prompted me to ask the question about making the switch: http://dba.stackexchange.com/questions/ ... le-indexes

Any insight as to why this would or would not be a good idea would be greatly appreciated.

[lmiltchev]

We would not recommend this conversion. While you could probably do it, and possibly see some performance benefits, this hasn't been tested, and it is NOT going to be supported. If something happens with your database, you would be on your own.