Pencarian

Rss Posts

 

 

 

Berita pada kategori ‘Basisdata’

on NULL and NOT IN

Feb 27, 2010

I#8217;ve been trying to think of something #8220;big#8221; to write about for so long, I haven#8217;t written anything.? So I#8217;ll write about something #8220;small#8221; that I found out the other day.
It turns out, #8216;NOT IN#8217; and #8216;NULL#8217; can have an odd (to me) effect.
Say you are doing something like
SELECT a FROM table1
WHERE a NOT IN
(SELECT a FROM table2);
If there are any NULLs in the table2#8217;s a column, you will never get any results from this query.
Here#8217;s an example:
#8211; first, here are the two tables I used:
mysqlgt; select * from test1;
+#8212;#8212;+
| a??? |
+#8212;#8212;+
|??? 1 |
|??? 2 |
|??? 3 |
|??? 4 |
|??? 5 |
|??? 6 |
+#8212;#8212;+
6 rows in set (0.02 sec)
mysqlgt; select * from test2;
+#8212;#8212;+
| a??? |
+#8212;#8212;+
|??? 1 |
|??? 2 |
|??? 3 |
| NULL |
+#8212;#8212;+
4 rows in set (0.00 sec)
mysqlgt; select a from test1 where a not in (select a from test2);
Empty set (0.02 sec)
#8211; personally, I expected to get 4,5,6.? For that, you can do the following:
mysqlgt; select a from test1 where a not in (select a from test2 where a is NOT NULL);
+#8212;#8212;+
| a??? |
+#8212;#8212;+
|??? 4 |
|??? 5 |
|??? 6 |
+#8212;#8212;+
3 rows in set (0.00 sec)
Why is this?? It is actually to follow the SQL standards on NULL.? As mentioned at http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html ,
#8220;To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.#8221;
Just as #8220;1=NULL#8221; is undefined rather than false, and #8220;1lt;gt;NULL#8221; is too, #8220;1 in (null)#8221; is also undefined (or null), and #8220;1 NOT IN (null) is null#8221;.
mysqlgt; select 1 in (null);
+#8212;#8212;#8212;#8212;-+
| 1 in (null) |
+#8212;#8212;#8212;#8212;-+
|??????? NULL |
+#8212;#8212;#8212;#8212;-+
1 row in set (0.00 sec)
mysqlgt; select 1 not in (null);
+#8212;#8212;#8212;#8212;#8212;#8211;+
| 1 not in (null) |
+#8212;#8212;#8212;#8212;#8212;#8211;+
|??????????? NULL |
+#8212;#8212;#8212;#8212;#8212;#8211;+
1 row in set (0.00 sec)
To simplify the examples with tables above, we can do the following, and see similar results.
mysqlgt; select 1 not in (2);
+#8212;#8212;#8212;#8212;#8211;+
| 1 not in (2) |
+#8212;#8212;#8212;#8212;#8211;+
| 1??????????? |
+#8212;#8212;#8212;#8212;#8211;+
1 row in set (0.00 sec)
mysqlgt; select 1 not in (2,null);
+#8212;#8212;#8212;#8212;#8212;#8212;-+
| 1 not in (2,null) |
+#8212;#8212;#8212;#8212;#8212;#8212;-+
| NULL????????????? |
+#8212;#8212;#8212;#8212;#8212;#8212;-+
1 row in set (0.00 sec)
Personally, once I read the reasoning, it made sense to me, but initially I was surprised.? I thought I#8217;d write about it, because it is common to do a #8220;SELECT #8230; WHERE NOT IN (SELECT #8230;)#8221;, as in the example above with table1 and table2, and the results might not be what you expect if you have any NULLs in your table.

Basisdata, Sindikasi     Comments Off

Presenting Cluster tutorial at MySQL UC (and discount code!)

Feb 26, 2010

Together with Geert and Andrew I#8217;ll be teaching the MySQL Cluster tutrial at this year#8217;s MySQL Cluster User Conference #8211; Santa Clara, on April 12th. If you#8217;re interested in using MySQL Cluster but aren#8217;t sure how to get started (or you#8217;ve used it but would like some tips) then this is a great opportunity. Check out the tutorial description.
If you register by 15 March then you get the early-bird price and if you use this #8216;friend of a speaker#8217; code then you get an additional 25% off: mys10fsp
mys10fsp

Basisdata, Sindikasi     Comments Off

innodb_file_per_table

Feb 10, 2010

Recently I attempted to use this parameter in one of our InnoDB table and had an experience to make a note of. So this is what actually happened.As we know InnoDB writes all the table information into one tablespace file ibdata1.Obviously this lead to a disk space issue, since the ibdata1 file grew to 90G+ and it was not possible to defragment tablespace using the Alter method (Infact is is hardly possible to do in a production environment, since it leads to a huge downtime.)So I decided to go for innodb_file_per_table on our other production database server which was being configured from scratch.Now this database had around 15 tables and it had very high inserts/deletes/updates. For few days, things went ok. But in few days, I started getting complains from development team regarding performance on the tables and queue being backed up. Apparently whenever a delete / update was made on a table, all the other tables slow down and queries started taking more than 4 to 5 secs to complete.Dug around, ran some more optimisations , flush tables but not much difference. I was able to get 660 to 661 qps tops.On digging further and discussing with developers, we figured out that innodb_file_per_table is the culprit.InnoDB uses fsync() instead of fdatasync() to flush both data and log files on non-Windows system. fdatasync() is like fsync(), except it flushes only files data, not its metadata (last modified time etc.). Therefor fsync() can cause more IO. innodb_file_per_table causes each file to be fsync()ed separately which means writes to multiple tables can’t be combined into a single IO operation. This requires InnoDB to perform higher total number of fsync() operations, leading to a IO contention in my case. Finally we had to migrate the database to another machine without innodb_file_per_table since it is also not possible to undo the existing tables for which files are created per table.As quoted in MySQL documentation : The –innodb_file_per_table option affects only table creation, not access to existing tables. If you start the server with this option, new tables are created using .ibd files, but you can still access tables that exist in the shared tablespace. If you start the server without this option, new tables are created in the shared tablespace, but you can still access any tables that were created using multiple tablespaces.Once we migrated the performance started going up , almost upto 1500 qps.So moral of the story : Better test the parameter innodb_file_per_table in a test environment, with the load higher than expected on production. I have read many blogs and many people are benefited by the use of this parametere, but as a general practice I would not favor its use. Sure it can be useful to reclaim some disk space, but I dont think it would be worth at the cost of performance.

Basisdata, Sindikasi     Comments Off

328 Thousand Queries Per Second

Feb 10, 2010

Well, I did it again. Two days ago I improved my server’s QPS from 20k to 109k.
Today, after upgrading the MySQL version which allowed me to allocate more memory to the key buffer size (12 Gb), I managed to get it to 328k QPS.

The server isnbsp;similarnbsp;to the one from the previous post. Its a 4 year old, but quite meaty server.

The server ran triggers and stored functions that I wrote to help connect different data sets together. Basically reference another table using fairly complicated search logic, get an ID back and store it. The code was then tweaked to be as fast and light as possible.

This time I ran:

99 million row table to reference a 101 million row table
45 million row table to reference the same 101 million row table
35 million row table (same reference)
29 million row table (same reference)
28 million row table (same reference)

All at the same time..

The server hovered consistently around the 300k QPS mark, with a here and there high of 322-328k.
Also, I had to modify the code of Mtop (the application used to monitor MySQL in the picture) to ask for Queries and not Questions from MySQL after I upgraded it.

I’m very happy with the result. I can’t wait to get my hands on a stronger server to try to get a higher number, and in the meantime I will try to keep tweaking the settings on this server to hopefully get it from a load of 4 to 10.

Basisdata, Sindikasi     Comments Off

My sessions at the MySQL Conference amp; Expo 2010

Feb 10, 2010

A preliminary schedule is now available for the MySQL Conference Expo 2010 (in Santa Clara, California, USA). I have two talks and a tutorial, currently scheduled as follows:MySQL Cluster Tutorial, Mon 08:30 on 12 Apr 2010, together with Andrew Hutchings and Andrew MorganMySQL Cluster: An Introduction, Tue 11:55 on 13 Apr 2010Connecting MySQL and Python, Tue 15:05 on 13 Apr 2010Schedule might change a bit, so I suggest keeping an eye on the conference website.And please remember: we might have changed colors a few times past 2 years, but we are still the nice and friendly people we were back in MySQL AB!

Basisdata, Sindikasi     Comments Off

Introducing percona-patches for 5.1

Feb 10, 2010

Our patches for 5.0 have attracted significant interest.? You can read about SecondLife#8217;s experience here, as well as what Flickr had to say on their blog.? The main improvements come in both performance gains and improvements to diagnostics (such as the improvements to the slow log output, and INDEX_STATISTICS).
Despite having many requests to port these patches to 5.1, we simply haven#8217;t had the bandwidth as our main focus has been on developing XtraDB and XtraBackup.? Thankfully a customer (who prefers to stay unnamed) as stood up and sponsored the work to move the patches to 5.1.
To refresh, the most interesting patches are:

Performance patches for InnoDB ?. Although many patches are present in XtraDB / InnoDB-plugin, the RC status of plugin does not allow to install it on product for some customer#8217;s policies.
Important fixes are:

- InnoDB IO threads
- Adaptive checkpointing
- Buffer pool mutex split
- Reimplemented read-write locks

Diagnostic patches.

#8211; We provide much more statistics in slow.log, i.e. execution plan, InnoDB timing, profiling info
- Userstat patch

Different patches to help with day to day usage of MySQL ?

Two new features which not available for 5.0:

In slow.log for Stored Procedure call you can see profiling for each individial query from this procedure, not just call storproc()
With userstat you can get additional THREADS_STATISTICS which show similar information to USER/CLIENT_STATISTICS but per THREAD granularity (it#8217;s useful if you have connection pool)

On this stage the patches are available only in source code, you
can get them from Launchpad https://code.launchpad.net/~percona-dev/percona-patches/5.1.43.? Binaries are also on the way, and will be ready soon. We are running intensive stress testing loads on them to provide stable and quality packages.
And to finalize are results for tpce-like benchmark, where I compare MySQL-5.1.43 vs percona-5.1.43.
The results made for TPCE configuration with 2000 customers and 300 tradedays and 16 concurrent users on our R900 server. The dataset is about 25GB, fully fitting into buffer_pool, so disk does not really matter, but data was stored on FusionIO 320GB MLC card.
On chart with results I show amount of TradeResults transactions per 10 sec during 3600 session (more is better)

As you see with percona patches you can get just about 10x improvement.
Yeah, that sounds too cool, but let me explain where difference comes from.
As I mentioned in tpce workload details the load is very SELECT intensive and these SELECTS are mainly scans by secondary keys ( not Primary Keys), so it hits problems in InnoDB rw-lock implementations and in buffer_pool mutex contention, which alredy fixed in percona-patches ( and in XtraDB and InnoDB-plugin also).
So you are welcome to try it!

Entry posted by Vadim |
2 comments
Add to: | | | |

Basisdata, Sindikasi     0 Comments

Dedy Hariyadi: Buku MySQL 5 Gratis dari Achmad Solichin

Feb 08, 2010


tanya siapa?

Kabar yang sangat menggembirakan bagi yang sering bersinggungan dengan database MySQL. Pak Achmad Solichin yang saat ini bekerja sebagai dosen dan merangkap sebagai kepala?Laboratorium Komputer Universitas Budi Luhur membuat buku dengan judul MySQL 5, dari Pemula hingga Mahir. Buku setebal 119 halaman termasuk halaman depan memberikan gambaran tentang penggunaan MySQL dari tahap dasar sampai tahap lanjutan. Buku MySQL 5, dari Pemula hingga Mahir ini terdiri dari 11 bab. Saya fikir buku ini cocok untuk anda miliki bagi yang sering menggunakan MySQL sebagai database server.
Tanpa fikir panjang saya pun langsung mengunduh buku tersebut. Sebelum mengunduh anda diwajibkan untuk mendaftar terlebih dahulu pada blog Achmad Solichin yang beralamat di http://achmatim.net. Setelah mendaftar silakan cek pada email anda, selanjutnya login menggunakan password yang dikirim pada email anda. Jika anda berhasil?login langkah selanjutnya mengunduh buku?MySQL 5, dari Pemula hingga Mahir?pada http://achmatim.net/download/21/.?Untuk lebih lengkapnya anda dapat langsung merujuk pada http://achmatim.net/2010/01/30/buku-gratis-mysql-5-dari-pemula-hingga-mahir/. Selamat belajar MySQL 5.

Basisdata, F/OSS, Sindikasi     Comments Off

MariaDB is Thinking about Fixing MySQL Replication and You Can Help

Jan 29, 2010

In case you have not noticed, MariaDB is joining the list of projects thinking about how to improve MySQL replication.nbsp;nbsp; The discussion thread starts here on the maria-developers mailing list. This discussion was jointly started by Monty Program, Codership, and Continuent (my employer) in an effort to push the state of the art beyond features offered by the current MySQL replication.nbsp; Now that things are starting to die down with the Oracle acquisition, we can get back to the job of making the MySQL code base substantially better.nbsp; The first step in that effort is to get a discussion going to develop our understanding of the replication problems we think are most important and outline a strategy to solve them.nbsp; Speaking as a developer on Tungsten, my current preference would to be to improve the existing MySQL replication.nbsp; I suspect this would also be the preference of most current MySQL users.nbsp; However, there are also more radical approaches on the table, for example from our friends at Codership, who are developing an innovative form of multi-master replication based on group communications and transaction certification.nbsp; That’s a good thing, as we want a range of contrasting ideas that take full advantage of the creativity in the community on the topic of replication. If you have interest in improving MySQL replication please join the MariaDB project and contribute your thoughts.nbsp; It should be an interesting conversation.

Basisdata, Sindikasi     0 Comments

MySQL Cluster GCP Stop

Jan 28, 2010

One of the most common errors we come across whilst supporting MySQL Cluster is an error commonly referred to as ‘GCP stop’.nbsp; These errors will occur most frequently in cluster setups which have high activity and more often than not use disk data.nbsp; So lets look into what these are, why they happen and how to prevent them.
What is a GCP Stop?
All data that needs to be written to MySQL cluster is first written to the REDO log, this is so that when a node starts the log can be played back from the position of the last good LCP (Local CheckPoint, a point at which all the cluster data memory is written to disk).nbsp; The REDO data needs to be consistent between all data nodes and that is where the GCP (or Global CheckPoint) comes in.nbsp; It synchronously flushes the REDO data across all data nodes to disk every 2 seconds (by default).nbsp; A GCP stop happens when a new GCP is trying to commit the REDO to disk and the previous one has not finished.nbsp; MySQL Cluster is a real-time database so this is a critical problem and the node in question is shut down to preserve data integrity.
Why does a GCP Stop happen?
GCP stop usually happens for one of two related reasons.nbsp; Firstly there is too much data to commit between GCPs for it to all be written to disk at once and secondly the disks are too slow.
You should now be able to get an idea of why this is more prominent on clusters using disk data, both the disk data and GCP are written to disk at the same time (as well as things like the LCP), lowering the disk bandwith available for the GCP.
This is also more common on multi-threaded data nodes (ndbmtd) in MySQL Cluster 7.0 because these can handle more data simultaneously and therefore can be in a situation where they need to write more to the REDO log.
How to prevent a GCP Stop
There are several effective ways to prevent a GCP stop:
1. Buy faster disks – may not be an option but if the data is written faster this can prevent a GCP Stop2. Spread the different parts of the data node onto different disks – the REDO, LCP and disk data can all be separated onto different disks, giving a much better disk I/O bandwidth to each3. Commit more often – if you have a really long transaction with lots of data this could create a commit which is too large for one GCP4. Configuration – there are some configuration settings you can tweak to improve things, but these will only give small improvements over the above three points.nbsp; Settings like TimeBetweenGlobalCheckpoints which if decreased causes the data node to GCP more often which means there is less to write to disk per checkpoint (but checkpointing more often means less time to checkpoint, so not always a good option).nbsp; There are also settings affecting disk factors outside of GCP such as DiskPageBufferMemory, increasing this will buffer more disk data (much like innodb_buffer_pool_size for InnoDB) decreasing disk bandwidth disk data uses so that the GCP can use more disk bandwidth.
There are other settings that can be tweaked as a last resort depending on what kind of GCP Stop occurs (yes, there are a couple of different types) but the first three points should be a primary concern before thinking about doing this.
If you have any problems with GCP Stop I highly recommend asking on the MySQL Cluster forum or the MySQL Cluster mailing list.

Basisdata, Sindikasi     Comments Off

MySQL Workbench 5.2.15 Beta 5 Available

Jan 28, 2010

Dear MySQL Users,
Its only been a week since last release, and already the MySQL Workbench Team has a 5.2 beta 5 ready. This Workbench Beta 5 build includes fixes for 26 bugs #8211; 5 P1, 8 P2, 9 P3 and 4 P4.
We want to thank all the people who have been testing MySQL Workbench 5.2 alphas and betas and taking the time to file bugs and provide valuable feedback. We have fixed many bugs since last release and worked on some other details as well.
MySQL Workbench 5.2 Beta 5 provides:

Data Modeling
Query (upgrade from MySQL Query Browser)
Admin (upgrade from MySQL Administrator)

If you are a current user of MySQL Query Browser or MySQL Administrator, we look forward to your feedback on all the new capabilities we are delivering in a single unified MySQL Workbench
As always, you will find binaries for the various platforms on our download pages.
Please get your copy from our Download

http://dev.mysql.com/downloads/workbench/

To get started quickly, please take a look at this short tutorial.
MySQL Workbench 5.2 Beta Tutorial

http://wb.mysql.com/?p=406

Please be aware that this release is still a beta version ? so please don?t use it on your production servers! Also note, MySQL Workbench files saved with version 5.2 cannot be opened with previous versions of our program.
The files for several platforms have been pushed to our main server and should be available on our mirrors.
Blog postings and general information ? including build instructions for Linux ? can be found on our Workbench Developer Central site.
Workbench Developer Central

http://wb.mysql.com

Workbench Documentation and details on changes between releases can be found on these pages

http://dev.mysql.com/doc/workbench/en/index.html

http://dev.mysql.com/doc/workbench/en/wb-change-history.html

If you need any additional info or help please get in touch with us.
Post in our forums, leave comments on our blog pages or if you want to talk to us directly you can visit us on our IRC channel #workbench on irc.freenode.net.
Again, thank you for trying out the Workbench beta, we look forward to your feedback and bug reports.
- The MySQL Workbench Team

Basisdata, Sindikasi     Comments Off
« Previous Entries