Linked Questions
34 questions linked to/from How large should be mysql innodb_buffer_pool_size?
1
vote
2
answers
25k
views
How to set Innodb Buffer Pool Size [duplicate]
I have a database using innodb completely. Its now having a innodb_buffer_pool_size at 128MB. Which I know is very less for my user.
But no matter what I do it won't change. I have added a value of ...
- 133
142
votes
5
answers
255k
views
Possible to make MySQL use more than one core?
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help
Setup
The servers are quite hefty with an OLAP/...
- 70.1k
64
votes
6
answers
87k
views
About single threaded versus multithreaded databases performance
H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded.
My question is: when does a multi-thread database become more interesting than an ...
- 1,481
24
votes
6
answers
44k
views
DELETE command not completing on 30,000,000 row table
I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data inserted due to an error on behalf of our programmer. ...
- 551
39
votes
2
answers
66k
views
Multi cores and MySQL Performance
Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the ...
- 1,271
25
votes
2
answers
39k
views
What to set innodb_buffer_pool and why..?
I have 170GB of InnoDB index and Data.
I have to readjust the innodb_buffer_pool size for better performance.The Max table size of a InnoDB table(Index+data) is 28GB.
So what should be the optimal ...
- 9,717
5
votes
2
answers
17k
views
MySQL performance impact of increasing innodb_buffer_pool_size
I have two databases one of size 170 MB and second one is of 15 MB, both having INNODB tables and innodb_buffer_pool_size is set to 8 MB.
My queries are responding well now i.e under mili seconds.
...
- 1,361
2
votes
1
answer
23k
views
Configuring MySQL (my.cnf) for MyISAM and InnoDB
I want to convert some (not all) the MyISAM tables to InnoDB.
Current my.cnf
Show engines output
Server has two 6-core Xeon processors (2 threads per core = 24 threads)
What would be the best ...
- 55
3
votes
2
answers
15k
views
MySQL 5.5 High CPU Usage
I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was ...
- 63
8
votes
3
answers
6k
views
Occasionally simple queries are slow with InnoDB caused by slow query end [closed]
We are having serious issues with one of our productions servers. The setup in question is CentOS 7 64-bit server with 16 GB of ram and 250GB SSD (so the setup should be very fast). The LEMP stack is ...
8
votes
1
answer
7k
views
Huge differences of performance of MySQL in two servers
We have a MySQL server installed in two different machines, a testing server and a production server, both windows, which is used by a web application.
The problem is that there are HUGE performance ...
- 81
2
votes
2
answers
12k
views
Would increasing innodb buffer pool size prevent a mysql crash?
My server keeps running out of memory for mysql. I keep getting. 150708 3:39:09 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 258064 bytes)
When I looked in my.cnf I saw that ...
- 67
4
votes
2
answers
6k
views
Why would increase in innodb_buffer_pool_size slow down MySQL?
5.1.68-cll - MySQL Community Server on CentOS
The system has 32GB of RAM.
I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB).
Time taken for a series of identical operations ...
- 1,240
2
votes
1
answer
5k
views
Can I reduce MySQL memory usage, or do I need to add more RAM?
I have a VPS with 2.25GB of RAM. MySQL is using 1.4GB with only four users on my site right now. It seems to use this amount even when no users are online. The available memory of my server regularly ...
- 201
5
votes
1
answer
8k
views
AWS Aurora running out of freeable memory and crashing
We have an issue with our AWS Aurora MySQL instance running out of freeable memory and crashing as a result. AWS's response was to upgrade to a bigger instance, but I don't feel that will necessarily ...
0
votes
2
answers
6k
views
Best Innodb Buffer Pool Size for 8GB RAM?
I found out that my MySQL server is configured with only 128MB for innodb_buffer_pool_size. That is not enough considering that I have a 8GB RAM server. It hosts MySQL server and an Apache/Catalina ...
1
vote
2
answers
6k
views
inno_db_buffer_pool_size and key efficiency
I have the following metrics from mysql workbench
After I have set innodb-buffer-pool-size to 6G, according to this answer's query on how to determine the recommended size.
While the used buffer ...
- 111
1
vote
1
answer
2k
views
Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?
We have a mysql database that has roughly 80 gigabytes (GB) of data using inno_db engine. We are looking to increase performance on the database (we looked at the slow log and gone through and ...
- 641
1
vote
1
answer
2k
views
innodb_buffer_pool & innodb_buffer_pool_instances [duplicate]
I have a database dedicated server with one quad-core cpu and 48GB Memory.
Total database size is about 6GB and my ultimate goal is to get the maximum performance out of this system.
At First, to ...
- 23
1
vote
1
answer
2k
views
mysql innodb_buffer_pool_size let me understand
I couldn't find answer for my situation.
I have a windows server 2008 with 2 cores and 7 GB RAM (Azure D1 VM).
I'm running mariadb 10.0.14 on it. I have 2 databases (1.5GB and 9GB in size).
...
- 11
1
vote
1
answer
1k
views
Hardware specs for MySQL database with millions of records
For 35 million records I have this table
CREATE TABLE IF NOT EXISTS `records` (
`code` varchar(12) NOT NULL,
`type` varchar(255) NOT NULL,
`used` set('y','n') NOT NULL default 'n',
PRIMARY ...
2
votes
1
answer
1k
views
Safe values for InnoDB variables when using FULLTEXT searches
I'm using Maria DB, version 10.2.22, where one database column uses FULLTEXT for a broad document searching. However, I've ran into a "Table handler out of memory" on some searches. The ...
- 21
1
vote
1
answer
768
views
Mysql 5.7 innodb_buffer_pool_size - zero I/O?
I have a question regarding Mysql server 5.7, put the correct value for innodb_buffer_pool_size or increase RAM. Maybe I'm wrong, I'm not an expert, but let me explain what is it about.
Server ...
- 13
1
vote
1
answer
672
views
Estimation of appropriate innodb buffer pool size
I'm reading this thread How large should be mysql innodb_buffer_pool_size?
And perform the querys suggested by RolandoMySQLDBA and I get 307 RIBPS and 264G. That's mean that I need 307GB of RAM?
...
- 111
1
vote
2
answers
510
views
Tuning MYSQL 8.0.32 for my server
EDIT:
User @mustaccio let me acknowledge that I must present the problem that motivates the need for the tuning:
There are querys running faster in an older server, less powerful, MySQL Version 5.7....
- 21
0
votes
2
answers
473
views
Optimization: Increase Innodb_pool_size useful?
We run an AWS RDS instance with 8vCPUs and 64GB memory.
All tables are using InnoDB engine.
I have kept the default setup of innodb_pool_size to be half the memory.
What I see is that almost 30Gb of ...
- 103
1
vote
1
answer
364
views
mysql multiple database prioritize innodb buffer pool size
With a mysql instance and multiple databases within that instance is there a way to allocate which database you would like to assign priority to the buffer pool size?
- 135
2
votes
1
answer
108
views
Which hardware you suggest?
I'm completely new to buying a database hosting and I would greatly appreciate your opinion about the following situation. I just created an App and I want to go live this weekend. I want to be ...
- 123
0
votes
1
answer
218
views
MySQL query very slow for one object, very fast for others
I have a table for Items and then a table for Parameters where each Item can have multiple Parameters. Each Parameter object has a foreign key back to its Item. I also have a Parameter Type table that ...
1
vote
2
answers
175
views
What settings should I use in my.cnf
We have about 5-6 different internal webapps (staff use only) that we will be transferring across to a new virtual server, however I need to setup our my.cnf settings first and I am lost (I am web ...
- 13