CruzIT
Tuesday October 27, 2020 - 10:43:41 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: 73 days, 4 hours, 13 minutes , 52 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  110594  Unique Visitors (IP Addresses) to this site.

Current users online : 22
Maximum users at a time : 29
Last 25 attacks have come from:
153.127.67.228
217.182.217.24
78.46.60.149
158.174.61.67
3.220.231.94
85.214.25.136
62.210.185.4
34.69.69.205
52.250.19.179
65.52.32.40
118.189.139.212
82.165.184.192
5.182.211.238
94.99.112.138
51.81.83.124
35.246.175.1
164.132.23.43
13.72.82.82
65.52.165.138
128.199.227.236
51.81.80.140
34.70.49.158
188.53.7.172
197.40.158.14
156.219.87.189

There are currently 25 unique IP addresses blacklisted.

Public cruzit.com Server Status
server offline limelight  web service
 Aw Crap!, It's Off-Line
server offline limelight  mail service
 Aw Crap!, It's Off-Line
server online www  web service
 Is On-Line
server online mx 08  mail service
 Is On-Line
server online www01  F/O web service
 Is On-Line
server online mx 09  mail service
 Is On-Line
server online tertiary  DNS service
 Is On-Line
server online mx 10  mail service
 Is On-Line

©Copyright 2004-2020 - 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.
MySQL® is a registered trademark of MySQL AB in the United States, the European Union and other countries.
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