-1

I have a 3GB server with the below settings. The issue I am facing is when I pull some records through the admin panel of my site, sometimes it works well, but most of the times it process very slow.

Can you please suggest the settings?

[mysqld]
max_connections=500
join_buffer_size = 2M
tmp_table_size = 24M
max_heap_table_size = 24M
query_cache_size = 256M
key_buffer=128M
key_buffer_size = 512M
thread_cache_size = 4
table_cache = 500
table_open_cache = 96
innodb_buffer_pool_size = 27M
slow_query_log
local-infile=0
jscott
  • 24,204
  • 8
  • 77
  • 99
user183207
  • 1

1 Answers1

0

It's impossible to give you a meaningful answer without a better idea as to the workload on the database.

You might try mysqltuner.pl which will, after leaving the database running under normal conditions for a couple of days, give you some recommendations for changes.

Flup
  • 7,688
  • 1
  • 31
  • 43
  • So do I need to copy and paste mysqltuner.pl ? and where i need to do this? – user183207 Jul 26 '13 at 15:03
  • Follow the link -- it's a script you can download and run on your mysql server. – Flup Jul 26 '13 at 15:04
  • could you please let me know if there how i can find an expert who can debug and optimize my mysql? I am ready to pay for this job. – user183207 Jul 26 '13 at 15:56
  • Hi, here is the output – user183207 Jul 26 '13 at 18:09
  • >> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.70-cll [OK] Operating on 64-bit architecture – user183207 Jul 26 '13 at 18:11
  • -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 28M (Tables: 135) [--] Data in InnoDB tables: 72M (Tables: 102) [!!] Total fragmented tables: 76 – user183207 Jul 26 '13 at 18:13
  • Recommendations General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes Increase table_cache gradually to avoid file descriptor limits Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** join_buffer_size (> 3.0M, or always use indexes with joins) table_cache (> 96) innodb_buffer_pool_size (>= 72M) – user183207 Jul 26 '13 at 18:15