Removing block data from databse

[ghost]

I Want to remove the block data from the database. so far it has become a 58GB database file just because of this. I only want to keep the chat and commands and few other thins in database. How can I remove block data from database using phpmyadmin?

Is it this command? DELETE FROM hawkeye WHERE action = 0 DELETE FROM hawkeye WHERE action = 10

Are those block data?

[bob7l]

Honestly MySQL isn't very optimial when removing indexed rows, especially at your current table size. I'd recommend you just reset the table altogether and start fresh.

Block-data is stored in the data column. Which is more or less just a blob of text. So it's going to take a very very very long time including a lot of resources to query that entire database and remove the rows containing those numbers.

DELETE FROM hawkeye WHERE action='0' AND data='10'; //This is for block-break with block-ID of 10

DELETE FROM hawkeye WHERE action='1' AND data='10'; //This is for block-place with block-ID of 10

Here are the actions and their ID's: https://github.com/bob7l/HawkReloaded/blob/master/src/uk/co/oliwali/HawkEye/DataType.java

[ghost]

Thanks a lot! this worked! it took a while to clear those but it did it. Unfortunately the space on disk for "hawkeye" database is still 28GB even though I cleared the block data. What else could be that is taking so much space? what command can I run to clear the rest? I only want to keep the data for chat and commands. and I do not want to delete the entire database and create a new one. the information for chat logs and commands logs are more valuable than the space on disk.

I don't know why the disk is so low on space. it shows I have about 100GB while when I got this server it says it has x3 120GB SSD but I don't know how its setup that it only shows one hard drive.

This is my server setup. (from SoYouStart.com aka OVH.com) CPU: Intel Xeon E3 1245v2 - 3.4 GHz RAM: 32 GB Disk: 3 x 120 GB SSD

I think it might be raid setup with hard drives but I dont know how to un-raid it to be 3 seperate drives .

[bob7l]

That is thanks to MySQL. Innodb does not release disk space. This is why most people use "innodb_file_per_table". That way running optimize table actually is able to free space.

What you COULD do is dump all your InnoDB tables, and delete all the InnoDB files manually. Then you should probably change MySQL's config to innodb_file_per_table. Finally just import the dump files back and you should be good to go.

Also you can't disable RAID on those three drives without having to reset them. If you don't know if you have RAID you probably don't. My guess is they left the other drives completely unmounted.

[ghost]

Thanks for the information. but I'm not that much of advanced. I downloaded the hawkeye database to my desktop which is now "1.42 GB" I dont know what to do at this point. should I delete the hawkeye database in phpmyadmin and import it again? if the "innodb_file_per_table" option is OFF it means deleting databse won't free the disk space and it means now I will have to backup the entire "innoDB" which is more than 28GB which takes days to download and then I Dont even know what I should do afterwards. and how to enable ""innodb_file_per_table". its insane this things are super advanced.

I have another about 20 other databases which are for other plugins that I do not want to mess with and if anything goes wrong with those or gets corrupted, everything on server will be lost. all data, stats, player money, permissions, everything is there.

I dont know about the hard drives. when I first got the server there was a setup but it didn't let me configure the hard drives. maybe I missed something like a advanced setting or something but this 80GB space is ridiculous there is nothing can be done . I can't even host a site, it runs out of space.