Hardware and performance

Hello,

Another update. I have:

1) Disabled InnoDB, since tables are using MyISAM

2) Adjusted some variables

Here's the new MySQLTuner output:

>> MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
>> 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: moodle_mdluser
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.81-community-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 922M (Tables: 218)
[!!] Total fragmented tables: 9

-------- Security Recommendations -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied to user 'moodle_mdluser'@'localhost' for table 'user'
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12h 25m 15s (1M q [32.293 qps], 75K conn, TX: 3B, RX: 315M)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 704.0M global + 20.4M per thread (500 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 10.7G (547% of installed RAM)
[OK] Slow queries: 0% (27/1M)
[OK] Highest usage of available connections: 4% (20/500)
[OK] Key buffer size / total MyISAM indexes: 512.0M/358.2M
[!!] Key buffer hit rate: 85.7% (19M cached / 2M reads)
[OK] Query cache efficiency: 65.9% (680K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 72K sorts)
[!!] Joins performed without indexes: 1609
[!!] Temporary tables created on disk: 32% (57K on disk / 176K total)
[OK] Thread cache hit rate: 99% (20 created / 75K connections)
[!!] Table cache hit rate: 13% (661 open / 5K opened)
[OK] Open file limit used: 36% (1K/3K)
[OK] Table locks acquired immediately: 99% (625K immediate / 625K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
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
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 (> 16.0M, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 32M)
table_cache (> 1500)


Two things are really, really puzzing me:

1) Fragmented tables. I run OPTMIZE on all the tables of the database. In a matter of an hour, I again get fragmented tables. Why the heck is that happening?? That is certainly not normal. Is it because I'm doing OPTMIZE while users are accessing the DB?


2) Excessive JOINs without INDEXes. 1609 JOINs without Indexes! Why is that possibility happening? Is that a 1.9.6+ issue? The DB is fully indexed, when running XML DB Editor for checking it, I get all indexes are fine. The output is attached for reference. I have increased join_buffer_size to 16M in order to optimize such time consuming JOINs.

So, if the DB is indexed and JOINs are not using indexes, who's the fault here? Do I have to dig into the SQL code and change the queries? Is anyone experiencing this as well??

If anyone can help here, I'd really appreciate it.

best regards,

- Breno