Re: Ssd vs mix of ssd and spinning disk

[shawn l.green]

Hi Shain,

On 5/8/2017 1:53 PM, Shain Miley wrote:

Hello,
We have traditionally setup our mysql database servers with a mix of ssd and 
spinning disk drives.

We use the ssd drives (Raid-1) for the mysql tablespace data, and we use the 
spinning disks (15 sas in Raid-1) for the index data, etc.

I am wondering if going forward we should simply put all of the data on the ssd 
drives. Does anyone have any information on whether or not we would see any 
significant performance increase if we made this switch?

I have been thinking about using 4 ssd drives (Raid-10) going forward…and 
wondering if we should expect to see any improvement in the database 
performance.

Any thoughts?

Thanks in advance,

Shain



If you would benefit from shifting storage technologies depends on how 
limiting your current devices are to your overall throughput.


In most cases, workloads are either CPU-bound (normally due to poor 
choices in table design or query patterns) or DISK-bound (too many reads 
and writes, i/o requests,  for the device to keep up).


Occasionally systems become MEMORY-bound (normally due to poor 
configuration choices which push the system to using swap) or 
NETWORK-bound (the number of concurrent network round trips to complete 
a task is higher than the components can handle).



Of those 4 situations, which is contributing most to your total response 
latency?


For example, are you spending more time waiting for data to be buffered 
in from disk than you are computing and returning the results? If so, 
faster disks could help temporarily.


What may help more (and for a longer time) is to improve your storage 
and retrieval patterns (table and query designs) to require less 
frequent trips to disk (aka, better buffering) or to need smaller slices 
of each table (more selective indexes, querying for fewer columns, 
sharding tables, sharding data to different instances, partitioning 
data, ... ).



--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: tcmalloc mysql 5.7.14

[shawn l.green]

(yes, I recognize how late this reply is)

On 5/8/2017 7:56 AM, Reindl Harald wrote:



Am 08.05.2017 um 13:51 schrieb Machiel Richards:

We are having an issue with memory allocations on mysql 5.7.14
whereby
mysql is not releasing the memory that is being allocated during
mysqldump processes.

 This has been logged as a bug with mysql dev team however they do
not
see this as a bug and suggested we test using another malloc library
such as tcmalloc.

However from what I can see, this is no longer included in mysql 5.7
and thus I am trying to find out if anyone can tell me how to enable /
install this and how to tell mysql to use this library.


does the oracle stuff not support "jemalloc" like MariaDB

MariaDB even suppports "-DWITH_JEMALLOC=system" because it's not the job
of random software ship and build random library sources in their
tarballs which usually don't get much attention in case of updates
(others than system packages)



Yes, if your system has the jemalloc library on it, MySQL can use it. 
One way to activate it is with this option to mysqld_safe.


https://dev.mysql.com/doc/refman/5.7/en/mysqld-safe.html#option_mysqld_safe_malloc-lib

And, you can control whether the InnoDB engine uses it's own memory 
management routines or those provided by the operating system:
https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-use_sys_malloc.html 




Just because we don't bundle a library with our software does not mean 
that our end users cannot download one for their operating system from 
other reputable sources (like the Google devs for tcmalloc, for example)


--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql