Hard Drives / Mysql performance
Moderator: General Moderators
Hard Drives / Mysql performance
I develop locally on my Windows machine. Mysql is pretty slow. CPU or memory do not appear to be the bottleneck though. That means its most likely the drives right?
Any way to measure/confirm that? And what drive setups do you guys use? Raptors? RAID? Solid State? I tried the solid state route and blew $400 on a drive that lasted only 4 months (Patriot). I guess it couldn't take the beating mysql gave it.
Any way to measure/confirm that? And what drive setups do you guys use? Raptors? RAID? Solid State? I tried the solid state route and blew $400 on a drive that lasted only 4 months (Patriot). I guess it couldn't take the beating mysql gave it.
-
jaiswarvipin
- Forum Commoner
- Posts: 32
- Joined: Sat Sep 12, 2009 3:43 pm
- Location: India
Re: Hard Drives / Mysql performance
there os so may tool are avalible like MYSQL Profiler which tell you all history and many more.
Re: Hard Drives / Mysql performance
josh, have you tuned MySQL memory usage?
http://www.mysqlperformanceblog.com/200 ... tallation/
http://www.mysqlperformanceblog.com/200 ... tallation/
There are 10 types of people in this world, those who understand binary and those who don't
Re: Hard Drives / Mysql performance
Memory does not appear to be the problem. For example I have a class that is a "match maker" of sorts. Matching a single entity can turn up 4,000 matches. That takes over a minute to run 4,000 insert statements (part of the spec). On my clients server it run in like 1 second flat though.
I am just looking at windows task monitor and I don't see memory or CPU being taxed more than normal. Is there any way to profile actual _disk activity_? and what kind of disks are best? I am looking for fastest seek time? or what?
I am just looking at windows task monitor and I don't see memory or CPU being taxed more than normal. Is there any way to profile actual _disk activity_? and what kind of disks are best? I am looking for fastest seek time? or what?
Re: Hard Drives / Mysql performance
Read again what vlad linked you to. MySQL does not use your entire system resources, and the defaults are very low for most common usage.
Apart from that, you can improve that insert routine you mentioned by inserting all records in one query instead of separate queries.
Apart from that, you can improve that insert routine you mentioned by inserting all records in one query instead of separate queries.
Re: Hard Drives / Mysql performance
Yes I could optimize that one algorithm, but that's just an example of the types of slow downs. It slows down *everything*. Ex ( running my re factoring suite takes 15 minutes as opposed to 15 seconds).
What do you suggest I don't have tuned right? I'm on INNODB tables and mysql is barely using 1% cpu and 3mb of memory. This is while running a "batch" task that was doing lots of querying.
What do you suggest I don't have tuned right? I'm on INNODB tables and mysql is barely using 1% cpu and 3mb of memory. This is while running a "batch" task that was doing lots of querying.
Code: Select all
# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is C:\mysql\data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[wampmysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
basedir=c:/wamp/bin/mysql/mysql5.1.36
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.1.36/data
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Disable Federated by default
skip-federated
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 128M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld]
port=3306Re: Hard Drives / Mysql performance
I'd suggest you to replace your config file with my-huge.cnf, uncomment the InnoDB section and see whether it's better.
This should help, though fast hard disks / RAID are always recommended.
So, just try it.
This should help, though fast hard disks / RAID are always recommended.
So, just try it.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Hard Drives / Mysql performance
That seems to crash my mysql server. With or without that part commented. Nothing gets written to log. Just my application shows "cant connect to localhost". The only thing that fixed it was putting it back to my-small.ini
How big of a difference do drives make? Pretty much *all* of the dedicated linux servers I haved worked on are really fast. Is it a windows vs linux thing possibly? I'm sure these *.ini files will help some, if I could figure them out I guess. The tweaking I have done so far does not seem to be changing anything as drastically as the difference I have noticed between my machine and these other servers though. I mean setting up and tearing down 10 Million rows in a minute flat. On my computer it takes upwards of an hour.
How big of a difference do drives make? Pretty much *all* of the dedicated linux servers I haved worked on are really fast. Is it a windows vs linux thing possibly? I'm sure these *.ini files will help some, if I could figure them out I guess. The tweaking I have done so far does not seem to be changing anything as drastically as the difference I have noticed between my machine and these other servers though. I mean setting up and tearing down 10 Million rows in a minute flat. On my computer it takes upwards of an hour.
Re: Hard Drives / Mysql performance
What extension are you using to connect to MySQL? (mysqli, PDO etc) I remember some of those extensions underperform in their Windows version
Re: Hard Drives / Mysql performance
The CPU usage is low due to the fact that MySQL is severely limited by how much RAM it is allowed to use. You will definitely need to increase the RAM limits.josh wrote:mysql is barely using 1% cpu and 3mb of memory.
The crashing is due to the InnoDB data files. When you change one of the InnoDB settings, MySQL will fail to start due to an inconsistency between the size specified in my.cnf and the actual size of the ibdata* files. You need to shutdown MySQL, modify my.cnf, delete ibdata* (in /var/lib/mysql/ on my box), delete ib_logfile* files, then restart the server. The first time MySQL starts it will recreate the ibdata file which may take a while. This may result in you receiving a message stating that MySQL failed to start, when in fact it didn't. Subsequent restarts will be fine.
Please note, this procedure can sometimes damage existing InnoDB database tables. It is recommended that any existing InnoDB tables be backed up so that you can restore them if you need to.
Hope that helps.