Domain Name Hosting, Java Programming - Cheap Web Hosting Blog

Chapter 11 . Server Configurations 299 Figure 11-8: The [mysqld] section from the my-huge.cnf example configuration file for MySQL. When the performance of the MySQL server is degraded, it is time to analyze why it is happening. One item that can assist, though it does create performance degradation itself, is the log option in the configuration file. Using the logfunction, you can ensure that all queries to the server are logged (see Figure 11-9). Using this log- file, you can then determine whether queries can be optimized or whether you have to fine-tune a server variable. To enable the logfunction, add the keyword log within the [mysqld] section of the MySQL configuration file. Other logging options, as defined in Table 11-1, can assist in diagnosing what parameters and variables are working and which ones might not be. With some patience while tuning the server, you can use these logging options to help make MySQL s performance exceptional. Tip Be sure to turn off logging when it s needed; it can degrade performance. Caution Be careful not to increase too many values at once or make memory-based values too large. This can cause disk-swapping behavior as the server runs out of available RAM and has to use (slower) virtual memory.
We recommend high quality webhost to host and run your jsp application: christian web host services.

298 Part III . Administration Figure 11-7: The [mysqld] section from the my-large.cnf example configuration file for MySQL. The [mysqld] section from the example configuration file is shown in Figure 11-8. Notice how the configuration file examples differ from each other. In the example configuration file for Hugedatabase servers, the buffer sizes take advantage of the large amounts of RAM memory available for use. The configuration-file example for Small servers uses much less RAM for buffers and other parameters. Developing your own configuration file The first recommendation I have is to use an example file that closely matches your configuration not only of your machine, but also of your planned application(s). For example, I use the my-small.cnf file for the example server that I m using a Pentium 200MMX with 96MB RAM. After starting the database server, monitor the variables and performance through the use of the mysqladmin extended-status command. Of course, your users will tell you if the database is performing slowly.
From our experience, we can recommend PHP Web Hosting services, if you need affordable webhost to host and run your web application.

Chapter 11 . Server Configurations 297 The second file, my-medium.cnf, is for machines with smaller amounts of memory (under 128MB). If MySQL is the sole application (or one of a few primary applications) running on a machine like this, then the medium configuration file may be for you. In addition, if you have 128MB to 256MB and MySQL will be working with other applications (such as Apache or other services), the medium configuration would be a good starting point for a configuration file. The example section of the configuration file for [mysqld]is shown in Figure 11-6. The third configuration file, my-large.cnf, is meant for systems that have more than 512MB RAM, though less than 1GB RAM. The server is mainly responsible for database tasks with MySQL therefore it isn t running many other services. Of course, running a Web server for an interface would probably be acceptable. However, a production Web server for public use would be better run on a separate server. Figure 11-6: The [mysqld] section from the my-medium.cnf example defaults file for MySQL The [mysqld] section for my-large.cnf is shown in Figure 11-7. Finally, the example configuration file for the largest configurations is myhuge. cnf. This file is meant as an example file for systems with 1GB RAM or more, where MySQL is the sole application or service running on the server. If you intend to run any other applications or services, you should run them servers other than the MySQL server and you can use this example file as a good starting point. If you are running a MySQL server in such an environment, you will probably have to set other variables specifically to match your application.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

296 Part III . Administration Sample Configuration Files Four sample configuration files come with MySQL. These files provide defaults based on the hardware that MySQL server will be run on. The sample configuration files can provide a good starting point for defaults. For most applications, the default settings will work just fine. Examination of the sample configuration files The sample configuration files can be found in the support-files directory of your MySQL installation. The four files range in recommended use from machines with less than 64MB RAM to machines with 1G to 2G RAM. For each of the files, I m going to examine the main server portion underneath the [mysqld]section, and also other changes that take place in the file to affect other applications such as Mysqldump and so on. The first file, for machines with less than 64MB RAM, is called my-small.cnf. MySQL AB recommends this configuration file for use where MySQL is not the primary application, and is only used now and then for minor database functions. The relevant section from my-small.cnf is shown in Figure 11-5. Figure 11-5: The [mysqld] section from the my-small.cnf example defaults file for MySQL
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

Chapter 11 . Server Configurations 295 Caution Running the testing suite can cause performance problems on the MySQL server. I recommend against running the testing suite during peak usage or in a production environment. Currently the tests are not multithreaded; they open one connection at a time to the MySQL server. This does limit the ability of the tests to truly simulate a real- world environment where multiple users or threads frequently use the database simultaneously. In future versions, MySQL AB will add to the testing suite to run multiple threads. If the tests are taking an incredibly long time and you think something may have gone wrong, you can check the progress of the tests with a couple commands on the MySQL server. Running the SHOW PROCESSLIST statement or mysqladminprocesslist command will a single thread running for the tests, as shown in Figure 11-4. Note the different values in the Info field as the testing suite runs INSERT statements. Figure 11-4: The mysqladmin processlist shows the progress of the testing suite, in this instance the INSERT tests. Because the tests simulate so many different applications and implementations, if you know you re going to have an INSERT-heavy application, you can limit the testing to just the INSERTtest. Then, by tweaking variables to improve INSERT performance and rerunning the test, you can see whether you get a noticeable performance improvement from the new settings.
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

294 Part III . Administration . Inserts . Selects . Query optimization and other aspects of the server The final test in the suite is called the Wisconsin test. (And no, this isn t something I m just making up because I m from the state of Wisconsin.) The Wisconsin test is a comprehensive set of tests to look at query optimization and performance of other aspects of the server. The sql-bench directory contains a number of scripts, as shown in Figure 11-3. Figure 11-3: A directory listing for the sql-bench directory in MySQL You can run the tests individually or all at once. Regardless of the whether you run them one at a time or all at once, you must specify at least a password to enable the programs to run. Other command-line switches are available to specify user and host. For example, to run the test-selectapplication, the following command line would be used: ./test-select –password=password As you can see, you have to specify the actual password on the command line for the application to run. If you specify the user, do so with the –user=username syntax. To specify the host, use –host=hostsyntax. The script that calls all the other testing applications (and runs them all in sequence) is called run-all-tests. This script also requires that the password be given as an argument. Running this script on a slower server say, a Pentium 200MMX with 96MB RAM can increase the server load (not to mention that the tests themselves take hours). Therefore I would not recommend running these tests in a production environment.
We recommend cheap and reliable webhost to host and run your web applications: Coldfusion Web Hosting services.

Chapter 11 . Server Configurations 293 All the latest copies of the Perl-DBI and the Msql-Mysql-modules can be found on CPAN, http://www.cpan.org. Perl is normally included with Linux, though you may not have installed it when you installed the operating system. Follow the instructions included with the Perl- DBI for installation of the software. The Msql-Mysql-modules ask some relevant questions during the installation, see Figure 11-2. Figure 11-2: Running Makefile.PL for the Msql-Mysql-modules poses some questions about how you would like to configure the modules. How you answer the questions for installation largely depends on how you intend to use the modules, and whether you have a previous version installed. If you intend to use mSQL through a Perl application, you may want to consider installing the module for that in addition to the MySQL module. In Figure 11-2, I simply select MySQL because I don t plan to use mSQL, via Perl or otherwise, for the examples. Running the tests Once you have the prerequisites installed you can run the Benchmark applications and Crash-me tests. The applications are located in the sql-bench directory in your MySQL server directory. The Benchmarking suite includes various tests for different types of applications to look at different aspects of server performance in particular, how it handles some crucial aspects of database operation: . Big tables . Alteration of tables . Connections . Creation of tables
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

292 Part III . Administration contained within one program aptly titled crash-me, and also in the sql-bench directory. In the next section I ll look at some of the benchmarking applications included with MySQL. Tip Even with the tests and benchmarking, you still need a way to simulate a real- world load on a database server. A program called Super-smack does that job; you can use it to try out various values as you set parameters until you get the ones that work best for your configuration. Super-smack is available for free from MySQL AB. To get Super-smack, go to the Contributed Software section of the MySQL Web site http://www.mysql.com/downloads/contrib.html. . Sample configuration files let you try out different-size implementations. Look for these sample configs in the support-filesdirectory of your MySQL installation. Four sample configuration files are included: my-small.cnf my-medium.cnf my-large.cnf my-hugr.cnf The sample configuration files are covered in detail later in this chapter. MySQL Benchmarking and Testing Included with the MySQL software are applications and scripts for testing the limits and variables of the MySQL server. These tests can help to improve performance by showing obvious bottlenecks on the actual hardware with close to real-world scenarios. Using specific tests you can simulate how your server will be used and see what improvements must be made. I ve had times where running the tests saved me from implementing a server on hardware that couldn t stand up to the load for the application to be run. Requirements for the applications The Benchmark and Crash-me applications use a number of different types of statements and functions. The applications are included with Binary installations of MySQL and MySQL-Max as well as with the MySQL source code. To use the applications from an RPM-based installation, make sure you install the mysql-bench RPM (in addition to the other MySQL RPMs). Additional requirements for the benchmarking and Crash-me applications are as follows: . Perl . Perl-DBI . Msql-Mysql-modules
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

Chapter 11 . Server Configurations 291 Although the SHOW VARIABLES output given here does contain even more variables, these are beyond the scope of this chapter. So many variables, so little time As you can see from Table 11-1 and the variable printout, I speak the truth about the large number of server variables and settings available for an administrator to work with. When you add capabilities such as replication, even more variables and settings must be considered. At this point you may be asking, Which variables and settings do I really have to change and which ones can be left at the defaults? I certainly can t preach that all variables must be set for each server; in my view, that s neither true nor practical. Responding to testing and feedback from users of MySQL, MySQL AB has set defaults for most variables that will work well. Another reason not to set all the variables is that the MySQL server itself is quite efficient for most uses of the database anywhere from just a few records to a few hundred thousand or more. I ve worked with large databases (millions of rows) on what I would consider normal hardware Pentium-class computers with 128MB RAM and noticed no performance problems that would make me want to change too many server variables. However, working with specific applications or databases may require that you set or change variables to improve performance of the MySQL server. Some reasons you may have to set variables include the following: . A specific application performs operations on the database slow or cause the server to lag. . The use of lower-end hardware or small amounts of resources. . Using MySQL on a server that runs many other applications and services. . Inefficient or poorly written applications that cause problems for the server or database. . A desire to improve your server s performance. Coming back to the original question in this section, how do you know which variables to set if you want optimal performance from MySQL? Luckily, MySQL AB has done some of the homework for many implementations; two features of its installation program can help you optimize MySQL: . A suite of applications helps you benchmark your server s performance. MySQL also includes another set of tests known as the Crash-me tests. Together with the MySQL Benchmark applications, the Crash-me tests help you give your server a vigorous test. These applications and scripts help determine and test the limits of your server by simulating real-world scenarios. The benchmarking applications are a series of tests contained in the sqlbench directory within your MySQL installation. The Crash-me tests are
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.

290 Part III . Administration *************************** 77. row Variable_name: safe_show_database Value: OFF *************************** 78. row Variable_name: server_id Value: 1 *************************** 79. row Variable_name: skip_locking Value: ON *************************** 80. row Variable_name: skip_networking Value: OFF *************************** 81. row Variable_name: skip_show_database Value: OFF *************************** 82. row Variable_name: slow_launch_time Value: 2 *************************** 83. row Variable_name: socket Value: /tmp/mysql.sock *************************** 84. row Variable_name: sort_buffer Value: 65528 *************************** 85. row Variable_name: table_cache Value: 4 *************************** 86. row Variable_name: table_type Value: MYISAM *************************** 87. row Variable_name: thread_cache_size Value: 0 *************************** 88. row Variable_name: thread_stack Value: 65536 *************************** 89. row Variable_name: transaction_isolation Value: READ-COMMITTED *************************** 90. row Variable_name: timezone Value: CST *************************** 91. row Variable_name: tmp_table_size Value: 33554432 *************************** 92. row Variable_name: tmpdir Value: /tmp/ *************************** 93. row Variable_name: version Value: 3.23.39 *************************** 94. row Variable_name: wait_timeout Value: 28800
We would like to recommend you tested and proved virtual web hosting services, which you will surely find to be of great quality.