Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

have installed MySQL in a particular folder. I was able to run the server and create accounts, databases etc. However, now whenever I try to start the server, I get an error:

$ mysqld_safe --defaults-file=mysql.cnf &
[1] 2002
[compute-0-5 /amber2/scratch/myname/mysql]$ 130725 17:56:24 mysqld_safe Logging to '/amber2/scratch/myname/mysql/data/compute-0-5.local.err'.
130725 17:56:24 mysqld_safe Starting mysqld daemon with databases from /amber2/scratch/myname/mysql/data
130725 17:56:25 mysqld_safe mysqld from pid file /amber2/scratch/myname/mysql/data/compute-0-5.local.pid ended

[1]+  Done                    mysqld_safe --defaults-file=mysql.cnf

In the error file inside the data folder:

130725 17:17:53 mysqld_safe Starting mysqld daemon with databases from /amber2/scratch/myname/mysql/data
2013-07-25 17:17:54 0 [Warning] option 'read_buffer_size': unsigned value 2147483648 adjusted to 2147479552
2013-07-25 17:17:54 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-07-25 17:17:54 28189 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2013-07-25 17:17:54 28189 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)

2013-07-25 17:17:54 28189 [Note] Plugin 'FEDERATED' is disabled.
2013-07-25 17:17:54 28189 [Note] InnoDB: The InnoDB memory heap is disabled
2013-07-25 17:17:54 28189 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-07-25 17:17:54 28189 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-07-25 17:17:54 28189 [Note] InnoDB: Using Linux native AIO
2013-07-25 17:17:54 28189 [Note] InnoDB: Not using CPU crc32 instructions
2013-07-25 17:17:54 28189 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-07-25 17:17:54 28189 [Note] InnoDB: Completed initialization of buffer pool
2013-07-25 17:17:54 28189 [Note] InnoDB: Highest supported file format is Barracuda.
InnoDB: Error: pthread_create returned 12
130725 17:17:55 mysqld_safe mysqld from pid file /amber2/scratch/myname/mysql/data/compute-0-18.local.pid ended

But why am I getting the pthread_create error 12? It seems this is related to not enough space. On the device where the mysql folder (/amnber2//scratch/myname/mysql) resides, I do have space:

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              49G  5.0G   41G  11% /
        ...
10.255.255.46:/export/scratch
                       15T   11T  4.2T  72% /amber2/scratch

I also have a few gigs in my home directory quota

$ quota -v
Disk quotas for user myname (uid 41222):
     Filesystem  blocks   quota   limit   grace   files   quota   limit   grace
10.255.255.45:/export/ncms
                22986221  26214400 26214400               0       0       0
10.255.255.46:/export/scratch
                7321108       0       0               0       0       0

i.e., I am using 22G out of 25G:

$ du -sh ~/.
22G /home/ncms/myname/.

I also have free memory:

$ free -mg
             total       used       free     shared    buffers     cached
Mem:            62         41         21          0          0         28
-/+ buffers/cache:         12         49
Swap:           64         13         51

In my mysql configuration file:

myisam_sort_buffer_size=4G   
myisam_max_sort_file_size=200G
read_buffer_size=2G

So why am I getting the error pthread_create error while starting the server?

share|improve this question

1 Answer 1

up vote 2 down vote accepted
$ /usr/local/mysql/bin/perror 12
OS error code  12:  Cannot allocate memory

Sure, you have memory now but MySQL isn't running now.

Remove those multi-gigabyte configuration entries and verify that you can start the server without them. I suspect read_buffer_size may be the one that's getting you but they are all unnecessarily large for a new server. The warnings you're seeing for max_open_files and table_cache suggest that you are trying to tweak MySQL for performance by setting large values elsewhere in your config, too.

That's not going to do what you expect. When it comes to buffer and cache sizes, more is not always better and often it is worse. Comment out your custom settings and start with the defaults unless you have a specific reason not to... and when you do make changes, don't change more than one thing at a time unless the changes are directly related to each other.

share|improve this answer
    
You're right, it was just a memory issue. Thanks! –  highBandWidth Jul 26 '13 at 22:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.