CruzIT
Thursday November 30, 2023 - 10:52:36 PM

Registered Linux User #440901    


 
Login    Register
Login Required

Performance Tuning MySQL Database Server

After you have MySQL Server up and running, some time should be set aside for tuning and performance analysis.
You may want to see Getting MySQL up and running if you haven't seen it yet.

Howto performance tune MySQL on Ubuntu or a Debian based machine.

Do these commands as an administrative user. Most of this will be done in command line with your favorite terminal - like gnome terminal or Eterm.

First: Connecting to MySQL

Open a terminal. Let's connect and see what kind of variables we're dealing with:

mysql --user=root --password=YOURMYSQLPASSWORD
You should get:
mysql >
Put in:
SHOW VARIABLES;
Returned will be all of the variables. You may want to remember this last command. In the future when you are looking at someone's server. It's a quick way to get started on troubleshooting or tuning.
Note: If you get stuck by typing in the wrong thing and have just "->" or for some other reason need to clear the buffer, type \c (W/O quotes) and hit enter.
Note2: To get out and return to the terminal, just type "exit" (W/O quotes).
You can also use mysqladmin. These commands are a quick in and out of mysql. They run the command and return to the terminal.
mysqladmin variables -u root -p
OR
mysqladmin extended-status -u root -p

A quick and dirty/simple report using mysqladmin:

mysqladmin extended-status -u root -p > /home/YOURDIRECTORY/mysqlxs.txt

Go to where you put it and have a look. (or just to make sure it's there: cat /home/YOURDIRECTORY/mysqlxs.txt)

Just like anything else, the more memory you throw at something, the better.

MySQL is very efficient and scalable and can actually run on very little memory. That said, it may need more memory allocated as tables start filling up and/or concurrant connection rates rise.
When tuning a MySQL server, the two most important variables to configure are key_buffer_size and table_cache. (More detail later on these.) You should first feel confident that you have these set appropriately before trying to change any other variables.
These examples show some typical variable values for different configurations:

More Coming Soon:

Have Fun!

Collecting For A Server Upgrade  

Time since last reboot: 16 days, 11 hours, 42 minutes , 10 seconds

@ Active Member Project Honeypot  email addresses

This page was last modified on 12/19/18 @ 09:26:37:pm
This file name: tuningmysql.php

Questions, Comments, Suggestions or Requests should be sent to:  ronnie@cruzit.com 

There have been  6736  Unique Visitors (IP Addresses) to this site.

Current users online : 19
Maximum users at a time : 62
Last 25 attacks have come from:
195.117.171.41
183.83.176.65
34.93.60.156
5.133.13.254
1.206.111.197
60.213.28.166
103.157.167.82
66.249.66.139
199.195.252.77
91.92.247.145
93.183.131.53
212.156.30.78
178.208.171.208
128.90.144.97
72.167.104.249
78.153.140.218
104.244.76.64
91.92.243.106
43.200.245.117
20.80.235.231
47.251.14.232
47.88.78.6
128.90.43.95
185.225.75.50
159.89.193.202

There are currently 25 unique IP addresses blacklisted.

Public cruzit.com Server Status
server online dbc001  db cluster service
 Is On-Line
server online www03  web service
 Is On-Line
server online www  web service
 Is On-Line
server online mx 08  mail service
 Is On-Line
server online www ha  F/O web service
 Is On-Line
server online mx 11  mail service
 Is On-Line
server online Primary  Client DNS
 Is On-Line
server online Secondary  Client DNS
 Is On-Line

©Copyright 2004-2023 - cruzit.com - Redd Enterprises™, Inc.,  All Rights Reserved.
Ubuntu® is a registered trademark of Canonical Ltd.
Linux® is a registered trademark of Linus Torvalds.
Apache® is a registered trademark of The Apache Software Foundation.
PHP® is a registered trademark of The PHP Group.
The MariaDB® a registered trademark of the MariaDB Corporation Ab.
MySQL® is a registered trademark of the Oracle Corporation Inc.
All other product and service names mentioned are the trademarks of their respective companies.

 

pretrial
pretrial
pretrial
pretrial
Let us know what you think! finance@homelandcomputersecurity.com