MySQL Bugs: #57644: CREATE INDEX TOO SLOW ON ULTRAPARC SOLARIS

Bug #57644	CREATE INDEX TOO SLOW ON ULTRAPARC SOLARIS
Submitted:	22 Oct 2010 3:00	Modified:	3 Nov 2010 17:20
Reporter:	prashanth mothukuri	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.0.67	OS:	Solaris
Assigned to:		CPU Architecture:	Any

Description:
I setup'ed mysql database server on Sun Solaris UltraSparc T5120. 

I created an empty db and then added one table(10 columns) . Storage engine is MyISAM. 

I inserted 100000 rows into the above created table. It inserted in 2 sec. 

Note:Client and Server are on same machine. I am using LOAD DATA INFILE statement.

But, when I try to create unique index on the above table with three columns, its taking almost 1 min to insert. 

The system configuration is high 32 GB RAM, 150 GB HDD and 1.5 GHZ 2 Cpu,8 threads,8 core processer. 

But, it runs in 10 seconds on less powerful Mac, Linux,Windows each with the configuration (200GB HDD,Inter Dual Core,2.5GHZ,4GB RAM) 

Can someone provide the solution to this. I tried putting key buffer size as 4GB but it still did not work.

Note: Tried the same on latest version of mysql. But still the same issue

How to repeat:
I inserted 100000 rows into the above created table. It inserted in 2 sec. 

Note:Client and Server are on same machine. I am using LOAD DATA INFILE statement.

But, when I try to create unique index on the above table with three columns, its taking almost 1 min to insert. 

The system configuration is high 32 GB RAM, 150 GB HDD and 1.5 GHZ 2 Cpu,8 threads,8 core processer.

Actually,its showstopper and critical as we getting downtime at a customer site because of increase in data.please give a workaround atleast

Please, send my.cnf from your Solaris box.

Also, please, compare execution time of:

select benchmark(500000000,2*2);

from that Solaris server and other environments. I think this comparison will explain everything.

Here is the benhmark result. Also attached my.cnf file

mysql> select benchmark(500000000,2*2);
+--------------------------+
| benchmark(500000000,2*2) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (1 min 42.76 sec)

Also, I ran the benchmark query you gave after I ran the load data infile statement. Since you did not tell me when to run this, I just assumed I should be running after loading the number of records(in which the issue is) and hence ran after the load data infile statement finished.

Also, forgot to mention that the index is three character columns(reach of 20bytes)

Thank you for the feedback.

You forgot to attach my.cnf and benchmark from box where creating index works fast. Please attach.

Please also upgrade to current version 5.0.91, try with it and inform us if problem still exists: version 5.0.67 is very old and many bugs were fixed since.

Benchmark result is already pasted in the bug. Please go through the entire bug to find it.

Coming to my.cnf, I uploaded it by clicking files in the bug and thereby uploading there.

Pasting my.cnf here in the bug comments space.

# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/data) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password	= your_password
port		= 3306
socket		= /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
skip-locking

#=> JLR-BEGIN Commented these out.
#key_buffer = 16K
#max_allowed_packet = 1M
#table_cache = 4
#sort_buffer_size = 64K
#read_buffer_size = 256K
#read_rnd_buffer_size = 256K
#net_buffer_length = 2K
#thread_stack = 64K
#=> JLR-END Commented these out.

#=> JLR-BEGIN added new
back_log = 75
skip-innodb
max_connections = 200
key_buffer = 1024M
myisam_sort_buffer_size = 64M
join_buffer_size = 16M
read_buffer_size = 4M
sort_buffer_size = 4M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 7200
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 64M
max_connect_errors = 1000
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 128M
query_cache_limit = 2M
query_cache_size =128M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking
server-id	= 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# Disable Federated by default
skip-federated

# Uncomment the following if you are NOT using BDB tables
#skip-bdb

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data/
#innodb_log_arch_dir = /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

#JLR Begin:added the following:

log-error=/db/db03/mysql/mysql01/errors/mysql5.0.67.err
pid-file=/db/db03/mysql/mysql01/localhost.pid
datadir=/db/db01/mysql/mysql01/data
basedir=/opt/mysql/mysql
log-bin=/db/db02/mysql/mysql01/mysql-bin
expire_logs_days = 14
#port = 3426 

#JLR End:added the following:

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

Also, please note that the same my.cnf works in all OS platforms except Solaris Sparc

Thank you for the feedback.

You posted benchamrk only from Solaris (?) box, but we also need benchmark from " less powerful Mac, Linux,Windows"

ON MAC,Linux,windows its the same result and the result is as follows

mysql> select benchmark(500000000,2*2);
+--------------------------+
| benchmark(500000000,2*2) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (30.44 sec)

Just a note that, you can clearly see that benchmark result is higher in Solaris than other OS platforms

This is what I had

mysql> select benchmark(500000000,2*2);
+--------------------------+
| benchmark(500000000,2*2) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (15.23 sec)

on my almost 5 years old laptop:

------------------
System Information
------------------
Time of this report: 11/26/2009, 09:59:00
       Machine name: TOSHIBA-USER
   Operating System: Windows XP Home Edition (5.1, Build 2600) Service Pack 3
(2600.xpsp_sp3_gdr.090206-1234)
           Language: English (Regional Setting: Ukrainian)
System Manufacturer: TOSHIBA
       System Model: Satellite M55
               BIOS: Ver 1.00PARTTBL
          Processor: Intel(R) Pentium(R) M processor 1.86GHz
             Memory: 1014MB RAM
...

so I'd expect that some of your non-SPARC boxes runs it faster than in 30 seconds...

In any case, this simplest single thread benchmark is executed 3 times faster(!) in your other environment(s). You claim that CREATE INDEX is executed almost 6 times faster there. As you can see, most part of the difference is explained by the fact that your UltraSPARC CPU is just too slow for single thread computation intensive load. I doubt this has anything to do with any bugs in MySQL code.

Thx for the info. But the objective of the bug being filed is not to convey that there is bug in the mysql code but what needs to be done to make things faster in the solaris sparc and why its slow. Basically we need some hindsights

Also just for curioisity, can you also let me know what are we trying to do with the benchmark query, like what its for etc. what does 2*2 means there?

Also, when I did profiling, i see that repair sort taking 42 sec

According to our manual, http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html:

"Repair by sorting

The repair code is using a sort to create indexes."

So, most time is spent on sort, that is on comparing values.

benchmark(N,<expression>) will just calculate <expression> N times. I've used 2*2 as expression just because I know how fast it runs on most versions of MySQL and hardware platforms. Also I know from experience that when it is slow, everything is slow...

As we speak about sort here, you can use expression that compares typical data in that column used in your index. If it is INTEGER, you can just use 1 < 2:

mysql>  select benchmark(500000000,2*2);
+--------------------------+
| benchmark(500000000,2*2) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (1 min 2.42 sec)

mysql>  select benchmark(500000000, 1 < 2);
+-----------------------------+
| benchmark(500000000, 1 < 2) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (31.27 sec)

The results above are from non-optimized debug build on my laptop.

Once again, your CPU is just slow for single thread load, comparing to CPUs in other environments. You can not do much to speed up processing CPU-intensive single thread load (like CREATE INDEX) and make it 6-10 times faster...

You already agreed that there is no evidence in this report of any bug in MySQL code (or building procedures). If you can build MySQL from sources somehow and make it run your CREATE INDEX, say, 2 times faster than current official binaries from MySQL - please, give your facts, compiler and options used etc.

As for tuning my.cnf or OS (to make CREATE INDEX run somewhat faster), it looks like a free support request. This is a wrong place for such requests, sorry.

I did not say that I agreed on this issue being not related to mysql code. All I said was I am not blaming it. Anyhow, can you let me know what exactly happens when we create unique index from mysql implemenation perspective. Hope feedback from this may give some clues

Also, has scalability testing been done for MyISAM on Solaris Sparc? Because Create Index on 100k rows is a basic thing that works well in Oracle on same machine. What needs to be setup'ed to make things work

Scalability testing deals with throughput when number of concurrent connections increase. This has nothing to do directly with performance of single session executing CREATE INDEX. In MySQL 5.0.x, this operation for MyISAM table is executed by single thread. That's why the problem is related to single thread performance of your T2 processor (and, maybe, of your internal 146G SAS disk and filesystem used there, at some stage).

I've provided you with a simple way to test how single thread performance of T2 compares to single thread performance of other CPUs when MySQL is used. What I see is that difference in single thread performance clearly correlates with a difference in CREATE INDEX execution time.

In Oracle RDBMS (depending on version, options etc) index is created in parallel, by several UNIX processes, thus it can use T2 ability to run numerous threads concurrently. If N threads are used it can run up to N times faster.

If you want to investigate this further, please, send the results of SHOW CREATE TABLE and SHOW TABLE STATUS for the table in question, and exact CREATE INDEX statement you are executing. Also, please, send the results of

mount

Solaris command.

This information may help other community members to suggest you some changes in my.cnf (and at filesystem level, maybe) to improve performance to some extent. For now we are discussing some hypothetical case, and I've shown you one key reason that explains most part of your performance problem.

You should accept that a) single thread performance of T2 processor is notably worse than single thread performance of other modern CPUs, and b) MySQL 5.x, when working with MyISAM tables, uses single tread to do everything related to CREATE INDEX. There is nothing we can do to change these basic facts any time soon (if at all).

Also, if you say its because of slow single thread cpu computation issue then why the loading of data takes 0.1 sec as opposed to create index. is create index implementation is dependent on some specifics on OS side? What needs to be expected from OS for the create index to work fine? what exacty happens on create index implementation

Also, forgot to mention another thing.

LOAD DATA INFILE OF 100K rows into a empty table with a composite primary key of three character columns is taking a more than a minute to finish as opposed to loading of the same amount of rows with a composite index of the same number of columns in 15 sec. This happens again only in Solaris 10 sparc v9.

What are the reasons and solutions to this? my.cnf is the same as specified in this bug conversations earlier.

solaris sparc system configuration(32GB RAM,200GB HD,SPARC T2,10000 RPM DRIVE) is the same as mentioned in this bug conversations earlier.

Also, does having a higher configuration automatically improves performance?

For example, say I have same my.cnf in 8GB,16GB,32GB environments. Does performance vary automatically or do we need to do anything extra to scale as we go with higher configuration

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.