vsbabu.org

MySQL in Mandrake 9.1

After trying out PostgreSQL for loading web server logs, I decided to see how MySQL would fare. PostgreSQL has lot more options, especially foreign keys, stored procedures, triggers, much more standard SQL and more functions. But the default installation turned out to be slightly slower in executing my queries. MySQL is known for its speed, atleast for few-writes-many-reads kind of usage. Here are my notes on getting started with MySQL on Mandrake 9.1. I’ve not covered issues like security, fine tuning or backup and restore.

I. Installation

Mandrake makes it very easy. Just login as root and type:

urpmi mysql
/etc/rc.d/init.d/mysql restart
mysqladmin -u root password 'whatever'

It will install the server, client and common packages. Second line will start the service. The third line will set the admin password for database as whatever.

II. Setup a database

By default, a database called test is installed. Here is how to install another database and create a user who can access it.

mysql -u root -p
mysql> create database mydb;
mysql> grant select, insert, delete, update, create, drop, index
            on mydb.* to
            myuser identified by 'hello';
mysql> flush privileges;
mysql> \q

First line will ask you the root password for MySQL. Enter whatever.

From that point onwards, you are in client prompt, as indicated by mysql>. Please do not type in mysql>.

Within the client, first line creates a new database called mydb. Second line creates a new user called myuser with password hello and has ability to issue DML (select, insert, update, delete) and DDL (create, drop for tables, index for making indexes) statements.

III. Use the database

Following our example with PostgreSQL:

mysql mydb -u myuser -p
mysql> create table states (state_code varchar(2) primary key, state_name varchar(30) not null);
mysql> insert into states (state_code, state_name) values ('VA', 'Virginia');
mysql> select * from states;
mysql>\q

Interesting Observations

Loading 400,000 records to a table in PostgreSQL using COPY command took 2 minutes. In MySQL, it took 30 seconds. Here is a sample MySQL command to load a text file.

mysql> load data local infile 'upld.txt' into table httplogs fields terminated by '|';

This was before I created any indexes on the table. Creating indexes on url, accesstime and referrer took 2 minutes each in PostgreSQL after dataload. MySQL took 20 seconds each for url and referrer, and 15 seconds for accesstime.

Query that groups by accesstime and has a where clause on accesstime (ie, equal to a particular month) takes 0.02 seconds in MySQL. PostgreSQL takes 30 seconds.

Query that groups by url or referrer and has a where clause on accesstime (ie, equal to a particular month) takes 8 seconds in MySQL. PostgreSQL takes 35 seconds.

Loading 400,000 records and creating 3 indexes ate up 250MB in PostgreSQL after VACUUM. To be fair, I had set url and referrer as varchar(1024). In MySQL it consumed 140MB. I had to keep url and referrer as varchar(255).

I think for looking up web server logs, I’ll use MySQL. For more traditional RDBMS applications, I’ll consider using MySQL, but my first preference will still be PostgreSQL or Oracle.

With this kind of performance, I think MySQL would be a great choice for data warehousing. Who cares about foreign keys and stored procedures in warehousing apps?

1. Extract aggregate data from enterprise databases (hopefully on Oracle).
2. Drop and re-create tables in MySQL.
3. Upload aggregate dump into MySQL.
4. Create required indexes.

Do the above periodically. Build a nice web front-end (preferably in Zope) that queries MySQL database. Use PocketXML-RPC and some macros to directly deliver the information into Microsoft Excel or Word. Datawarehousing and reporting on the cheap

1. Another cool thing to do as far as indexing and speed is to use a md5 hash of the URLs, it gives you a fixed field and will speed up your queries even more. I learned that, and many other cool tips from Jeremy Zawondy @ http://jeremy.zawodny.com/mysql/

   Posted by: John Beimler on April 17, 2003 07:27 PM

2. I just installed mysql on Mandrake9.1.
   When I do a mysql restart, I get the following message:
   ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

   I'm missing something, I have set the server up somehow.
   Please help. Thanks.

   Posted by: yen on July 10, 2003 09:09 PM

3. yen's problem is covered here:

   http://www.linuxquestions.org/questions/history/53518">http://www.linuxquestions.org/questions/history/53518">http://www.linuxquestions.org/questions/history/53518">http://www.linuxquestions.org/questions/history/53518">http://www.linuxquestions.org/questions/history/53518

   Posted by: Mike Wharton on July 11, 2003 04:32 AM

4. And here:

   http://forums.devshed.com/archive/4/2003/05/1/60291

   Posted by: Mike Wharton on July 11, 2003 04:35 AM

5. I have a problem with Mandrake Linux 9.1. I have a radeon 7500 and I hear that the two shouldn't have any conflict. When I boot up linux, everything boots up nice and fast, and then when it gets to the part, "starting display panel", it runs very very slow from then on. Everything is sluggish, and if i click on something, it'll actually click it three seconds later, but my mouse moves around fine. Any ideas on what this might be? It does it under kde 1.3 and gnome.
   Thanx

   Posted by: Herve QUADJOVIE on October 15, 2003 05:03 PM

6. Let Learn and Learnt.

   Posted by: The Star on October 22, 2003 01:00 PM

7. Sorry for above thing,

   I have installed Mysql in Mandrake 9.1. It worked fine. But prob is I lost root previleges when I changed these through Webmin accidentally .Now root doesn't have even a right to create database.How can I regain all previleges of root?.Through webmin no way!.

   Posted by: The Star on October 22, 2003 01:04 PM

8. I tried the following and keep getting messages saying root has no access. Is there any way to fix or restore this?

   
   [root@magikcat root]# urpmi mysql
   Everything already installed
   [root@magikcat root]# /etc/rc.d/init.d/mysql restart
   Stopping MySQL Server
   [FAILED]
   Starting MySQL Server [ OK ]
   [root@magikcat root]# mysqladmin -u root password 'mynewpassword'
   mysqladmin: connect to server at 'localhost' failed
   error: 'Access denied for user: 'root@localhost' (Using password: YES)'
   

   Posted by: Mysterie on January 27, 2004 01:45 PM