Archive

Posts Tagged ‘mariadb’

MySQL and MariaDB innodb_file_per_table

For those of you pushing your MySQL instances to higher levels, you’ll have realized at some point that all InnoDB tables share their indexing space into a single file (ibdata1, usually).

This has several very bad outcomes:

  • the ibdata1 file becomes huge after some time (we’ve seen it pass the 8GB bar)
  • starting the MySQL server takes longer
  • deleting an InnoDB table *doesn’t* reduce the space used by ibdata1
  • you have to shutdown your server, remove ibdata1 completely, then restart the server in order to reinitialize the ibdata1 file to a 0-size file (but this is, of course, difficult or dangerous to do in a high-availability context)

I just found there was a setting called “innodb_file_per_table” since MySQL 5.1 at least:
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table

There is an explanation on what the advantages and disadvantages of the method are (but as you might have guessed, the main disadvantage of using separate files is that you have more files, taking a little more time to do I/O operations when you scan them all – and of course retro-compatibility with previous versions of MySQL)
http://dev.mysql.com/doc/refman/5.1/en/innodb-multiple-tablespaces.html

This seems to be available in MariaDB as well.

Advertisements

gem install mysql2 on Debian Wheezy with MariaDB 10

April 13, 2014 2 comments

Just in case you would be in this situation (and given the fact I couldn’t find online information about it), if you are ever installing the mysql2 gem on a Debian Wheezy system with MariaDB 10 (I would guess a rather rare situation), you might get this error:

# gem install mysql2 -v=0.3.11
Building native extensions. This could take a while...
ERROR: Error installing mysql2:
 ERROR: Failed to build gem native extension.

 /usr/bin/ruby1.9.1 extconf.rb
checking for rb_thread_blocking_region()... yes
checking for rb_wait_for_single_fd()... yes
checking for mysql_query() in -lmysqlclient... no
checking for main() in -lm... yes
checking for mysql_query() in -lmysqlclient... no
checking for main() in -lz... yes
checking for mysql_query() in -lmysqlclient... no
checking for main() in -lsocket... no
checking for mysql_query() in -lmysqlclient... no
checking for main() in -lnsl... yes
checking for mysql_query() in -lmysqlclient... no
checking for main() in -lmygcc... no
checking for mysql_query() in -lmysqlclient... no
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of
necessary libraries and/or headers. Check the mkmf.log file for more
details. You may need configuration options.

Provided configuration options:
 --with-opt-dir
 --without-opt-dir
 --with-opt-include
 --without-opt-include=${opt-dir}/include
 --with-opt-lib
 --without-opt-lib=${opt-dir}/lib
 --with-make-prog
 --without-make-prog
 --srcdir=.
 --curdir
 --ruby=/usr/bin/ruby1.9.1
 --with-mysql-config
 --without-mysql-config
 --with-mysql-dir
 --without-mysql-dir
 --with-mysql-include
 --without-mysql-include=${mysql-dir}/include
 --with-mysql-lib
 --without-mysql-lib=${mysql-dir}/lib
 --with-mysqlclientlib
 --without-mysqlclientlib
 --with-mlib
 --without-mlib
 --with-mysqlclientlib
 --without-mysqlclientlib
 --with-zlib
 --without-zlib
 --with-mysqlclientlib
 --without-mysqlclientlib
 --with-socketlib
 --without-socketlib
 --with-mysqlclientlib
 --without-mysqlclientlib
 --with-nsllib
 --without-nsllib
 --with-mysqlclientlib
 --without-mysqlclientlib
 --with-mygcclib
 --without-mygcclib
 --with-mysqlclientlib
 --without-mysqlclientlib


Gem files will remain installed in /var/lib/gems/1.9.1/gems/mysql2-0.3.11 for inspection.
Results logged to /var/lib/gems/1.9.1/gems/mysql2-0.3.11/ext/mysql2/gem_make.out

 

If you find yourself in that situation, the solution is apparently to first issue an

apt-get install libmariadbd-dev

And then repeat the gem install mysql2 command. Done.

El mejor valor para max_connections en MySQL

January 13, 2014 Leave a comment

Según el artículo http://www.mysqlperformanceblog.com/2013/11/28/mysql-error-too-many-connections/, no hay un “mejor valor”, y mucho depende de la aplicación detrás, pero dicen que en máquinas con ~16GB de RAM, puede estar alrededor de 1000 sin mucho problema.

Para más, ya habría que empezar a pensar en un thread_pool.

Ver el artículo original para mayores detalles, y el artículo anterior en este blog para saber como cambiar este valor en vivo (a parte de cambiarlo en la configuración para que quede más allá de un reboot).

Profiling MySQL/MariaDB queries

MariaDB logoIf you ever face an optimization issue in MySQL or MariaDB and want to know how to measure/benchmark the differences in execution between two queries, you should definitely know about the set profiling=1; command.

Here is how it works:

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(login_user_id)  FROM stats.track_e_online WHERE DATE_ADD(login_date, INTERVAL 60 MINUTE) >= ‘2012-06-11 11:56:20’;
+———————-+
| count(login_user_id) |
+———————-+
|                   65 |
+———————-+
1 row in set (0.00 sec)
mysql> show profile;
+——————————–+———-+
| Status                         | Duration |
+——————————–+———-+
| starting                       | 0.000022 |
| checking query cache for query | 0.000055 |
| checking permissions           | 0.000010 |
| Opening tables                 | 0.000012 |
| System lock                    | 0.000005 |
| Table lock                     | 0.000023 |
| init                           | 0.000027 |
| optimizing                     | 0.000010 |
| statistics                     | 0.000008 |
| preparing                      | 0.000009 |
| executing                      | 0.000004 |
| Sending data                   | 0.001994 |
| end                            | 0.000006 |
| query end                      | 0.000004 |
| freeing items                  | 0.000014 |
| storing result in query cache  | 0.000227 |
| logging slow query             | 0.000004 |
| logging slow query             | 0.000028 |
| cleaning up                    | 0.000003 |
+——————————–+———-+
19 rows in set (0.01 sec)

mysql> SELECT count(login_user_id)  FROM stats.track_e_online WHERE ‘2012-06-11 11:56:20’  <= DATE_ADD(login_date, INTERVAL 60 MINUTE);
+———————-+
| count(login_user_id) |
+———————-+
|                   65 |
+———————-+
1 row in set (0.00 sec)

mysql> show profile;
+——————————–+———-+
| Status                         | Duration |
+——————————–+———-+
| starting                       | 0.000032 |
| checking query cache for query | 0.000083 |
| checking permissions           | 0.000014 |
| Opening tables                 | 0.000011 |
| System lock                    | 0.000005 |
| Table lock                     | 0.000031 |
| init                           | 0.000026 |
| optimizing                     | 0.000009 |
| statistics                     | 0.000008 |
| preparing                      | 0.000009 |
| executing                      | 0.000005 |
| Sending data                   | 0.001989 |
| end                            | 0.000007 |
| query end                      | 0.000003 |
| freeing items                  | 0.000015 |
| storing result in query cache  | 0.000340 |
| logging slow query             | 0.000005 |
| logging slow query             | 0.000030 |
| cleaning up                    | 0.000003 |
+——————————–+———-+
19 rows in set (0.00 sec)
mysql> set profiling=0;
Query OK, 0 rows affected (0.00 sec)

Well, granted, there isn’t much of a difference between those two examples. I believe that’s because these queries have been stored in the query cache already, but you get the idea…

Note that profiling is a session variable, which means it only acts in your session of the MySQL/MariaDB client. You can find more information about profiling in the MySQL documentation pages.

Of course, this also works in the great MariaDB server.

Important meetings

October 30, 2011 Leave a comment

Sometimes I like to treat this blog as a personal one and write down things that might affect in fundamental ways our future… or at least my own future, potentially.

Anyway, I’m currently living in Peru and had a series of meeting over the last two months with the new Electronical Government Office (ONGEI). Can’t say much for now (don’t want to spoil it) but I was in a meeting with the Prime Minister who seemed to agree with my proposal (in the big lines) and told me “you’re not Peruvian, are you?” in a kind of funny way (I managed to get the Peruvian accent so much that my Spanish friends make fun of me now). I’d also like to think I’ve been able to bend a little the shape of the future of the One Laptop Per Child project in Peru (on the side of Good) through my contact with the office’s director, but that was mostly a side-effect of what I hope is coming.

On Tuesday this week, I met with MySQL and MariaDB’s founder/creator, Michael Widenius (see picture). I didn’t really expect to like the guy, but as it turns out I felt soooo much related between the experiences of what happened on the MySQL-MariaDB side and what happened on the D0keos/Chamilo side that I ended up passing a good time. Those two historical events (for the projects themselves) happened pretty much at the same time, as well.

Yannick Warnier (Chamilo) and Michael Widenius (MariaDB) in Lima

This put me in the usual sharing mood, and I thought about Latinux. Now Latinux is mainly a Latin-American group (it’s at the same time an association and a company and an open-source software start-ups cluster) founded by my new friend (or at least close acquaintance) Ricardo Strusberg (*not* the guy on the left, that’s Santiago Gonzales), with whom I met a series of times during the last 12 months, and with whom we’re mounting the Official Chamilo Teacher and Admin Certifications (first one of them is currently in a review process and should be out before our Chamilo Users Day Peru on the 18th of November, so people will be able to come around, go to the workshop and get certified the same day).

So… coming back to the sharing mood. As it turns out, MariaDB suffers the exact same problems Chamilo does right now (and probably LibreOffice, to some extent). It is better than the software from which it moved away, it has most of the influent people in it, but there is no widespread adoption yet, because the original software kept the trademark and is still luring people into them being the best. Furthermore, the job was initially done so well, with so many people involved in the original projects, that it’s difficult to out-rank them with simple good intentions. A considerable marketing effort is necessary to make people know we’ve changed name, and that we are now better.

One of the reason I’m so fond of that certification effort is that it will definitely give a boost to our project, to have the ability to get yourself certified and prove you are a true professional. If we do it, we’re definitely moving one big step forward in terms of promotion. And so could MariaDB. So I mentioned it to Michael, who immediately agreed this was a great idea, and as I’m writing this post (which is two weeks later), Ricardo has been discussing the details of such a certification and they will be launching it at the beginning of next year all over America and France.

This specific encounter (that might shape the future of MariaDB considerably) was casually made possible by the organizers of the Comtel.pe event, who made it possible for us to meet. So thank you guys!

I can’t believe how things can happen so casually, but one thing is sure: if all of this would have been in another, more popular context, I would already have “casually” met with a bunch of the most popular IT people on the planet (in fact, I have a few practical examples in mind involving friends of mine). So, in a way, Peru is a great place, but there are still other (great) places to be out there.

%d bloggers like this: