← Back to team overview

maria-discuss team mailing list archive

Re: MariaDB 10.1 uses huge amount of memory onWndows

 

On a side-remark (if someone is interested) the numbers displayed in Task
Manager for the VM running SuSE with MariaDB 10.0.9 must be incorrect (ther
is a full OS runnnig and with a lot of server programs - LDAP, Apache etc.
etc.).  It seems that Windows does not get true information  from the VM
process. Also when task manager displays 19-20 GB of memory use in total,
Windows will start complaining that it is about to run of of memory and
programs should be closed  So it seems that around 10 GB memory used by VMs
are unaccounted for when it happens.



For the SuSE 12.3/MariaDB 10.0.9 instance InnoDB buffer details are :


SHOW VARIABLES LIKE 'innodb_buffer%'

Variable_name                        VALUE
-----------------------------------  ----------------
innodb_buffer_pool_dump_at_shutdown  OFF
innodb_buffer_pool_dump_now          OFF
innodb_buffer_pool_filename          ib_buffer_pool
innodb_buffer_pool_instances         8
innodb_buffer_pool_load_abort        OFF
innodb_buffer_pool_load_at_startup   OFF
innodb_buffer_pool_load_now          OFF
innodb_buffer_pool_populate          OFF
innodb_buffer_pool_size              134217728  (= 128M)


-- Peter


On Fri, Nov 21, 2014 at 11:35 PM, Peter Laursen <peter_laursen@xxxxxxxxxx>
wrote:

> Well,you are right
>
>
>
> -- 10.0:
> =====
>
> SHOW VARIABLES LIKE 'innodb_buffer%'
>
> Variable_name                        VALUE
> -----------------------------------  ----------------
> innodb_buffer_pool_dump_at_shutdown  OFF
> innodb_buffer_pool_dump_now          OFF
> innodb_buffer_pool_filename          ib_buffer_pool
> innodb_buffer_pool_instances         8
> innodb_buffer_pool_load_abort        OFF
> innodb_buffer_pool_load_at_startup   OFF
> innodb_buffer_pool_load_now          OFF
> innodb_buffer_pool_populate          OFF
> innodb_buffer_pool_size              2133852160
>
>
>
> [mysqld]
> datadir=C:/Program Files/MariaDB 10.0/data
> port=3313
> sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
> default_storage_engine=innodb
> innodb_buffer_pool_size=2035M
> innodb_log_file_size=50M
> [client]
> port=3313
>
>
>
>
> -- 10.1
> =====
>
> SHOW VARIABLES LIKE 'innodb_buffer%'
>
> Variable_name                        VALUE
> -----------------------------------  ----------------
> innodb_buffer_pool_dump_at_shutdown  OFF
> innodb_buffer_pool_dump_now          OFF
> innodb_buffer_pool_filename          ib_buffer_pool
> innodb_buffer_pool_instances         8
> innodb_buffer_pool_load_abort        OFF
> innodb_buffer_pool_load_at_startup   OFF
> innodb_buffer_pool_load_now          OFF
> innodb_buffer_pool_populate          OFF
> innodb_buffer_pool_size              4289724416
>
>
> [mysqld]
> datadir=C:/Program Files/MariaDB 10.1/data
> port=3314
> sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
> default_storage_engine=innodb
> innodb_buffer_pool_size=4091M
> innodb_log_file_size=50M
> character-set-server=utf8
> [client]
> port=3314
>
>
> I did not notice the setting during installaton. IMO it is a rather
> aggressive settng for a desktop/developer/application testing system from
> 10.1.  But of course I should have compared configurations.
>
>
>
> (BTW - SuSe 12.3 skips this config with no explicit innodb buffer pool
> setting (commented lines removed):
>
> [client]
> # this section is empty
>
> [mysqld]
> innodb_file_format=Barracuda
> innodb_file_per_table=ON
> sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
>
> [mysqld_multi]
> mysqld     = /usr/bin/mysqld_safe
> mysqladmin = /usr/bin/mysqladmin
> log        = /var/log/mysqld_multi.log
> # details for multi instances all commented here
>
> !includedir /etc/my.cnf.d
> # my.cnf.d contains load statements to plugin .so's for blackhole,
> federated and archive engines - all commented out.)
>
>
>
> ..so it is all explained.  But I think such setting should be more visible
> in the installer interface, in particular since it seems to have been
> changed between 10.0 and 10.1. But not a big issue of course.
>
>
>
> -- Peter
>
> On Fri, Nov 21, 2014 at 10:57 PM, Vladislav Vaintroub <
> vvaintroub@xxxxxxxxx> wrote:
>
>>  MSI installation defaults to  1/8 of RAM for the Innodb bufferpool. You
>> can change this value during the installation, see the screenshot is under
>> "Other database properties'" section in
>> https://mariadb.com/kb/en/mariadb/documentation/getting-started/binary-packages/installing-mariadb-msi-packages-on-windows/
>>
>>
>>
>> On 11/21/14 21:45, Peter Laursen wrote:
>>
>> Please see attached imaged - a screenshot of Windows Task manager.
>>
>>  I have a lot of MySQL and MariaDB servers installed (all needed for
>> application testing). It is noticeable that MariaDB 10.1 uses around twice
>> as much as much memory as compared to MariaDB 10.0, MySQL 5.6 and 5.7 (5 GB
>> versus 2.5 GB in rough numbers).  Even more surprising to me as P_S is not
>> running with 10.1 (as I understand).
>>
>>  Further I have MariaDB 10.0 running virtualized in OpenSuSE 12.3 inside
>> Virtualbox. The process for the virtual machine use only around 10% or
>> memory as compared to MariaDB 10.0 running natively on Windows (there is
>> also an active VM running Mint Linux - but MySQL is not running there
>> currently)
>>
>>  I also have a number of older MySQL servers (5.0, 5.1 and 5.5) The use
>> much less memory.  That is expected as the configuration reserves smaller
>> buffers.
>>
>>  Both MariaDB servers in Windows use the configuration created by the
>> installer. MySQL 5.6 adn 57 servers use the MySQL Installer standard
>> "developer machine" configuration. The configuraiton of MariaDB 10 in SuSE
>> is as shipped with the distro (I did not check it actually).
>>
>>  None of the servers have been connected to since system was restarted.
>> System has been  running for approximately 4 hours and all servers start
>> with Windows. The VM with SuSE has been running for approxmately 2 hours.
>>
>>  In the attached image I have framed and starred my observations.
>>
>>
>>  I want to emphatize, that I don't face any problems with this at all.
>> I have 32 GB RAM and 4 CPUs each capable of processing 2 parallel
>> threads,and I can run  Windows 7 with all the servers you see (and
>> simultaneously use the system iinteractively for Internet browsing, playing
>> media, processing photos, document creation/editing and what else you would
>> do with a desktop system), as well as have the two VMs (both configured
>> with an upper resource limit of 24 GB RAM and 6 CPU threads).
>>
>>  The memory may be released if it is required by other processes. I
>> don't think it is a problem to use memory if it is available if it will be
>> released when it is required elsewhere (on the opposite it was a little
>> expensive, so it would be a shame if it was not used at all!)
>>
>>  But still I find the find the MariaDB 10.1 number for memory use so
>> much *off* as compared to comparable servers, that I think I should mention
>> the observation here. IMO it should be understood **why** it happens and
>> next it can be decided **if** a fix is necessary or not.
>>
>>
>>  -- Peter
>> -- Webyog
>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~maria-discuss
>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>>
>

Follow ups

References