Mysql Configuration problem too slow

[IamJim]

any inputs???

After installing and uninstalling once or twice a day now...I have come to the conclusion that for anything other than maybe emulating small websites and having a hobbyist situation, trying to use xampp for what I am trying to do is like bringing a rubber knife to a gunfight.

My attempts to bring into xampp a 4.1 gigg InnoDB have nothing but failed using (2) machines.

Dell 1300 dual 600 w/1 gig of ram and HP Probook 6550b with 4gig of ram and have used all mysql configurations included with the package.

Jim

Below is 2nd attempt for the day (after new install) using HP 6550b machine and the InnoDB - ini file supplied with xampp 1.8.0

Code: Select all

140102 20:21:21 [Note] Plugin 'FEDERATED' is disabled.
140102 20:21:21 InnoDB: The InnoDB memory heap is disabled
140102 20:21:21 InnoDB: Mutexes and rw_locks use Windows interlocked functions
140102 20:21:21 InnoDB: Compressed tables use zlib 1.2.3
140102 20:21:21 InnoDB: Initializing buffer pool, size = 2.0G
InnoDB: VirtualAlloc(2175795200 bytes) failed; Windows error 87
140102 20:21:21 InnoDB: Completed initialization of buffer pool
140102 20:21:21 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140102 20:21:21 [ERROR] Plugin 'InnoDB' init function returned error.
140102 20:21:21 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140102 20:21:21 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140102 20:21:21 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140102 20:21:21 [Note] Server socket created on IP: '0.0.0.0'.
140102 20:21:21 [ERROR] c:\xampp\mysql\bin\mysqld.exe: Table '.\mysql\db' is marked as crashed and should be repaired
140102 20:21:21 [Warning] Checking table:   '.\mysql\db'
140102 20:21:21 [ERROR] 1 client is using or hasn't closed the table properly
140102 20:21:21 [ERROR] c:\xampp\mysql\bin\mysqld.exe: Table '.\mysql\tables_priv' is marked as crashed and should be repaired
140102 20:21:21 [Warning] Checking table:   '.\mysql\tables_priv'
140102 20:21:21 [ERROR] 1 client is using or hasn't closed the table properly
140102 20:21:21 [ERROR] c:\xampp\mysql\bin\mysqld.exe: Table '.\mysql\columns_priv' is marked as crashed and should be repaired
140102 20:21:21 [Warning] Checking table:   '.\mysql\columns_priv'
140102 20:21:21 [ERROR] 1 client is using or hasn't closed the table properly
140102 20:21:21 [Note] Event Scheduler: Loaded 0 events
140102 20:21:21 [Note] c:\xampp\mysql\bin\mysqld.exe: ready for connections.
Version: '5.5.25a-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
140102 20:21:24 [Note] c:\xampp\mysql\bin\mysqld.exe: Normal shutdown

140102 20:21:24 [Note] Event Scheduler: Purging the queue. 0 events
140102 20:21:24 [Note] c:\xampp\mysql\bin\mysqld.exe: Shutdown complete

140102 20:21:26 [Note] Plugin 'FEDERATED' is disabled.
140102 20:21:26 InnoDB: The InnoDB memory heap is disabled
140102 20:21:26 InnoDB: Mutexes and rw_locks use Windows interlocked functions
140102 20:21:26 InnoDB: Compressed tables use zlib 1.2.3
140102 20:21:26 InnoDB: Initializing buffer pool, size = 2.0G
InnoDB: VirtualAlloc(2175795200 bytes) failed; Windows error 87
140102 20:21:26 InnoDB: Completed initialization of buffer pool
140102 20:21:26 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140102 20:21:26 [ERROR] Plugin 'InnoDB' init function returned error.
140102 20:21:26 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140102 20:21:26 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140102 20:21:26 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140102 20:21:26 [Note] Server socket created on IP: '0.0.0.0'.
140102 20:21:26 [Note] Event Scheduler: Loaded 0 events
140102 20:21:26 [Note] c:\xampp\mysql\bin\mysqld.exe: ready for connections.
Version: '5.5.25a-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
140102 22:11:40 [Note] Plugin 'FEDERATED' is disabled.
140102 22:11:43 InnoDB: The InnoDB memory heap is disabled
140102 22:11:43 InnoDB: Mutexes and rw_locks use Windows interlocked functions
140102 22:11:43 InnoDB: Compressed tables use zlib 1.2.3
140102 22:11:44 InnoDB: Initializing buffer pool, size = 2.0G
InnoDB: VirtualAlloc(2175795200 bytes) failed; Windows error 87
140102 22:11:44 InnoDB: Completed initialization of buffer pool
140102 22:11:44 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140102 22:11:44 [ERROR] Plugin 'InnoDB' init function returned error.
140102 22:11:44 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140102 22:11:45 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140102 22:11:45 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140102 22:11:45 [Note] Server socket created on IP: '0.0.0.0'.
140102 22:11:51 [Note] Event Scheduler: Loaded 0 events
140102 22:11:51 [Note] c:\xampp\mysql\bin\mysqld.exe: ready for connections.
Version: '5.5.25a-log'  socket: ''  port: 3306  MySQL Community Server (GPL)


[EDIT by Altrea: splitted from original topic for the following reasons]

• OP was 4 years old
• topic is now unrelated to OP
• one thread per unique user and issue rule

[Nobbie]

My attempts to bring into xampp a 4.1 gigg InnoDB have nothing but failed using (2) machines.

Indeed, Xampp is a development environment suited for beginners and small environments, A 4GB database definately boasts the idea of an development tool, such large databases require special configurations for a production environment (what is definately NOT the goal of Xampp).

Anyway, if you are a skilled administrator, you should be able to configure MySQL to run such large databases on your PC as well, several example configurations are delivered with the MySQL installation. You also should think about using MyISAM instead of InnoDB as database driver, this usually takes less resources and may solve many problems of the large database.

Anyway, 4GB is a huge database for MySQL, which comes preconfigured for very tiny databases instead (usually developers do not need millions of data sets).

[IamJim]

You yourself must not be very versed in the differences between MyISAM and InnoDB and the abilities and limits MyISAM has with recovery, record locking as would be need for 19 million records and multiple users on a large scale.

I just figured I could "get by with" xampp on my Win22003 R2 server edition here, as to not have to use another machine and bounce back and forth.

re: several example configurations are delivered with the MySQL installation

Yes, I have tried every one with Xampp on both machines also. In which you bring up a very good point...as it seems no one (at least in the 1.8.0 version) has bothered to properly modify :: my-innodb-heavy-4G.ini for xampp as this is incorrect for that ini file also./ {default storage engine} sure the heck should have been changed

Code: Select all

# Table type which is used by default when creating new tables, if not
# specified differently during the CREATE TABLE statement.
default-storage-engine = MYISAM

And is MyISAM is better, why is InnoDB now the default from 5.5.5 (yes, 1.8.0 is 5.5.25)

Again, just figured I'd give xampp a whirl...and in hopes? Find possibly someone that also using it in a larger capacity as I intend to. Not in a production NIX as my leased servers...but just the ease of being able to "not have to Dual Boot" or run a local machine other than windows.

Thanks for the replies though.

Jim

[Altrea]

And is MyISAM is better, why is InnoDB now the default from 5.5.5 (yes, 1.8.0 is 5.5.25)

Because Oracle has bought Sun Microsystems and wanted to force InnoDB to be the default Engine.
At this time InnoDB was far away from being performant (which has changed now for most downsides).

Again, just figured I'd give xampp a whirl...and in hopes? Find possibly someone that also using it in a larger capacity as I intend to.

Your requirements are very specific. At the end your configuration has to be very specific too to fit these requirements.

I myself have a development environment running based on XAMPP 1.7.3 which has databases in gigabyte capacities.
And i can just say setting up such an environment is not like changing some configs in a common way and all is running well.
Your OS, drivers, third party programs, XAMPP components and not at least your hardware is influencing the result so you need to bear all of this in mind.
I think this is out of scope a volunteer support board can provide.

Your last logfile is showing that you do basic errors in your configurations so if you want to set up such an environment and want to have it bullet proof (inclusively a backup and restore strategy, performance tweaks, etc) you should maybe spend some money to get an expert setting it up for you.

[IamJim]

re: Your last logfile is showing that you do basic errors in your configurations

Those are erros generated with the Supplied package xampp configs renamed to my.ini

Same as Un commenting inno db items in the other configs...it will not allow to start.. So I am under the impression those alternate configs never really tested for InnoDB ?

I'll just trash xampp for this...and partition off and install CentOS..

Sorry to bother you guys, and appreciate replies in trying to help...but I thought I would give it a try. XAMPP is the only emulator I have ever tried for Windows...and for doing small sites? Worked flawlessly..CONGRATS!!

[Altrea]

Same as Un commenting inno db items in the other configs...it will not allow to start.. So I am under the impression those alternate configs never really tested for InnoDB ?

These are templates shiped already with each MySQL installation. You need to define important settings there relating to the working my.ini.

I'll just trash xampp for this...and partition off and install CentOS..

It's a free world. But that will maybe not solve your problems.

[IamJim]

For those with aspirations of Hi Hopes and large sites, this is a must read first~~

http://dev.mysql.com/doc/refman/5.0/en/ ... ndows.html

[IamJim]

Such is true...and enjoyed the efforts and responses~~and thanks again.

But as mentioned, thought I would give it a try..

I also just posted a new thread...which should become a sticky maybe?

Jim

[Altrea]

I have merged your threads because it is related and the thread itself was very less self speaking and less of information. I would expect something different if i read this headline.

And i will not sticky it, because it is not relevant for a representative count of XAMPP users.