Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Limit MySQL Users' CPU/memory usage

Tags:

memory

mysql

cpu

We are going to allow many users to access to a MySQL database. Each use has his/her own tables. We are going to set limitations on each user's CPU/memory usages. For instance, each use can't use more than 15% CPU and 512M memory. What are the best practice to achieve this goal?

Our operation system is Windows Server 2012. As much as we know, windows has the abilities to set CPU/memory usage limitation on the process level. However, all users share the same process in our scenario. Can we limit users' CPU/memory consumption by configuring MySQL?

like image 511
Harry He Avatar asked Mar 03 '14 02:03

Harry He

People also ask

Why does MySQL consume so much CPU?

Increases in CPU utilization can be caused by several factors, such as user-initiated heavy workloads, multiple concurrent queries, or long-running transactions. To identify the source of the CPU usage in your Amazon RDS for MySQL instance, review the following approaches: Enhanced Monitoring. Performance Insights.

Why does MySQL consume so much memory?

If you are using MEMORY tables and variable max_heap_table_size is set very high, this can also take a large memory since max_heap_table_size system variable determines how large a table can grow, and there is no conversion to on-disk format.

Which MySQL case eats all memory?

First of all, there are 3 major cases when MySQL will crash due to running out of memory: MySQL tries to allocate more memory than available because we specifically told it to do so. For example: you did not set innodb_buffer_pool_size correctly. This is very easy to fix.

1 Answers

This is not exactly what you are (or were) looking for, but for MySQL 5.6 and 5.7 at least, there are ways to limit:

-The number of queries an account can issue per hour

-The number of updates an account can issue per hour

-The number of times an account can connect to the server per hour

-The number of simultaneous connections to the server by an account

See http://dev.mysql.com/doc/refman/5.7/en/user-resources.html for more info.

I can't seem to find a CPU/memory locked solution, but will update this post if I find one.

like image 103
adavea Avatar answered Oct 31 '22 10:10

adavea
Sign in to Comment

Related questions

How do we store chat conversations on MySQL database? XMPP Openfire
#1072 - Key column 'role_id' doesn't exist in table
Laravel 4 seed returns successful but nothing in database
Unicode (Greek) characters are stored in the database like "??????"
MySQL different storage engines for tables
Mysql update query, what if value didn't change? how do I check for that?
Way to automatically truncate a String column using JPA / Spring / Hibernate?
Insert into MySQL from Chrome Extension
A new table per client in MySQL?
Alter and Optimize sql query
How to efficiently design database of multi list application
How can I create a complex query that sum conditions with 2 tables?
Why is MySQL is giving an incorrect count for a simple query?
PhpMyAdmin | Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate xxxxxx bytes) in Unknown on line 0
How to make node.js and mysql work together?
Extracting alpha and numeric parts from a column
Some advice on DB design for simple booking system
Best practice for fixed number of strings in MySQL?
Fatal error: Call to undefined method mysqli_stmt::get_result() [duplicate]
Two Primary Keys

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!