Re: Tuning a MySQL desktop database
Rohit, RP Khare wrote: We are testing MySQL in production environment with real data. When the application is ready and all tests have been conducted well, we will finally migrate to MySQL Enterprise. At present our application is running on a desktop machine with MySQL 5.1 Community Edition installed on it. MySQL 5.1 is using default settings. Machine is: Pentium 4 with 256 MB RAM. What settings I need to change to tune MySQL? I really do not want to offend you, but the question is silly. If there were some way to definitely improve MySQL's performance without causing any drawbacks or problems, it would be used in the default settings. Default settings are meant to be usable for many installations, but cannot be optimum for all. Users can leave them as provided and start running their application, watch them, and check whether there are some bottlenecks. Once they see these, they can change the settings so as to avoid or at least reduce these bottlenecks (as much as the hardware permits). We readers here will never know what your bottlenecks are unless you tell us. It might be cache sizes, number of concurrent users, select strategies, ... The only general remark I dare make: 256 MB may be very little RAM for most database servers, will be sufficient only if you have few users, not much data, or can tolerate slow response times. Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tuning a MySQL desktop database
Thanks for the answer. Maximum five users will work. The machine on which I am testing is the minimum configuration my client has. Attached is the My.ini file. . Rohit. Date: Tue, 11 Nov 2008 16:21:07 +0100 From: [EMAIL PROTECTED] Subject: Re: Tuning a MySQL desktop database To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Rohit, RP Khare wrote: We are testing MySQL in production environment with real data. When the application is ready and all tests have been conducted well, we will finally migrate to MySQL Enterprise. At present our application is running on a desktop machine with MySQL 5.1 Community Edition installed on it. MySQL 5.1 is using default settings. Machine is: Pentium 4 with 256 MB RAM. What settings I need to change to tune MySQL? I really do not want to offend you, but the question is silly. If there were some way to definitely improve MySQL's performance without causing any drawbacks or problems, it would be used in the default settings. Default settings are meant to be usable for many installations, but cannot be optimum for all. Users can leave them as provided and start running their application, watch them, and check whether there are some bottlenecks. Once they see these, they can change the settings so as to avoid or at least reduce these bottlenecks (as much as the hardware permits). We readers here will never know what your bottlenecks are unless you tell us. It might be cache sizes, number of concurrent users, select strategies, ... The only general remark I dare make: 256 MB may be very little RAM for most database servers, will be sufficient only if you have few users, not much data, or can tolerate slow response times. Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 _ Search for videos of Bollywood, Hollywood, Mollywood and every other wood, only on Live.com http://www.live.com/?scope=videoform=MICOAL# MySQL Server Instance Configuration File # -- # Generated by the MySQL Server Instance Configuration Wizard # # # Installation Instructions # -- # # On Linux you can copy this file to /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options # (@localstatedir@ for this installation) or to # ~/.my.cnf to set user-specific options. # # On Windows you should keep this file in the installation directory # of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To # make sure the server reads the config file use the startup option # --defaults-file. # # To run run the server from the command line, execute this in a # command line shell, e.g. # mysqld --defaults-file=C:\Program Files\MySQL\MySQL Server X.Y\my.ini # # To install the server as a Windows service manually, execute this in a # command line shell, e.g. # mysqld --install MySQLXY --defaults-file=C:\Program Files\MySQL\MySQL Server X.Y\my.ini # # And then execute this in a command line shell to start the server, e.g. # net start MySQLXY # # # Guildlines for editing this file # -- # # In this file, you can use all long options that the program supports. # If you want to know the options a program supports, start the program # with the --help option. # # More detailed information about the individual options can also be # found in the manual. # # # CLIENT SECTION # -- # # The following options will be read by MySQL client applications. # Note that only client applications shipped by MySQL are guaranteed # to read this section. If you want your own MySQL client program to # honor these values, you need to specify it as an option during the # MySQL client library initialization. # [client] port=3306 [mysql] default-character-set=latin1 # SERVER SECTION # -- # # The following options will be read by the MySQL Server. Make sure that # you have installed the server correctly (see above) so it reads this # file. # [mysqld] # The TCP/IP Port the MySQL Server will listen on port=3306 #Path to installation directory. All paths are usually resolved relative to this. basedir=C:/Program Files/MySQL/MySQL Server 5.1/ #Path to the database root datadir=C:/Documents and Settings/All Users.WINDOWS/Application Data/MySQL/MySQL Server 5.1/Data/ # The default character set that will be used when a new schema or table is # created and no character set is defined default
Re: Tuning a MySQL desktop database
I have only 3 things worth mentioning: You might want to lower max_connections... wouldn't want someone to actually start *using* 100 connections on a desktop box that only has 256MB RAM to begin with... it'll be in swap instantly (if it isn't already, before you've even started MySQL). Why InnoDB? If you're looking at low-concurrency (5 user max, you said) and low memory usage, you might want to leave it at the default of MyISAM, and then use 'skip-innodb' for the memory savings... unless you've got a good reason to change it. It's not a lot, but with only that small amount to work with every little bit will help. That'll save you $innodb_buffer_pool_size amount of memory at least (plus the actual InnoDB code itself). Depending on what you're trying to do, you might want to consider something like SQLite or HSQLDB instead. Both should be more lightweight than MySQL. As for other tweaks to make to MySQL itself, I don't have much to offer. Most tweaks depend almost entirely on the workload in question... like another respondent said, if there were tweaks that everyone used, they'd be the defaults by now :). You might try out these two scripts... they've been very helpful diagnosing MySQL performance issues for me on Linux servers. Good luck getting them going on Windows though... Cygwin might be necessary :) http://www.day32.com/MySQL/ http://wiki.mysqltuner.com/MySQLTuner Jake 2008/11/11 RP Khare [EMAIL PROTECTED]: Thanks for the answer. Maximum five users will work. The machine on which I am testing is the minimum configuration my client has. Attached is the My.ini file. . Rohit. Date: Tue, 11 Nov 2008 16:21:07 +0100 From: [EMAIL PROTECTED] Subject: Re: Tuning a MySQL desktop database To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Rohit, RP Khare wrote: We are testing MySQL in production environment with real data. When the application is ready and all tests have been conducted well, we will finally migrate to MySQL Enterprise. At present our application is running on a desktop machine with MySQL 5.1 Community Edition installed on it. MySQL 5.1 is using default settings. Machine is: Pentium 4 with 256 MB RAM. What settings I need to change to tune MySQL? I really do not want to offend you, but the question is silly. If there were some way to definitely improve MySQL's performance without causing any drawbacks or problems, it would be used in the default settings. Default settings are meant to be usable for many installations, but cannot be optimum for all. Users can leave them as provided and start running their application, watch them, and check whether there are some bottlenecks. Once they see these, they can change the settings so as to avoid or at least reduce these bottlenecks (as much as the hardware permits). We readers here will never know what your bottlenecks are unless you tell us. It might be cache sizes, number of concurrent users, select strategies, ... The only general remark I dare make: 256 MB may be very little RAM for most database servers, will be sufficient only if you have few users, not much data, or can tolerate slow response times. Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 What's on the ramp today could be on the streets tomorrow. Keep up with trends on MSN Lifestyle Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tuning a MySQL desktop database
At 09:36 AM 11/11/2008, you wrote: Thanks for the answer. Maximum five users will work. The machine on which I am testing is the minimum configuration my client has. Attached is the My.ini file. . Rohit. Date: Tue, 11 Nov 2008 16:21:07 +0100 From: [EMAIL PROTECTED] Subject: Re: Tuning a MySQL desktop database To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Rohit, Rhoit, Have you looked at High Performance MySQL: Optimization, Backups, Replication, and More from O'Reilly Books? It's available from Amazon. I believe Google Books has a few chapters of the previous version online at http://books.google.ca/books?id=iaCCQ13_zMICpg=PA103dq=mysql+performance+tuning#PPP1,M1 Mike RP Khare wrote: We are testing MySQL in production environment with real data. When the application is ready and all tests have been conducted well, we will finally migrate to MySQL Enterprise. At present our application is running on a desktop machine with MySQL 5.1 Community Edition installed on it. MySQL 5.1 is using default settings. Machine is: Pentium 4 with 256 MB RAM. What settings I need to change to tune MySQL? I really do not want to offend you, but the question is silly. If there were some way to definitely improve MySQL's performance without causing any drawbacks or problems, it would be used in the default settings. Default settings are meant to be usable for many installations, but cannot be optimum for all. Users can leave them as provided and start running their application, watch them, and check whether there are some bottlenecks. Once they see these, they can change the settings so as to avoid or at least reduce these bottlenecks (as much as the hardware permits). We readers here will never know what your bottlenecks are unless you tell us. It might be cache sizes, number of concurrent users, select strategies, ... The only general remark I dare make: 256 MB may be very little RAM for most database servers, will be sufficient only if you have few users, not much data, or can tolerate slow response times. Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- What's on the ramp today could be on the streets tomorrow. Keep up with trends on MSN Lifestyle Try it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tuning a MySQL desktop database
We are testing MySQL in production environment with real data. When the application is ready and all tests have been conducted well, we will finally migrate to MySQL Enterprise. At present our application is running on a desktop machine with MySQL 5.1 Community Edition installed on it. MySQL 5.1 is using default settings. Machine is: Pentium 4 with 256 MB RAM. What settings I need to change to tune MySQL? ... Rohit. _ Movies, sports news! Get your daily entertainment fix, only on live.com http://www.live.com/?scope=videoform=MICOAL
Re: Tuning a MySQL desktop database
Hello, Here, you can get some help: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ Regards. Salah NAIT-MOULOUD www.echovox.com | www.m-boost.com On Sun, Nov 9, 2008 at 12:33 PM, RP Khare [EMAIL PROTECTED] wrote: We are testing MySQL in production environment with real data. When the application is ready and all tests have been conducted well, we will finally migrate to MySQL Enterprise. At present our application is running on a desktop machine with MySQL 5.1 Community Edition installed on it. MySQL 5.1 is using default settings. Machine is: Pentium 4 with 256 MB RAM. What settings I need to change to tune MySQL? ... Rohit. _ Movies, sports news! Get your daily entertainment fix, only on live.com http://www.live.com/?scope=videoform=MICOAL
RE: Tuning a MySQL desktop database
Quiesce ALL slaves if you have master/slave DB's Once you've implemented explain plans..check any SQL statements which produce Full Table Scans If so You'll want to implement Indexes on compsite columns for those join conditions to implement a Unique Constraint you will need to extract out the data and reload as The table now accepts unique data items only Get familiar with export routines available via mysqladmin to export the data recreate the Tables with the Unique column constraint create the indexes to use those unique columns and finally use mysqladmin to import the data to those tables HTH Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Sun, 9 Nov 2008 12:44:29 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Tuning a MySQL desktop database CC: mysql@lists.mysql.com Hello, Here, you can get some help: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ Regards. Salah NAIT-MOULOUD www.echovox.com | www.m-boost.com On Sun, Nov 9, 2008 at 12:33 PM, RP Khare [EMAIL PROTECTED] wrote: We are testing MySQL in production environment with real data. When the application is ready and all tests have been conducted well, we will finally migrate to MySQL Enterprise. At present our application is running on a desktop machine with MySQL 5.1 Community Edition installed on it. MySQL 5.1 is using default settings. Machine is: Pentium 4 with 256 MB RAM. What settings I need to change to tune MySQL? ... Rohit. _ Movies, sports news! Get your daily entertainment fix, only on live.com http://www.live.com/?scope=videoform=MICOAL _ Stay up to date on your PC, the Web, and your mobile phone with Windows Live http://clk.atdmt.com/MRT/go/119462413/direct/01/
