Ideas, releases, practical guides, and perspectives from the people building with MySQL.
The NoSQL event in New York had a number of presentations on non relational technologies including of Hadoop, MongoDB and CouchDB.
Coming historically from a relational background of 20 years with Ingres, Oracle and MySQL I have been moving my focus towards non relational data store. The most obvious and well used today is memcached, a non persistent distributed key/value pair store. There are a number of persistent key/value stores in the marketplace, Tokyo Cabinet, …
[Read more]Feels like a long time ago, but my talk at the MySQL User’s Conference back in April 09 on running MySQL Multiple Times to get better performance is now available online at YouTube. The original PDF of the presentation is available here.View it here: YouTube – Improving Performance by Running MySQL Multiple Times
Feels like a long time ago, but my talk at the MySQL User’s Conference back in April 09 on running MySQL Multiple Times to get better performance is now available online at YouTube. The original PDF of the presentation is available here.
View it here: YouTube – Improving Performance by Running MySQL Multiple Times
Has anyone read the report Microsoft, IBM and Oracle Lead the SMB and Mid-Market Database Segment - Yankee Group - 7/31/2006 - 5 Pages - ID: YANL1327246?
Paying 800USD just to know the percentages of each database is a bit expensive for my small startup budget, especially since it is not me who needs the information.
I was asked today to confirm the operation of INET_ATON() and INET_NTOA() functions for converting IP4 strings to numeric representations. My tests on the machine I was just connected to at the very instant reported the following results.
mysql> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 5.1.31-1ubuntu2 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT INET_ATON('74.165.97.204') AS ipn;
+------------+
| ipn |
+------------+
| 1252352460 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT INET_NTOA(1252352460) as ipa;
+---------------+
| ipa |
+---------------+
| 74.165.97.204 |
+---------------+
1 row in set (0.01 sec)
The results on the client via ODBC using MySQL 5.0.45 was?
ipn 1252352460 ipa 37342E3136352E39372E323034
What is causing this? It was immediately suspicious the pattern of 2E (the only letter), which translates from Hex to decimal …
[Read more]Maximize your strengths, minimize your weaknesses.
You can apply this approach to many things in life, I apply it to describing and using MySQL the product, and it’s components. The Query Cache like many features in MySQL, and indeed features in many different RDBMS products (don’t get me started on Oracle *features*) have relative benefits. In one context it can be seen as ineffective, or even detrimental to your performance, however it’s course grain nature makes it both trivial to disable dynamically (SET GLOBAL query_cache_size=0;), and also easy to get basic statistics on current performance (SHOW GLOBAL STATUS LIKE ‘QCache%’;) to determine effectiveness and action appropriately.
The Query Cache is course grained, that is it is rather simple/dumb in nature. When you understand the path of execution of a query within the MySQL kernel you learn a few key things.
I generally use the following MySQL INFORMATION_SCHEMA (I_S) query to Calculate Your MySQL Database Size. This query and most others that access the MySQL INFORMATION_SCHEMA can be very slow to execute because they are not real tables and are not governed by physical data, memory buffers and indexes for example but rather internal MySQL data structures.
Mark Leith indicates in his post on innodb_stats_on_metadata that Innodb performs 8 random(ish) dives in to the index, when anybody accesses any of SHOW TABLE STATUS, SHOW INDEX, INFORMATION_SCHEMA.TABLES,INFORMATION_SCHEMA.STATISTICS for InnoDB tables. This can have an effect on performance, especially with a large number of Innodb tables, and a poor ratio of innodb_buffer_pool_size to disk data+index footprint.
What is even more …
[Read more]While learning a new ORDER BY syntax recently, as a diligent architect/DBA I reviewed the documentation. What I also found in the SELECT syntax which I did not also know was the keyword DUMPFILE.
The SELECT Syntax from MySQL 5.1 Manual
states:
If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL writes
only one row into the file, without any column or line
termination and without performing any escape processing. This is
useful if you want to store a BLOB value in a file.
It’s a shame there is no middle ground, where you get the features of OUTFILE (i.e. all rows), and the features of DUMPFILE (i.e. no heading)
With a discussion on identifying CPU and Memory bottlenecks achieved, let us now look at how Disk can affect MySQL performance.
One of the first steps when addressing a MySQL performance tuning problem is to perform a system audit of the physical hardware resources (CPU,Memory,Disk,Network), then identify any obvious bottlenecks in these resources.
There are several commands you can use including vmstat, iostat and sar (both part of the sysstat package) to provide information on disk I/O.
vmstat is a good place to start for an overall view for multiple resources, however for disk it only provides a total system overview.
procs -----------memory---------- ---swap-- -----io---- -system-- …[Read more]
I am publishing my MySQL related bookmark collection http://www.mysqlpreacher.com/bookmarks/.
Feel free to send me links you think might be good to add in order to help others.
Remember, SHARING IS CARING!!! …. we get so much for free, why shouldn’t we give some back?
Cheers,
Darren