You are here: Home / 2010 / Archives for July 2010

Will Oracle kill MySQL?

by

I get asked this question often. It was mentioned again recently in a NYTECH executive breakfast with RedHat CIO Lee Congdon.

The short answer is No.

There is clear evidence that in the short to medium term Oracle will continue to promote and enhance MySQL. Some of these indicators include:

It is clear from these sources that Oracle intends to incorporate MySQL into Oracle Backup and Security Vault products. Both a practical and necessary step. There is also a clear mention of focusing on the Microsoft platform, a clear indicator that SQL Server is in their sights without actually saying it.

What is unknown is exact how and when features will be implemented. Also important is how much these may cost the end user. Oracle is in the business of selling, now an entire H/W and S/W stack. They also have a complicated pricing model of different components with product offerings. I assume this will continue. There are already two indications, InnoDBbackup included for Enterprise Backup (from April Keynote) and 5.1 enterprise split. (Note: while this split may have existed prior to Oracle, it is now more clearly obvious).

MySQL can never be seen as drawing away from any Oracle sales of the core entry level database product. It is likely Oracle will provide a SQL Syntax compatibility layer for MySQL within 2 years, however it will I’m sure be a commercial add-on. Likewise, I would suspect a PL/SQL lite layer within 5 years, but again at a significant cost to offset the potential loss of sales in the low end of the server market. There continues to be active development in the MySQL Enterprise Monitor, MySQL Workbench and MySQL Connectors which is all excellent news for users.

Moving forward, how long will this ancillary development of free tools continue? What will happen to the commercial storage engine, OEM and licensing model after the 5 year commitment? How will the MySQL ecosystem survive.? There is active development in Percona, MariaDB and Drizzle forks, however unless all players that want to provide a close MySQL compatible solution work together, progress will continue to be a disappointing disjointed approach. The 2011 conference season will also see a clear line with competing MySQL conferences in April scheduled at the same time, the O’Reilly MySQL conference in Santa Clara California and the Oracle supported(*) Collaborate 2011 in Orlando, Florida.

I have a number of predictions on what Oracle ME MySQL may look like in 5 years however this is a topic for a personal discussion.

Speaking at Surge Scalability 2010 – Baltimore, MD

by

I will be joining a great list of quality speakers including John Allspaw, Theo Schlossnagle, Rasmus Lerdorf and Tom Cook at Surge 2010 in Baltimore, Maryland on Thu 30 Sep, and Fri Oct 1st 2010. Surge 2010 Speaker - Baltimore, MD

My presentation on “The most common MySQL scalability mistakes, and how to avoid them.” will include discussing various experiences observed in the field as a MySQL Consultant and MySQL Performance Tuning expert.

Abstract:

The most common mistakes are easy to avoid however many startups continue to fall prey, with the impact including large re-design costs, delays in new feature releases, lower staff productivity and less then ideal ROI. All growing and successful sites need to achieve higher Availability, seamless Scalability and proven Resilience. Know the right MySQL environments to provide a suitable architecture and application design to support these essential needs.

Overview:

Some details of the presentation would include:

  • The different types of accessible data  (e.g. R/W, R, none)
  • What limits MySQL availability (e.g software upgrades, blocking statements, locking etc)
  • The three components of scalability – Read Scalability/Write Scalability/Caching
  • Design practices for increasing scalability and not physical resources
  • Disaster is inevitable. Having a tested and functional failover strategy
  • When other products are better (e.g. Static files, Session management via Key/Value store)
  • What a lack of accurate monitoring causes
  • What a lack of breakability testing causes
  • What does “No Downtime” mean to your organization.
  • Implementing a successful “failed whale” approach with preemptive analysis
  • Identifying when MySQL is not your bottleneck

If you missed MySQL Idiosyncrasies that BITE

by

I recently gave a webinar to the LAOUC and NZOUG user groups on MySQL Idiosyncrasies that BITE.

For the benefit of many viewers that do not use English as a first language my slides include some additional information from my ODTUG Kaleidoscope presentation in June.

Thanks to Francisco Munoz Alvarez for organizing.

MySQL Idiosyncrasies That Bite 2010.07

3 webinars on Upgrading MySQL

by

The IOUG Online Education Series: Get Real with Upgrades will include next week 3 different MySQL webinars. These are:

  • MySQL 5.1: Why and How to Upgrade by Sheeri Cabral on Tuesday, July 27, 12:00 p.m. – 1:00 p.m. CT
  • MySQL Upgrades With No Downtime by Sean Hull on Wednesday, July 28, 12:00 p.m. – 1:00 p.m. CT
  • MySQL Upgrade Best Practices by Matt Yonkovit on Thursday, July 29, 12:00 p.m. – 1:00 p.m. CT

Each speaker will be covering different areas and reasons for considering and implementing a successful MySQL Upgrade.

Installing WordPress on Oracle Enterprise Linux LAMP stack

by

A company blog can be easily configured in under 10 minutes using WordPress, a popular open source LAMP product that runs a reported 12+ million blogs including those found at CNN, NY Times, Wall Street Journal (WSJ), ZDNet, MTV, People Magazine, Playstation and eBay.

A company blog is a great way for the dissemination of information to your user base as well as enabling a means of user feedback via comments.

The following steps show you how to download, configure and get your WordPress blog operational.

Software Pre-Requisites

Software Installation

su -
cd /tmp
wget  http://wordpress.org/latest.tar.gz
cd /var/www/html
tar xfz /tmp/latest.tar.gz
mv wordpress blog

You can now visit http://localhost/blog and you will be presented with a message of an un-configured WordPress environment. You can streamline the MySQL portion of this configuration with the following commands.

cd blog
sed -e "s/database_name_here/blog/;s/username_here/blog_user/;s/password_here/sakila/" wp-config-sample.php > wp-config.php
mysql -uroot -p -e "CREATE SCHEMA blog"
mysql -uroot -p -e "CREATE USER blog_user @localhost IDENTIFIED BY 'sakila'"
mysql -uroot -p -e "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE on blog.* TO blog_user@localhost"

Returning now to http://localhost/blog you simply only need to specify a Title, password and email address, click Save and your Blog at http://localhost/blog is complete and operational.



MySQL Structures

Looking at the tables that are created by the installation process:

$ mysql -ublog_user -psakila blog

mysql> show tables;
+-----------------------+
| Tables_in_blog        |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
11 rows in set (0.00 sec)

mysql> SELECT table_name,engine,table_rows FROM information_schema.tables WHERE table_schema = 'blog';
+-----------------------+--------+------------+
| table_name            | engine | table_rows |
+-----------------------+--------+------------+
| wp_commentmeta        | MyISAM |          0 |
| wp_comments           | MyISAM |          1 |
| wp_links              | MyISAM |          7 |
| wp_options            | MyISAM |        109 |
| wp_postmeta           | MyISAM |          1 |
| wp_posts              | MyISAM |          2 |
| wp_term_relationships | MyISAM |          8 |
| wp_term_taxonomy      | MyISAM |          2 |
| wp_terms              | MyISAM |          2 |
| wp_usermeta           | MyISAM |         13 |
| wp_users              | MyISAM |          1 |
+-----------------------+--------+------------+
11 rows in set (0.00 sec)

Additional References

Short URL for this post rb42.com/oel-install-wordpress

Reviewing your MySQL installation on Oracle Enterprise Linux

by

After successfully Installing MySQL, let us take a look at an operational MySQL instance on your Oracle Enterprise Linux server.

User Management

By default there will be a new mysql user and group created. This user is used to run the mysqld process is generally not used for any other purpose.

$ grep mysql /etc/{passwd,shadow,group}
/etc/passwd:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
/etc/shadow:mysql:!!:14796::::::
/etc/group:mysql:x:27:

Binaries

MySQL binaries are found in /usr/bin.

$ ls -l /usr/bin/mysql*
-rwxr-xr-x 1 root root  314568 Feb 16 17:45 /usr/bin/mysql
-rwxr-xr-x 1 root root  110776 Feb 16 14:39 /usr/bin/mysqlaccess
-rwxr-xr-x 1 root root   35144 Feb 16 17:45 /usr/bin/mysqladmin
-rwxr-xr-x 1 root root  112944 Feb 16 17:45 /usr/bin/mysqlbinlog
-rwxr-xr-x 1 root root    7632 Feb 16 17:45 /usr/bin/mysqlbug
-rwxr-xr-x 1 root root   30576 Feb 16 17:45 /usr/bin/mysqlcheck
-rwxr-xr-x 1 root root    7632 Feb 16 17:45 /usr/bin/mysql_config
-rwxr-xr-x 1 root root    3670 Feb 16 17:44 /usr/bin/mysql_convert_table_format
-rwxr-xr-x 1 root root   22522 Feb 16 17:44 /usr/bin/mysqld_multi
-rwxr-xr-x 1 root root   13073 Feb 16 17:44 /usr/bin/mysqld_safe
-rwxr-xr-x 1 root root   75184 Feb 16 17:45 /usr/bin/mysqldump
-rwxr-xr-x 1 root root    6356 Feb 16 17:44 /usr/bin/mysqldumpslow
-rwxr-xr-x 1 root root   11648 Feb 16 17:44 /usr/bin/mysql_explain_log
-rwxr-xr-x 1 root root    3245 Feb 16 14:39 /usr/bin/mysql_find_rows
-rwxr-xr-x 1 root root     483 Feb 16 17:44 /usr/bin/mysql_fix_extensions
-rwxr-xr-x 1 root root    5834 Feb 16 17:44 /usr/bin/mysql_fix_privilege_tables
-rwxr-xr-x 1 root root   31431 Feb 16 17:44 /usr/bin/mysqlhotcopy
-rwxr-xr-x 1 root root   26160 Feb 16 17:45 /usr/bin/mysqlimport
-rwxr-xr-x 1 root root   13659 Feb 16 17:44 /usr/bin/mysql_install_db
-rwxr-xr-x 1 root root    6586 Feb 16 17:44 /usr/bin/mysql_secure_installation
-rwxr-xr-x 1 root root   16687 Feb 16 17:44 /usr/bin/mysql_setpermission
-rwxr-xr-x 1 root root   28224 Feb 16 17:45 /usr/bin/mysqlshow
-rwxr-xr-x 1 root root   14473 Feb 16 14:39 /usr/bin/mysql_tableinfo
-rwxr-xr-x 1 root root  158192 Feb 16 17:45 /usr/bin/mysqltest
-rwxr-xr-x 1 root root   42360 Feb 16 17:45 /usr/bin/mysqltestmanager
-rwxr-xr-x 1 root root   15464 Feb 16 17:45 /usr/bin/mysqltestmanagerc
-rwxr-xr-x 1 root root   13448 Feb 16 17:45 /usr/bin/mysqltestmanager-pwgen
-rwxr-xr-x 1 root root 1312064 Feb 16 17:45 /usr/bin/mysql_tzinfo_to_sql
-rwxr-xr-x 1 root root   54160 Feb 16 17:45 /usr/bin/mysql_upgrade
-rwxr-xr-x 1 root root    5753 Feb 16 17:44 /usr/bin/mysql_upgrade_shell
-rwxr-xr-x 1 root root  112136 Feb 16 17:45 /usr/bin/mysql_waitpid
-rwxr-xr-x 1 root root    3818 Feb 16 17:44 /usr/bin/mysql_zap

The mysqld binary is found in /usr/libexec

Error Log

The MySQL error log is found in /var/log/mysqld.log

The content after an initial start of MySQL will look similar to:

cat /var/log/mysqld.log
100705 22:09:03  mysqld started
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
100705 22:09:03  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
100705 22:09:03  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
100705 22:09:03  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
100705 22:09:03  InnoDB: Started; log sequence number 0 0
100705 22:09:03 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

On the first invocation of MySQL, the InnoDB storage engine will create a default tablespace and redo logs. This is the majority of messages in the above log.

Processes

MySQL is a multi-threaded single process called mysqld. A second wrapper process mysqld_safe is generally found. This process logs stderr and also will restart the mysqld process if not found.

ps -ef | grep mysql
root     14733     1  0 Jul05 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql
mysql    14783 14733  0 Jul05 pts/1    00:00:10 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

Memory Usage

MySQL can have a very low memory footprint. By default the mysqld process has a 175M virtual size.

$ ps -eopid,fname,rss,vsz,user,command | grep -e "RSS" -e "mysql"
  PID COMMAND    RSS    VSZ USER     COMMAND
14275 grep       720  61136 root     grep -e RSS -e mysql
14733 mysqld_s  1192  63820 root     /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql
14783 mysqld   27004 179496 mysql    /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock

Disk Usage

The MySQL data files will be stored on a default installation in /var/lib/mysql

$ du -sh /var/lib/mysql
22M     /var/lib/mysql

$ ls -ld /var/lib/mysql
drwxr-xr-x 4 mysql mysql 4096 Jul 13 11:50 /var/lib/mysql

$ ls -l /var/lib/mysql
total 20552
-rw-rw---- 1 mysql mysql 10485760 Jul  5 22:09 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jul  5 22:09 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jul  5 22:09 ib_logfile1
drwx------ 2 mysql mysql     4096 Jul  5 22:09 mysql
srwxrwxrwx 1 mysql mysql        0 Jul  5 22:09 mysql.sock

The MySQL data directory includes the InnoDB tablespace datafile (ibdata1), redo logs (ib_logfile?), and the mysql directory corresponding to the mysql schema containing instance meta data.

This directory also contains the socket file, which is actually a poor location as this opens the security of this directory for world access. This will be discussed later in securing your installation.

Running MySQL

The best means of controlling the starting and stopping of mysql is to use the provided service init script mysqld

$ ls -l /etc/init.d/mysqld
-rwxr-xr-x 1 root root 4286 Feb 16 17:45 /etc/init.d/mysqld

Configuration

For OEL the MySQL configuration can be found in /etc.
NOTE: MySQL can use multiple configuration files.

$ ls -l /etc/my.cnf
-rw-r--r-- 1 root root 441 Feb 16 14:39 /etc/my.cnf

MySQL includes a minimalistic configuration file by default. The configuration file format is variable=value pairs for a given number of different sections, in this file [mysqld] and [mysqld_safe].

$ cat /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Audit

A full audit of all MySQL related files.

find / -name "*mysql*"
/etc/rc.d/rc3.d/S64mysqld
/etc/rc.d/rc5.d/S64mysqld
/etc/rc.d/rc6.d/K36mysqld
/etc/rc.d/init.d/mysqld
/etc/rc.d/rc0.d/K36mysqld
/etc/rc.d/rc4.d/S64mysqld
/etc/rc.d/rc1.d/K36mysqld
/etc/rc.d/rc2.d/S64mysqld
/etc/php.d/pdo_mysql.ini
/etc/php.d/mysql.ini
/etc/php.d/mysqli.ini
/etc/ld.so.conf.d/mysql-x86_64.conf
/etc/ld.so.conf.d/mysql-i386.conf
/usr/lib64/mysql
/usr/lib64/mysql/mysqlbug
/usr/lib64/mysql/libmysqlclient_r.so.15.0.0
/usr/lib64/mysql/libmysqlclient.so.15
/usr/lib64/mysql/libmysqlclient_r.so.15
/usr/lib64/mysql/mysql_config
/usr/lib64/mysql/libmysqlclient.so.15.0.0
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/Bundle/DBD/mysql.pm
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql.pm
/usr/lib64/php/modules/mysql.so
/usr/lib64/php/modules/pdo_mysql.so
/usr/lib64/php/modules/mysqli.so
/usr/libexec/mysqld
/usr/libexec/mysqlmanager
/usr/share/mysql
/usr/share/mysql/mysql_system_tables.sql
/usr/share/mysql/mysql_system_tables_data.sql
/usr/share/mysql/mysql_fix_privilege_tables.sql
/usr/share/mysql/mysql_test_data_timezone.sql
/usr/share/vim/vim70/syntax/mysql.vim
/usr/share/man/man8/mysqld.8.gz
/usr/share/man/man8/mysqlmanager.8.gz
/usr/share/man/man1/mysql.1.gz
/usr/share/man/man1/mysql.server.1.gz
/usr/share/man/man1/mysql_tableinfo.1.gz
/usr/share/man/man1/mysql_upgrade.1.gz
/usr/share/man/man1/mysqlaccess.1.gz
/usr/share/man/man1/mysql_waitpid.1.gz
/usr/share/man/man1/mysql_fix_extensions.1.gz
/usr/share/man/man1/mysqlman.1.gz
/usr/share/man/man1/mysqlbinlog.1.gz
/usr/share/man/man1/mysql_install_db.1.gz
/usr/share/man/man1/mysql_tzinfo_to_sql.1.gz
/usr/share/man/man1/mysql_secure_installation.1.gz
/usr/share/man/man1/mysqld_safe.1.gz
/usr/share/man/man1/mysqladmin.1.gz
/usr/share/man/man1/mysqlimport.1.gz
/usr/share/man/man1/mysql_zap.1.gz
/usr/share/man/man1/msql2mysql.1.gz
/usr/share/man/man1/mysqlshow.1.gz
/usr/share/man/man1/mysqldump.1.gz
/usr/share/man/man1/safe_mysqld.1.gz
/usr/share/man/man1/mysql_explain_log.1.gz
/usr/share/man/man1/mysql_config.1.gz
/usr/share/man/man1/mysqlbug.1.gz
/usr/share/man/man1/mysqld_multi.1.gz
/usr/share/man/man1/mysql_setpermission.1.gz
/usr/share/man/man1/mysqlhotcopy.1.gz
/usr/share/man/man1/mysql_find_rows.1.gz
/usr/share/man/man1/mysql_convert_table_format.1.gz
/usr/share/man/man1/mysql_fix_privilege_tables.1.gz
/usr/share/man/man1/mysqldumpslow.1.gz
/usr/share/man/man1/mysqltest.1.gz
/usr/share/man/man1/mysqlcheck.1.gz
/usr/share/man/man3/Bundle::DBD::mysql.3pm.gz
/usr/share/man/man3/DBD::mysql.3pm.gz
/usr/share/man/man3/DBD::mysql::INSTALL.3pm.gz
/usr/share/doc/mysql-server-5.0.77
/usr/share/doc/mysql-5.0.77
/usr/share/doc/selinux-policy-2.4.6/html/services_mysql.html
/usr/share/pixmaps/comps/mysql.png
/usr/share/info/mysql.info.gz
/usr/share/selinux/devel/include/services/mysql.if
/usr/bin/mysql_fix_extensions
/usr/bin/mysql
/usr/bin/mysqltestmanager
/usr/bin/mysqldumpslow
/usr/bin/mysql_upgrade_shell
/usr/bin/mysql_convert_table_format
/usr/bin/mysqlimport
/usr/bin/mysqldump
/usr/bin/mysqltestmanager-pwgen
/usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysqlbug
/usr/bin/mysqlhotcopy
/usr/bin/mysqlaccess
/usr/bin/mysqltest
/usr/bin/mysqladmin
/usr/bin/mysql_upgrade
/usr/bin/mysqltestmanagerc
/usr/bin/mysqld_safe
/usr/bin/mysql_zap
/usr/bin/mysql_waitpid
/usr/bin/msql2mysql
/usr/bin/mysql_secure_installation
/usr/bin/mysql_fix_privilege_tables
/usr/bin/mysqlshow
/usr/bin/mysql_config
/usr/bin/mysql_setpermission
/usr/bin/mysql_tableinfo
/usr/bin/mysql_find_rows
/usr/bin/mysqld_multi
/usr/bin/mysqlcheck
/usr/bin/mysqlbinlog
/usr/bin/mysql_install_db
/usr/bin/mysql_explain_log
/usr/lib/mysql
/usr/lib/mysql/mysqlbug
/usr/lib/mysql/libmysqlclient_r.so.15.0.0
/usr/lib/mysql/libmysqlclient.so.15
/usr/lib/mysql/libmysqlclient_r.so.15
/usr/lib/mysql/mysql_config
/usr/lib/mysql/libmysqlclient.so.15.0.0
/usr/lib/python2.4/site-packages/sos/plugins/mysql.pyo
/usr/lib/python2.4/site-packages/sos/plugins/mysql.pyc
/usr/lib/python2.4/site-packages/sos/plugins/mysql.py
/var/log/mysqld.log
/var/run/mysqld
/var/run/mysqld/mysqld.pid
/var/lock/subsys/mysqld
/var/lib/mysql
/var/lib/mysql/mysql
/var/lib/mysql/mysql.sock
/root/.mysql_history
/selinux/booleans/mysqld_disable_trans
/selinux/booleans/allow_user_mysql_connect

Installing a LAMP stack on Oracle Enterprise Linux

by

After successfully installing MySQL on Oracle Enterprise Linux installing a LAMP (Linux/Apache/MySQL/PHP) stack can also be performed with a single command:

$ yum install -y httpd php php-mysql
# Start the Apache Httpd Process
$ /etc/init.d/httpd start

To test and confirm Apache Httpd and PHP, we can use the CLI browser lynx:

$ yum install -y lynx
$ echo "<? phpinfo() ?>" > /var/www/html/phpinfo.php
$ lynx http://localhost/phpinfo.php

If successful, you will find a web page that contains the following.

phpinfo() (p1 of 31)

   PHP Logo

PHP Version 5.1.6

   System Linux localhost.localdomain 2.6.18-164.el5 #1 SMP Thu Sep 3 04:15:13
   EDT 2009 x86_64
   Build Date Feb 11 2010 19:07:36
   Configure   Command   './configure'  '--build=x86_64-redhat-linux-gnu'
   '--host=x86_64-redhat-linux-gnu'    '--target=x86_64-redhat-linux-gnu'
   '--program-prefix=''--prefix=/usr''--exec-prefix=/usr''--bindir=/usr/bin'
   '--sbindir=/usr/sbin'    '--sysconfdir=/etc'    '--datadir=/usr/share'
   '--includedir=/usr/include'                      '--libdir=/usr/lib64'
   '--libexecdir=/usr/libexec'                     '--localstatedir=/var'
   '--sharedstatedir=/usr/com'                  '--mandir=/usr/share/man'
   '--infodir=/usr/share/info'             '--cache-file=../config.cache'
   '--with-libdir=lib64'                   '--with-config-file-path=/etc'

It is important to note that PHP is also a standalone scripting language that doesn’t require a web browser. You can use PHP on the command line, for example:

$ php --version
PHP 5.1.6 (cli) (built: Feb 11 2010 19:06:40)
Copyright (c) 1997-2006 The PHP Group
Zend Engine v2.1.0, Copyright (c) 1998-2006 Zend Technologies

$ echo "<?phpinfo()?>" | php | grep -i mysql
Configure Command =>  './configure' '--build=x86_64-redhat-linux-gnu' '--host=x86_64-redhat-linux-gnu' '--target=x86_64-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib64' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--cache-file=../config.cache' '--with-libdir=lib64' '--with-config-file-path=/etc' '--with-config-file-scan-dir=/etc/php.d' '--disable-debug' '--with-pic' '--disable-rpath' '--without-pear' '--with-bz2' '--with-curl' '--with-exec-dir=/usr/bin' '--with-freetype-dir=/usr' '--with-png-dir=/usr' '--enable-gd-native-ttf' '--without-gdbm' '--with-gettext' '--with-gmp' '--with-iconv' '--with-jpeg-dir=/usr' '--with-openssl' '--with-png' '--with-pspell' '--with-expat-dir=/usr' '--with-pcre-regex=/usr' '--with-zlib' '--with-layout=GNU' '--enable-exif' '--enable-ftp' '--enable-magic-quotes' '--enable-sockets' '--enable-sysvsem' '--enable-sysvshm' '--enable-sysvmsg' '--enable-track-vars' '--enable-trans-sid' '--enable-yp' '--enable-wddx' '--with-kerberos' '--enable-ucd-snmp-hack' '--with-unixODBC=shared,/usr' '--enable-memory-limit' '--enable-shmop' '--enable-calendar' '--enable-dbx' '--enable-dio' '--with-mime-magic=/usr/share/file/magic.mime' '--without-sqlite' '--with-libxml-dir=/usr' '--with-xml' '--with-system-tzdata' '--enable-force-cgi-redirect' '--enable-pcntl' '--with-imap=shared' '--with-imap-ssl' '--enable-mbstring=shared' '--enable-mbstr-enc-trans' '--enable-mbregex' '--with-ncurses=shared' '--with-gd=shared' '--enable-bcmath=shared' '--enable-dba=shared' '--with-db4=/usr' '--with-xmlrpc=shared' '--with-ldap=shared' '--with-ldap-sasl' '--with-mysql=shared,/usr' '--with-mysqli=shared,/usr/lib64/mysql/mysql_config' '--enable-dom=shared' '--with-dom-xslt=/usr' '--with-dom-exslt=/usr' '--with-pgsql=shared' '--with-snmp=shared,/usr' '--enable-soap=shared' '--with-xsl=shared,/usr' '--enable-xmlreader=shared' '--enable-xmlwriter=shared' '--enable-fastcgi' '--enable-pdo=shared' '--with-pdo-odbc=shared,unixODBC,/usr' '--with-pdo-mysql=shared,/usr/lib64/mysql/mysql_config' '--with-pdo-pgsql=shared,/usr' '--with-pdo-sqlite=shared,/usr' '--enable-dbase=shared'
/etc/php.d/mysql.ini,
/etc/php.d/mysqli.ini,
/etc/php.d/pdo_mysql.ini,
mysql
MySQL Support => enabled
MYSQL_MODULE_TYPE => external
MYSQL_SOCKET => /var/lib/mysql/mysql.sock
MYSQL_INCLUDE => -I/usr/include/mysql
MYSQL_LIBS => -L/usr/lib64/mysql -lmysqlclient
mysql.allow_persistent => On => On
mysql.connect_timeout => 60 => 60
mysql.default_host => no value => no value
mysql.default_password => no value => no value
mysql.default_port => no value => no value
mysql.default_socket => no value => no value
mysql.default_user => no value => no value
mysql.max_links => Unlimited => Unlimited
mysql.max_persistent => Unlimited => Unlimited
mysql.trace_mode => Off => Off
mysqli
MysqlI Support => enabled
MYSQLI_SOCKET => /var/lib/mysql/mysql.sock
mysqli.default_host => no value => no value
mysqli.default_port => 3306 => 3306
mysqli.default_pw => no value => no value
mysqli.default_socket => no value => no value
mysqli.default_user => no value => no value
mysqli.max_links => Unlimited => Unlimited
mysqli.reconnect => Off => Off
PDO drivers => mysql, sqlite
pdo_mysql
PDO Driver for MySQL, client library version => 5.0.77

Short URL: rb42.com/oel-install-lamp

Installing MySQL on Oracle Enterprise Linux

by

One of the significant benefits of MySQL is it’s ease of use. Generally already installed on most Linux systems, MySQL can be installed by a single command if not yet present. On Oracle Enterprise Linux 5.4 you can use the following commands to check for MySQL, configure your yum repository and install MySQL.

# Check if already installed
$ rpm -qa | grep -i mysql

# Configure yum repository on new server
$ su -
$ cd /tmp
$ wget http://public-yum.oracle.com/public-yum-el5.repo
$ sed -e "s/enabled=0/enabled=1/" public-yum-el5.repo > /etc/yum.repos.d/public-yum-el5.repo

# Install MySQL
$ yum install -y mysql-server mysql

# Start and test MySQL Instance
$ /etc/init.d/mysqld start
$ mysql -uroot -e "SELECT VERSION()"

+-----------+
| VERSION() |
+-----------+
| 5.0.77    |
+-----------+

You can find more information about the Oracle public yum repository at http://public-yum.oracle.com You will also note that the version installed is 5.0. The current GA version of MySQL is 5.1, however Red Hat is notorious for taking time to update repositories more regularly. You can always use more current rpm files available at the MySQL downloads page.

If you want MySQL to be configured to automatically start and stop on your server you need to run the following command.

$ chkconfig mysqld on
$ chkconfig --list mysqld
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

You can link to this post with the short url rb42.com/oel-install-mysql

Related Posts

Speaking at Oracle Open World 2010

by


I will be one of 18 MySQL speakers at Oracle Open World 2010 at the first ever MySQL Sunday. With a great diversity of technical, community and product talks this will be a great opportunity to get a cross section of MySQL content. Combined with Java One, this year’s Oracle Open World will include a lot more opportunity of technical and developer content then the more regular marketing material.

As the program chair for the first dedicated MySQL track at last month’s ODTUG Kaleidoscope 2010 our MySQL community now includes a larger number of target people. From the Oracle community come many highly technical and skilled resources, some with an understanding or appreciation of MySQL and many that are new to MySQL.

This is a great opportunity to share our knowledge and experience with MySQL.

References

Optimizing SQL Performance – The Art of Elimination

by

The most efficient performance optimization of a SQL statement is to eliminate it. Cary Millsap’s recent Kaleidoscope presentation again highlighted that improving performance is function of code path. Removing code will improve performance.

You may think that it could be hard to eliminate SQL, however when you know every SQL statement that is executed in your code path obvious improvements may be possible. In the sequence SQL was implemented sometimes easy observations can lead to great gains. Let me provide some actual client examples that were discovered by using the MySQL General Log.

Example 1

5 Query   SELECT *  FROM `artist`
5 Query   SELECT *  FROM `artist`
5 Query   SELECT *  FROM `artist`
5 Query   SELECT *  FROM `artist`
5 Query   SELECT *  FROM `artist`
5 Query   SELECT *  FROM `artist` WHERE (ArtistID = 196 )
5 Query   SELECT *  FROM `artist` WHERE (ArtistID = 2188 )
5 Query   SELECT *  FROM `artist`
5 Query   SELECT *  FROM `artist`
5 Query   SELECT *  FROM `artist`

In this example, the following was executed for a single page load. Not only did I find a bug where full-table scans occurred rather then being qualified, there were many repeating and unnecessary occurrences.

Example 2

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
SELECT option_value FROM wp_options WHERE option_name = 'aiosp_title_format' LIMIT 1
SELECT option_value FROM wp_options WHERE option_name = 'ec3_show_only_even' LIMIT 1
SELECT option_value FROM wp_options WHERE option_name = 'ec3_num_months' LIMIT 1
SELECT option_value FROM wp_options WHERE option_name = 'ec3_day_length' LIMIT 1
SELECT option_value FROM wp_options WHERE option_name = 'ec3_hide_event_box' LIMIT 1
SELECT option_value FROM wp_options WHERE option_name = 'ec3_advanced' LIMIT 1
SELECT option_value FROM wp_options WHERE option_name = 'ec3_navigation' LIMIT 1
SELECT option_value FROM wp_options WHERE option_name = 'ec3_disable_popups' LIMIT 1
SELECT option_value FROM wp_options WHERE option_name = 'sidebars_widgets' LIMIT 1

This is a stock WordPress installation and highlights a classic Row at a Time (RAT) processing.

Example 3

SELECT * FROM activities_theme WHERE theme_parent_id=0
SELECT * FROM activities_theme WHERE theme_parent_id=1
SELECT * FROM activities_theme WHERE theme_parent_id=2
SELECT * FROM activities_theme WHERE theme_parent_id=11
SELECT * FROM activities_theme WHERE theme_parent_id=16

In this client example, again RAT processing, I provided a code improvement to run these multiple queries in a single statement, otherwise known as Chunk At a Time (CAT) processing. It’s not rocket science however the elimination of the network component of several SQL statements can greatly reduce page load time.

SELECT *
FROM   activities_theme
WHERE  theme_parent_id in  (0,1,2,11,16) 

Example 4

The following represents one of the best improvement. During capture, the following query was executed 6,000 times over a 5 minute period. While you make think this is acceptable, the value passed wae 0. The pages_id is an auto_increment column which by definition does not have a 0 value. In this instance, a simple boundary condition in the code would eliminate this query.

SELECT pages_id, pages_livestats_code, pages_title,
       pages_parent, pages_exhibid, pages_theme,
       pages_accession_num
FROM pages WHERE pages_id = 0

There are many tips to improving and optimizing SQL. This is the simplest and often overlooked starting point.

Related articles include: The RAT and the CAT and We need more CAT’s

What do MySQL Consultants do?

by

One role of a MySQL consultant is to review an existing production system. Sometimes you have sufficient time and access, and other times you don’t. If I am given a limited time here is a general list of things I look at.

  1. Review Server architecture, OS, Memory, Disks (including raid and partition type), Network etc
  2. Review server load and identify physical bottleneck
  3. Look at all running processes
  4. Look specifically at MySQL processes
  5. Review MySQL Error Log
  6. Determine MySQL version
  7. Look at MySQL configuration (e.g. /etc/my.cnf)
  8. Look at running MySQL Variables
  9. Look at running MySQL status (x n times)
  10. Look at running MySQL INNODB status (x n times) if used
  11. Get Database and Schema Sizes
  12. Get Database Schema
  13. Review Slow Query Log
  14. Capture query sample via SHOW FULL PROCESSLIST (locked and long running)
  15. Analyze Binary Log file
  16. Capture all running SQL

Here are some of the commands I would run.

2. Review server load and identify physical bottleneck

$ vmstat 5 720 > vmstat.`date +%y%m%d.%H%M%S`.txt

4. Look at MySQL processes

$ ps -eopid,fname,rss,vsz,user,command | grep -e "RSS" -e "mysql"
  PID COMMAND    RSS    VSZ USER     COMMAND
 5463 grep       764   5204 ronald   grep -e RSS -e mysql
13894 mysqld_s   596   3936 root     /bin/sh /usr/bin/mysqld_safe
13933 mysqld   4787812 5127208 mysql /usr/sbin/mysqld --basedir=/usr --datadir=/vol/mysql/mysqldata --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
13934 logger     608   3840 root     logger -p daemon.err -t mysqld_safe -i -t mysqld

$ ps -eopid,fname,rss,vsz,user,command | grep " mysqld " | grep -v grep | awk '{print $3,$4}'
4787820 5127208

5. Review MySQL Error Log

The error log can be found in various different places based on the operating system and configuration. It is important to find the right log, the SHOW GLOBAL VARIABLES LIKE ‘log_error’ will determine the location.

This is generally overlooked, however this can quickly identify some underlying problems with a MySQL environment.

7. Look at MySQL configuration

$ [ -f /etc/my.cnf ] &&  cat /etc/my.cnf
$ [ -f /etc/mysql/my.cnf ] &&  cat /etc/mysql/my.cnf
$ find / -name  "*my*cnf" 2>/dev/null

8. Look at running MySQL Variables

$ mysqladmin -uroot -p variables

9. Look at running MySQL status (x n times)

$ mysqladmin -uroot -p extended-status

It is important to run this several times at regular intervals, say 60 seconds, 60 minutes, or 24 hours.

I also have dedicated scripts that can perform this. Check out Log MySQL Stats.

11. Get Database and Schema Sizes

Check out my scripts on my MySQL DBA page

14. Capture Locked statements

Check out my script for Capturing MySQL sessions.

15. Analyze Binary Log file

Check out my post on using mk-query-digest.

16. Capture all SQL

Check out my post on DML Stats per table

Moving forward

Of course the commands I run exceeds this initial list, and gathering this information is only

Timing your SQL queries

by

When working interactively with the MySQL client, you receive feedback of the time the query took to complete to a granularity of 10 ms.

Enabling profiling is a simple way to get more a more accurate timing of running queries. In the following example you can see the time the kernel took to run an explain, the query, and alter, and repeat explain and query.


mysql> set profiling=1;
mysql> EXPLAIN SELECT ...
mysql> SELECT ...
mysql> ALTER ...
mysql> show profiles;
+----------+------------+-------------------------
| Query_ID | Duration   | Query
+----------+------------+-------------------------
|        1 | 0.00036500 | EXPLAIN SELECT sbvi.id a
|        2 | 0.00432700 | SELECT sbvi.id as sbvi_i
|        3 | 2.83206100 | alter table sbvi drop in
|        4 | 0.00047500 | explain SELECT sbvi.id a
|        5 | 0.00367100 | SELECT sbvi.id as sbvi_i
+----------+------------+-------------------------

More information at Show Profiles documentation page.

Getting started with Ruby and Sinatra

by

I’ve been doing a little work with Ruby, starting with some XHTML parsing with Nokogiri. I’ve just created my first web page using Sinatra.

While the instructions makes it look simple, it was a little more complex due a package dependency error.

Installation

$ sudo gem install sinatra
Password:
Successfully installed rack-1.2.1
Successfully installed sinatra-1.0
2 gems installed
Installing ri documentation for rack-1.2.1...
Installing ri documentation for sinatra-1.0...
Installing RDoc documentation for rack-1.2.1...
Installing RDoc documentation for sinatra-1.0...

Smoking it

Following the 5 line example on the home page, didn’t produce the result I expected.

 ruby rb42.rb
/Library/Ruby/Gems/1.8/gems/rack-1.2.1/lib/rack/utils.rb:138:in `union': can't convert Array into String (TypeError)
	from /Library/Ruby/Gems/1.8/gems/rack-1.2.1/lib/rack/utils.rb:138
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `require'
	from /Library/Ruby/Gems/1.8/gems/rack-1.2.1/lib/rack/request.rb:1
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `require'
	from /Library/Ruby/Gems/1.8/gems/rack-1.2.1/lib/rack/showexceptions.rb:3
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
	 ... 7 levels...
	from /Library/Ruby/Gems/1.8/gems/sinatra-1.0/lib/sinatra.rb:4
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:36:in `gem_original_require'
	from /Library/Ruby/Site/1.8/rubygems/custom_require.rb:36:in `require'
	from rb42.rb:2

Some Googling found a reference to a compatibility problem. While the syntax given on the post wasn’t correct, it was sufficient for me to find a correct solution.

$  sudo gem uninstall rack
Remove executables:
	rackup

in addition to the gem? [Yn]  y
Removing rackup

You have requested to uninstall the gem:
	rack-1.2.1
sinatra-1.0 depends on [rack (>= 1.0)]
If you remove this gems, one or more dependencies will not be met.
Continue with Uninstall? [Yn]  y
Successfully uninstalled rack-1.2.1

$ sudo gem install rack --version '1.2.0'
Successfully installed rack-1.2.0
1 gem installed
Installing ri documentation for rack-1.2.0...
Installing RDoc documentation for rack-1.2.0...

And now expected results starting then viewing http://localhost:4567/

$ ruby rb42.rb
== Sinatra/1.0 has taken the stage on 4567 for development with backup from Mongrel

Environment

$ sw_vers
ProductName:	Mac OS X
ProductVersion:	10.5.8
BuildVersion:	9L31a
$ ruby --version
ruby 1.8.6 (2009-06-08 patchlevel 369) [universal-darwin9.0]
$ gem --version
1.3.6

Upcoming Conferences with dedicated MySQL content

by

We recently held a dedicated MySQL Track at ODTUG Kaleidoscope 2010 conference for 4 days. This is the first of many Oracle events that will begin to include dedicated MySQL content.

If your attending OSCON 2010 in the next few weeks you will see a number of MySQL presentations.

MySQL will be represented at Open World 2010 in September with MySQL Sunday. Giuseppe has created a great one page summary of speakers. This event is described as technical sessions, an un-conference and an fireside chat with Edward Screven. I’ve seen tickets listed at $50 or $75 for the day.

Open SQL Camp will be held in Germany in August, and Boston in October. This is a great FREE event that includes technical content not just on MySQL but other open source databases and data stores.

You will also find dedicated MySQL tracks in Europe at the German Oracle Users Group (DOAG) conference in November and the United Kingdom Oracle Users Group (UKOUG) in November that I am planning on attending.

In 2011 there is already a lineup of events that will all contain multiple tracks of MySQL content.

For the MySQL community the introduction of various large Oracle conferences may be confusing. From my perspective I describe the big three as.

  • Oracle Open World is targeted towards marketing. This includes product announcements, case studies and first class events.
  • Collaborate is targeted towards deployment and includes 3 different user groups, the IOUG representing the Oracle Database, the Oracle Applications User Group, and the Quest Group.
  • ODTUG Kaleidoscope is targeted towards development. This includes the tools and technologies for developers and DBA’s to do your job.

Having just attended Kaleidoscope 2010, and being a relative unknown I left with a great impression of an open, technical and welcoming event. There was a great atmosphere, great events with excellent food for breakfast, lunch and dinner and I now have a long list of new friends. This conference very much reflected being part of a greater extended family, the experience I have enjoyed at previous MySQL conferences. I’ve already committed to being involved next year.

Improving MySQL Productivity – From Design to Implementation

by

My closing presentation at the dedicated MySQL track at ODTUG Kaleidoscope 2010 discussed various techniques and best practices for improving the ROI of developer resources using MySQL. Included in the sections on Design, Security, Development, Testing, Implementation, Instrumentation and Support were also a number of horror stories of not what to do, combined with practical examples of improving productivity.

Increasing MySQL Productivity