cloudstack-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alexey Zilber <alexeyzil...@gmail.com>
Subject Re: CS 4 Database integrity - mysql server performances
Date Thu, 14 Feb 2013 15:07:01 GMT
CS should really switch to and recommend MariaDB instead of straight mysql.

You will find your hardware goes much farther with it.

-Alex
On Feb 14, 2013 6:24 PM, "benoit lair" <kurushi4000@gmail.com> wrote:

> Hello,
>
> I'm testing a cs 4 preproduction testing environment. I was asking me what
> if mysql server go away or mysql server was not enough big for the cs mgmt
> server requirements.
>
> My mysql server has 2 Gb of ram, i ran the mysqltuner.pl and saw these
> results :
>
> [root@cloudstack-master-server ~]# ./mysqltuner.pl
>
>  >>  MySQLTuner 1.2.0 - 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: root
> Please enter your MySQL administrative password:
>
> -------- General Statistics
> --------------------------------------------------
> [--] Skipped version check for MySQLTuner script
> [OK] Currently running supported MySQL version 5.1.61-log
> [OK] Operating on 64-bit architecture
>
> -------- Storage Engine Statistics
> -------------------------------------------
> [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
> [--] Data in InnoDB tables: 34M (Tables: 166)
> [--] Data in MEMORY tables: 125K (Tables: 2)
> [!!] Total fragmented tables: 166
>
> -------- Security Recommendations
> -------------------------------------------
> [OK] All database users have passwords assigned
>
> -------- Performance Metrics
> -------------------------------------------------
> [--] Up for: 55d 23h 4m 47s (175M q [36.356 qps], 120 conn, TX: 37B, RX:
> 16B)
> [--] Reads / Writes: 90% / 10%
> [--] Total buffers: 98.0M global + 18.6M per thread (350 max threads)
> [!!] Maximum possible memory usage: 6.5G (353% of installed RAM)
> [OK] Slow queries: 0% (0/175M)
> [OK] Highest usage of available connections: 4% (15/350)
> [OK] Key buffer size / total MyISAM indexes: 8.0M/92.0K
> [OK] Key buffer hit rate: 99.6% (1K cached / 4 reads)
> [OK] Query cache efficiency: 28.1% (14M cached / 52M selects)
> [!!] Query cache prunes per day: 21931
> [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3M sorts)
> [!!] Joins performed without indexes: 54279
> [OK] Temporary tables created on disk: 0% (299 on disk / 3M total)
> [OK] Thread cache hit rate: 70% (35 created / 120 connections)
> [!!] Table cache hit rate: 1% (64 open / 3K opened)
> [OK] Open file limit used: 0% (6/1K)
> [OK] Table locks acquired immediately: 100% (17M immediate / 17M locks)
> [OK] InnoDB data size / buffer pool: 34.1M/40.0M
>
> -------- 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 ***
>     query_cache_size (> 32M)
>     join_buffer_size (> 16.0M, or always use indexes with joins)
>     table_cache (> 64)
>
>
> Okay, i know i configured very too strongly certains buffers so theorically
> i can override the physical capacity of this server.
>
> But what about the query cache prunes per day ?
> Other question, if i have connections aborted, is it dangerous for the cs
> db integrity ?
> Also if i reach the max connections ? Is ti dangerous too
>
> What are the cases i must absolutely avoid in order to keep my cs db
> integrity ?
>
> Thanks for your answers.
>
> Regards, Benoit Lair.
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message