InnoDB: Operating system error number 995 in a file operatio

[alahamdan]

Support Request Template
What version of phpBB are you using? phpBB 3.0.6
What is your board's URL? http://www.12allchat.com/forum/
Who do you host your board with? Dedicated Server
How did you install your board? I used the download package from phpBB.com
Is your board a fresh install or a conversion? Fresh Install
Do you have any MODs installed? Yes
Is registration required to reproduce this issue? No
What MODs do you have installed? Advanced Meta Tags.
What styles do you currently have installed? Livexblue (Subsilver2)
What language(s) is your board currently using? Arabic
Which database type/version are you using? MySQL 5
What is your level of experience? New to PHP but not phpBB
When did your problem begin? since 72 hours (We got an increase of about 30% in traffic).
Please describe your problem. Mysql keep going down with an error message:

100207 9:45:14 InnoDB: Operating system error number 995 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/ ... codes.html
InnoDB: File name .\ibdata1
InnoDB: File operation call: 'Windows aio'.
InnoDB: Cannot continue operation.
100207 9:49:15 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
100207 9:49:15 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 23 1015991756.
InnoDB: Doing recovery: scanned up to log sequence number 23 1016161739
100207 9:49:15 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
100207 9:49:16 InnoDB: Started; log sequence number 23 1016161739
100207 9:49:16 [Note] C:\....\....\....\mysqld-nt.exe: ready for connections.
Version: '5.0.45-community-nt-log' socket: '' port: 3306 MySQL Community Edition (GPL)

Im using Innodb Tables (Because when i was new to phpbb i didnt notice server create innodb databases by default).

Phpbb running on a Windows (IIS 6) dedicated Xeon machine with 8 G ram. so we are not out of resources when this happens.

I tried to research and there s many gray responses to the error, no one is specific or sure!

Any help appreciated.

Thanks in Advance.
Generated by SRT Generator ($Rev: 3603 $)

[alahamdan]

Hello

I found a way to reproduce the error:

When i try to optimize the (1 G size table : Phppbb_Search_Wordmatch) , i see this error happening!

By the way, this table was 1 G, after oprimizing its 650 M, this is normal?

[Noxwizard]

You may try updating MySQL. There was a report about this problem and a patch was added to fix it about two months ago. 5.1.42 and higher include the fix.

[alahamdan]

Thanks for your reply.

I would like to ask Few questions please:

1-There is any problem in leaving tables as Innodb?

2- If yes, do changing them to MYISAM tables? i mean just convert tables or some thing more needed?.

3- there is any tutorial made by PHPBB team, on how to optimize mysql for big phpbb boards? i mean a tutorial with commands to run on mysql server command line, compare results, if you see X then change setting to Y in MY.ini

Thanks

[Noxwizard]

1. There shouldn't be any issues with InnoDB tables, phpBB has support for some of its extra features, like transactions. (InnoDB vs MyISAM)
3. Not at the moment, but hopefully in the very near future. You might check out MySQL Performance Blog and see if you can find anything there.

[alahamdan]

3. Not at the moment, but hopefully in the very near future. You might check out MySQL Performance Blog and see if you can find anything there.

Thanks for your reply. as per your advice, my host now updating the mysql server to 5.1.43 which is the most recent stable version as they said.

About mysql performance, i've been reading a lot on mysql forums, but since im not professional with mysql management (maybe many of phpbb administrators are same like me), i cannot determine how to optimize my.ini values for phpbb.

I wish you guys create a tutorial as handy tutorials your usually creating, explaining in details how to monitor mysql performance with phpbb.

Like telling admins how to log to mysql command line, and do set of commands to read current values, and determine if value x is higher/lower than y then you have to change setting z to some value! there is many such tutorials for mysql optimization but no one specifically about phpbb.

If there is a place i can suggest this please tell me.

Many Thanks.

[AdamR]

If there is a place i can suggest this please tell me.

This is actually currently being discussed by the Teams and, as Noxwizard mentioned above, will hopefully be something we can offer very soon.

- Adam

[alahamdan]

If there is a place i can suggest this please tell me.

This is actually currently being discussed by the Teams and, as Noxwizard mentioned above, will hopefully be something we can offer very soon.

- Adam

Glad to hear that, it will be one of the most important articles.

i would like to ask, can i post our board my.ini settings , server preferences, board statistics , and some mysql commands output here or in area51 so maybe one of support team give some advices about values?

I know its soon as you said, but im facing problems getting the mysql down!

Thanks in advance.

[AdamR]

i would like to ask, can i post our board my.ini settings , server preferences, board statistics , and some mysql commands output here or in area51 so maybe one of support team give some advices about values?

We'd be glad to offer whatever advise we can.

However, the specific issue you are having, from my research, is not necessarily a performance issue per se, but rather an OS issue specific to Windows+InnoDB which has yet to be fully identified.

- Adam

[alahamdan]

Hello

Let me summarize problem again:

1- Since few days we gained 30%+ traffic, so we starte to get this error :

Code: Select all

InnoDB: Operating system error number 995 in a file operation

2- There is no much about this problem, so we updated mysql to 5.1.43, since it looks like a bug issue, note that till this point we still using innodb_buffer_pool_size=1024M

3- Now after new installation of mysql-5.1.43, im trying to increase innodb_buffer_pool_size=3G and to make innodb_log_file_size=1G (since some posts saying increasing log file size solve Operating system error number 995 error).

Why to increase innodb_buffer_pool_size to 3G because from my experience 1G isn't enough, we will face erno memory lack problem!

Now when we try to increase innodb_buffer_pool_size to 3G we get this error:

Code: Select all

100208  7:42:49  InnoDB: Error: cannot allocate 3221241856 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 27645764 bytes. Operating system errno: 8
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
InnoDB: Fatal error: cannot allocate the memory for the buffer pool
100208  7:43:49 [ERROR] Default storage engine (InnoDB) is not available
100208  7:43:49 [ERROR] Aborting

This is strange, since we have Xeon Quad Core 2.83 , with 8 G ram, 500G sata 2 hard-drive and windows 2003 64 bit (We are running 32 Mysql , but with a 4 G ram server i could place that value to 3G before, dont know what is happening this time!).

Our board statistics are:

500.000 + posts.
30.000 members.
25.000 topics
Max users online (past hour at peak time reach 1400 users).

New members posts are moderated, we do a lot of delete and move of topics.

Our my.ini settings are as following:

Code: Select all

[MySQLD]
long_query_time = 1
log-error = error.txt
log-slow-queries = mysql-slow.log
basedir=C:\\......
datadir=C:\\......
default-character-set=latin1
default-storage-engine=INNODB
read_buffer_size=1M
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_max_dirty_pages_pct=90
innodb_lock_wait_timeout=300
innodb_log_buffer_size=8M
innodb_log_file_size=256M
innodb_buffer_pool_size=1024M
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=0
innodb_thread_concurrency=100
innodb_data_file_path = ibdata1:100M:autoextend
max_connections=300
old_passwords=1
max_user_connections=200
thread_cache_size=1
thread_stack=196608
binlog_cache_size=32768
net_read_timeout=20
net_retry_count=5
net_write_timeout=30
thread_concurrency=20
open_files_limit=0
wait_timeout=3600
table_cache=1024

So any Suggestions!

Thanks in advance.

[alahamdan]

Here is my mysql Status command show the following:

Threads: 9 Questions: 3077266 Slow queries: 361 Opens: 5127 Flush tables: 1
Open tables: 63 Queries per second avg: 58.884

Slow queries time threshold is 1 Second.

I will appreciate if you give me some commands i can do to get you results reflecting what is happening.

[alahamdan]

Any Help Here?

[alahamdan]

Hello

Looks like we reached the mysql 32-bit limit, since we cannot assign more memory for mysql, so we are migrating to 64-bit mysql.

Do you know if there will be any difference? i mean we just need to dump the database from 32bit and dump it back to the 64bit?

Or there is any thing i should take care of? off course im speaking about phpbb database.

Thanks in advance.

[Noxwizard]

phpBB isn't affected by that. As long it works correctly with PHP, the board will work fine. Specifics on how to upgrade the MySQL server will have to be asked at the MySQL site.

[alahamdan]

Ok thanks for reply, i'll try to do it.

If i want to change tables from Innodb to myisam what shall do?

And will myisam get troubles with my forums stats i explained before?

Thanks.