MySQL Specialist Philippines offers a remote or on-site DBA service. For inquiries, Please email me: jtvalerio@gmail.com

Latest Posts

Thursday, December 31, 2015

Top 5 MySQL settings I usually configure in a Single Instance



My goal on this post is to give newbies in MySQL an idea on the list of some of the most important settings they need to review after completing a MySQL Server installation.


Note: The recommendation here doesn't cover tuning and optimizing a MySQL Replication.


1. innodb_buffer_pool_size

This is the first important setting DBAs should look at and configure correctly. The innodb buffer pool is where data and indexes are cached. If dedicated server, I typically allocate atleast 75% of your Physical RAM, this is to give extra memory for the OS. If your Physical RAM is greater than 128GB, you can choose to maximize up to 90% since the remaining 10% of 128GB is more than enough for the OS.

2. innodb_log_file_size

this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. I typically set value to innodb_log_file_size = 512M. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G.

3. innodb_flush_log_at_trx_commit

You have three options in setting this parameter: 0,1,2.
The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.

With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions with any mysqld process crash.

With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage.

4. innodb_file_per_table

InnoDB tables, data and indexes, is stored in a separate .ibd file. A file per table allows you to reclaim disk space when truncating, dropping or rebuilding a table. Please note that it does not provide any performance benefit.

5. innodb_flush_method

This parameter defines the method used to flush data to the InnoDB data files and log files, which can affect I/O throughput. For a performance boost and better I/O throughput I have decided to change innodb_flush_method to O_DIRECT. 

Please Note: Where InnoDB data and log files are located on a SAN, it has been found that setting innodb_flush_method to O_DIRECT can degrade performance of simple SELECT statements by a factor of three.

Wednesday, July 8, 2015

What is the correct pronunciation of MySQL?


Have you ever attended a MySQL conference or a simple webinar?

Have you noticed the different ways a speaker or a participant pronounce MySQL?

I do! And it confused me before.

You might have encountered some saying it "m|y|s|q|l" (spelled), or "my seek well", or "my es Q el".

So which one is the official way to pronounce MySQL?

I rushed to pick-up my phone and ask my very good friend, Google!

The result returned this:

I tried to look for few more answers and Wiki  gave me this:

MySQL is officially pronounced /maɪˌɛskjuːˈɛl/ ("My S-Q-L").
From then on, I usually pronounce it as "My es Q el" and try to influence others to pronounce it the same. But, whether you pronounce it as "My es Q el" or "My seek well", both should be fine.
But here's more!
Ever wonder why "MySQL" was named as  "MySQL"?
Well, one of the co-founder of MySQL AB, Michael Widenius, named it after to one of his daughter, My. 

Pretty cool, Right!?
I'll be sharing more interesting facts about MySQL soon. So please subscribe and follow me via email  using the text box on the upper right corner of my blog.

Tuesday, July 7, 2015

Why MySQL?



In order to fully appreciate the benefits for using MySQL as your organization's primary database, let's first discuss some important facts about MySQL.

MySQL is the world's most popular open source database. With its proven performance, reliability and ease-of-use, MySQL has become the leading database choice for web-based applications, used by high profile web properties including Facebook, Twitter, YouTube, Yahoo! and many more.

Oracle drives MySQL innovation, delivering new capabilities to power next generation web, cloud, mobile and embedded applications.

You may not be aware, but most of the applications installed in your mobile phone is using MySQL as its database. Even those social networking site that you frequently visit is storing most of your information using MySQL.

Suprised?!

But the main questions are, Why MySQL? How will my company benefit from it? Why will I migrate my existing databases to MySQL?

There some things you might need to know about MySQL:

1. It's FREE! MySQL Communition Edition can be downloaded and used freely according to the terms of the GNU General Public License (GPL). Here is the MySQL Download link

2. Performance and Scalability. MySQL is powering some of the most demanding Web-based applications.

As per Oracle, here are some of those companies who run mission critical web-based applications with MySQL as their primary databases:



a. Facebook runs tens of thousands of MySQL servers, a typical instance counting 1 to 2 TB of data. 11.2 Million row changes and 2.5 Billion rows read per second are handled in MySQL.

b. Twitter achieved in August 2013 a new World record of 143,199 Tweets Per Second (TPS). A typical day sees over 500 million tweets, which means about 5,700 per second with MySQL.

c. Over 6 billion hours of video are watched each month on YouTube, served to you via MySQL.

d. PayPal built a globally-distributed database with 100 terabytes of user-related data based on MySQL Cluster.

Other Companies using MySQL:




3. Reliability. MySQL is famous for its rock-solid reliability. Aside from the extensive QA testing at Oracle, companies like Percona and organization like MariaDB are sharing their expertise and help in providing recommendations and improvements.

4. Ease of Use and Administration. MySQL offers quick-start capabilities with the average time from software download to installation completion being less than fifteen minutes.

In Summary, MySQL is a proven and mature product. It has evolved over the past 20 years benefitting from the feedback of millions of developers and DBAs relying on it for the most demanding Web applications in the world, pushing its limits, and contributing in a number of ways to its performance, reliability and ease of use. MySQL is available on all major platforms and has connectors for all key development languages.

Welcome to MySQL Specialist Philippines


Welcome to MySQL Specialist Philippines!

My name is Jonathan Valerio, I'm a MySQL Database Administrator for almost 7 years now (2015), and I have decided to create this blog site in order for me to document every MySQL challenges, issues, bugs and anything worth sharing, and contribute to the MySQL Community. Its more fun when you share ideas and skills, you just not helped someone, you also get a chance to learn in return.

If you wish to follow me, we can connect via my LinkedIn. You can also Like my Facebook Page MySQL Specialist Philippines for updates.

Again, I wish to welcome you to my blog site and I hope we learn alot from each other.

Feel free to comment in my upcoming articles.

There is no funny way for a DBA to greet you without using a SQL command., here is goes:

mysql> select "HELLO WORLD! Welcome to MySQL Specialist Philippines." as "MySQL Specialist PH";
+-------------------------------------------------------+
| MySQL Specialist PH                                   |
+-------------------------------------------------------+
| HELLO WORLD! Welcome to MySQL Specialist Philippines. |
+-------------------------------------------------------+
1 row in set (0.00 sec)

Mostly Read Today

Popular Posts

Powered by Blogger.