Solved

MySQL script tuning primer in windows machine

Posted on 2011-02-13
9
1,961 Views
Last Modified: 2012-05-11
How can I run the script to check the variables and performance of MySQL

http://www.day32.com/MySQL/tuning-primer.sh

In a windows machine?

0
Comment
Question by:lnrivera
  • 4
  • 3
  • 2
9 Comments
 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 34882781
You can not run this on Windows. This script is made for *nix.
0
 

Author Comment

by:lnrivera
ID: 34882946
Is there any emulation? or some scripts like this for windows?

0
 
LVL 20

Expert Comment

by:Muhammad Wasif
ID: 34882990
I have not seen such a script for Windows yet.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 7

Expert Comment

by:printnix63
ID: 34883015
There are implementations of "unix environments" like Cygwin and MKS, but this will still not let you run this script due to the hard coded path names they have inside the script.
I do have some kind of Unix shell implementation, which always claims to not now tput, and then even so I run mysql you cannot run the script without changes due to the path issues.

Basically, what exactly do you need to know about the performance and variables?
When you call "mysql --help" you already get a part of variable info outside the mysql:

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- -----------------------------
auto-rehash                       TRUE
character-sets-dir                (No default value)
default-character-set             latin1
compress                          FALSE
database                          (No default value)
delimiter                         ;
vertical                          FALSE
force                             FALSE
named-commands                    FALSE
local-infile                      FALSE
no-beep                           FALSE
host                              (No default value)
html                              FALSE
xml                               FALSE
line-numbers                      TRUE
unbuffered                        FALSE
column-names                      TRUE
sigint-ignore                     FALSE
port                              0
prompt                            mysql>
quick                             FALSE
raw                               FALSE
reconnect                         TRUE
shared-memory-base-name           (No default value)
socket                            (No default value)
table                             FALSE
debug-info                        FALSE
user                              (No default value)
safe-updates                      FALSE
i-am-a-dummy                      FALSE
connect_timeout                   0
max_allowed_packet                16777216
net_buffer_length                 16384
select_limit                      1000
max_join_size                     1000000
secure-auth                       FALSE
show-warnings                     FALSE

Open in new window

0
 
LVL 7

Expert Comment

by:printnix63
ID: 34883050
Additionally, when you are logged in, you can easily enlist further parameters that you can check, like "status" that at least provides information if you have "slow queries"

Besides below short view, you can go to:
use INFORMATION_SCHEMA;
show tables;

There you see what information you can query from the database, like e.g.
select * from global_variables;
select * from global_status;

And from there you can also get partial information like:
mysql> select * from global_variables where variable_name like 'SQL%';
(result see below in the code window:)
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear command.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
notee     (\t) Don't write into outfile.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute a SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

mysql> status
--------------
mysql  Ver 14.12 Distrib 5.0.18, for Win32 (ia32)

Connection id:          1
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.1.41
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 8 min 8 sec

Threads: 1  Questions: 3  Slow queries: 0  Opens: 19  Flush tables: 1  Open tables: 12  Queries per second avg: 0.6
--------------
+--------------------------+----------------------+
| VARIABLE_NAME            | VARIABLE_VALUE       |
+--------------------------+----------------------+
| SQL_QUOTE_SHOW_CREATE    | ON                   |
| SQL_LOG_UPDATE           | ON                   |
| SQL_LOW_PRIORITY_UPDATES | OFF                  |
| SQL_NOTES                | ON                   |
| SQL_AUTO_IS_NULL         | ON                   |
| SQL_SELECT_LIMIT         | 18446744073709551615 |
| SQL_BIG_TABLES           | OFF                  |
| SQL_SLAVE_SKIP_COUNTER   |                      |
| SQL_MODE                 |                      |
| SQL_MAX_JOIN_SIZE        | 18446744073709551615 |
| SQL_BIG_SELECTS          | ON                   |
| SQL_BUFFER_RESULT        | OFF                  |
| SQL_LOG_BIN              | ON                   |
| SQL_WARNINGS             | OFF                  |
| SQL_LOG_OFF              | OFF                  |
| SQL_SAFE_UPDATES         | OFF                  |
+--------------------------+----------------------+
16 rows in set (0.01 sec)

Open in new window

0
 

Author Comment

by:lnrivera
ID: 34883056
We want to know a good values for a Windows XP 64bits with 4GB of RAM (All tables are innodb)
0
 
LVL 7

Expert Comment

by:printnix63
ID: 34883166
You mean, you want a base for configuring the database on a Windows XP 64 bit system that has 4GB of RAM ... but what are you doing, what is the database intended for?

When you install the database, you can set up some basic preconfiguration, e.g. depending on how many connections you expect etc. As well, what MySQL Version do you use, as with the newer versions you have improved concurrency etc. I am afraid from the description available we'll not be able to provide much input.

Performance tuning is a lot of looking into the system where you have bottle necks and removing them, checking where you can remove load or provide more resources, but with the given information it is more wild guessing.
0
 

Author Comment

by:lnrivera
ID: 34883264
Is there any GUI tool to help with a basic tuning?
0
 
LVL 7

Accepted Solution

by:
printnix63 earned 500 total points
ID: 34884047
Well, you can download from mysql.com the workbench - which is an Administration GUI.
It brings (at least with Mysql 5.1.5x) some references to "Performance BLOG's" and a commandline tool "mysqlprocgrep" that lets you dump some information about connections of users and processes. Not really much, but it's free.

You will as well find there a plethora of documents and discussions, which as well are directed onto how improve performance.
http://www.mysqlperformanceblog.com/?s=mysql+performance

If you are ready to spend some money you could go for the MySQL Enterprise Monitor, which is claimed to provide a lot more information about what the server does and where it spends time ...

You can find other tools, which you can get as a 30 day trial e.g. at
http://www.webyog.com/en/downloads.php#monyog

On regard of your shell script, eventually you can try this one, it is a perl script, and basically, perl is available for windows...

http://rackerhacker.com/mysqltuner/
# For the latest updates, please visit http://mysqltuner.com/
# Subversion repository available at http://tools.assembla.com/svn/mysqltuner/

but I haven't tested it.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Best database setup for image uploads 6 63
showing numeric numbers 2 36
mysql disables rename 4 68
Present Absent from working date rage 11 16
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question