-1

I have a server with 8GB of memory, running CentOS 5.4, php 5.2.9, mysql 5.0.77.

I have a very large databse of about 20GB (InnoDB and MyIsam tables) and website using it. Website has about 20000 visitors per day and about 100 000 pageviews per day. My website is very slow. Sometimes takes about 15-20 sec to open up a page.

I am asking if you could help me improve my server performances.

I think problem is with MySql. And maybe aplication not closing connections properly. Also to mention I have about 2000 - 3000 TIME_WAIT processes.

Here is my.cnf for MySQl:

key_buffer = 1024M
table_cache = 1024
table_open_cache = 1024
sort_buffer_size = 10M
read_buffer_size = 10M
read_rnd_buffer_size = 10M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 64M
-# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
innodb_buffer_pool_size = 1536M
innodb_additional_mem_pool_size = 128M
innodb_flush_log_at_trx_commit=0
set-variable=max_connections=1200

And here is mysqltuner log:

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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 8G (Tables: 19)
[--] Data in InnoDB tables: 5G (Tables: 27)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 3

-------- Security Recommendations -------------------------------------------
[!!] User '@cl-t061-430cl.privatedns.com' has no password set.
[!!] User '@localhost' has no password set.
[!!] User '[email protected]' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 54d 21h 14m 44s (466M q [98.362 qps], 2M conn, TX: 1103B, RX: 112B)
[--] Reads / Writes: 78% / 22%
[--] Total buffers: 2.7G global + 80.4M per thread (1200 max threads)
[!!] Maximum possible memory usage: 96.9G (1240% of installed RAM)
[OK] Slow queries: 0% (46K/466M)
[OK] Highest usage of available connections: 66% (801/1200)
[OK] Key buffer size / total MyISAM indexes: 1.0G/10.8G
[OK] Key buffer hit rate: 100.0% (194B cached / 1M reads)
[OK] Query cache efficiency: 73.7% (316M cached / 429M selects)
[!!] Query cache prunes per day: 24969
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 77M sorts)
[!!] Joins performed without indexes: 188318
[!!] Temporary tables created on disk: 49% (69M on disk / 139M total)
[OK] Thread cache hit rate: 92% (205K created / 2M connections)
[OK] Table cache hit rate: 23% (1K open / 4K opened)
[OK] Open file limit used: 4% (269/6K)
[OK] Table locks acquired immediately: 99% (346M immediate / 346M locks)
[!!] InnoDB data size / buffer pool: 5.7G/1.5G

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
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
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
* MySQL's maximum memory usage is dangerously high
Add RAM before increasing MySQL buffer variables *
query_cache_size (> 64M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
innodb_buffer_pool_size (>= 5G)

I would appreciate any ideas, suggestions. Thanks very much.

4 Answers 4

1

buy more ram. is there anything you can do at the application level? Can you enable caching, add indexes, switch engines? I've seen an increase in performance with big tables when switching to mysql 5.1

2
  • +1 for more ram, like 16G or so.
    – 3molo
    Jul 14, 2011 at 14:22
  • First thanks for quick response. Well, soon I will migrate the website to a new server with 24 GB RAM. And hopefully I have configured the server properly. This is customer website and I can't do nothing on application (until me and my company make new website) . I guess I could empty some tables in database (unnecessary statistics table is 15GB!!!). I was just looking for a temp solutions. Maybe something with this mysql configuration.
    – dennisg
    Jul 14, 2011 at 14:41
1

I am not sure it can be defined as performance tuning but, by $deity sake, use innodb_file_per_table or you will have trouble managing disk space sooner or later cfr http://lamp.mayavi.info/index.php/databases/innodb-per-table-tablespaces-file-for-each-innodb-table.html

0

Once you have more RAM some of the low hanging fruit would be to:

  1. Increase the innodb_buffer_pool_size (as suggested by mysqltuner)
  2. Increase innodb_log_file_size to some reasonable size, say 1024M
  3. You seem to have a lot of Temporary tables created on disk, you can try to optimize your queries or put MySQL's tmpdir on a RAM disk

Cheers

0

first try to enable slow query log, it could be good to review queries taking a lot of time to be excecuted. mysqldumpslow is good tool provided with mysql to analyse your slow queries log.

indeed you should also follow the advice of jasondbecker about memory usage.

Not the answer you're looking for? Browse other questions tagged .